Date Searches are broken

[revanbhapri]

It is working now

Thanks,
Revan

[Isomorphic]

This turns out to be an issue specific to the combination of Hibernate and SQL Server, and specifically with "date" fields backed by "datetime" columns. As of version 13.0, we internally use a trivial subclass of java.util.Date to represent logical dates (ie, dates with no time information, aka "date" fields) in criteria, and SQL Server does not like being passed an instance of that class as a criteria parameter value. We don't know why, because the subclass is completely trivial and is used only for marker purposes, but if we convert it to an instance of the base Date class before handing it to Hibernate, the problem goes away.

So we have made that change, and this issue should be resolved for your use case in 13.0 and greater builds as of tomorrow (ie, builds dated December 11 or later). Please try it and let us know how you get on.

Regards,
Isomorphic Software Support

[revanbhapri]

Hi Support Team
Please find the attached Sample project. I have attched databse backup and Eclipse project.

SQL server 2022
apache-tomcat-9.0.74

run it using : http://localhost:8080/SampleSmartGWT/index.html

Once Grid loaded, please try to filter on start date - Select today/other option from filter

Since zip filea are not allowed attach, I emailed the code to support@isomorphic.com and lee.russell@isomorphic.com

Thanks,
Revan

[Isomorphic]

No, this does not indicate a problem SmartClient. What is more likely (and more common) is that you have longstanding bad usage and it's causing a hard error in the new version of either Hibernate or SmartClient, whereas it was a more subtle error before.

As we mentioned, you can see this feature working, in general, in the Showcase. We also have many automated tests showing that date filtering works.

So there is something specific to your application code and/or setup. And obviously, since we show the feature working with code just like what you've shared, we have no way to dig deeper yet.

Please revisit the possibilities we covered above.

Then, if you believe this is actually a problem in SmartClient, you should try to create a standalone, self-contained, immediately runnable test case that will show the problem occurring when run in a standard environment. Guidance for doing this is in the "Debugging" topic in the Reference.

If you're struggling to produce a standalone test case, and prefer over-the-shoulder debugging of your application code via web conference, you can purchase an Hourly Support plan to get this service.

[revanbhapri]

I replaced isomorphic_hibernate.jar, isomorphic_sql.jar and isomorphic_core_rpc.jar with older version jars. I am not getting that hibernate error.

But I am getting client side error Date.parseServerDate is not a functio error which expected, as client and server libraries does not match.


Seee the attached screenshot for response data.

So, I belive there must be something wrong in your newer jars

[Isomorphic]

No, that does not suggest a SmartClient problem. It is not normal to have to explicitly tell Hibernate the type of a parameter when it can be determined from the object passed itself.

So, this again suggests instead any of the 3 problems we listed above.

[revanbhapri]

I am able to reprodue the eror, if I set parameter using - query.setParameter(0,crDate);

It works with query.setParameter(0,crDate,DateType.INSTANCE);

So I guess, something wrong in you end.

[revanbhapri]

My Custom code works fine. Below is implementation

DSResponse response=new DSResponse();

Session session = StandaloneDAO.sessionOpen();

Transaction tx = session.beginTransaction();

try {

Map<String,Object> crMap = req.getAdvancedCriteria().getCriteriaAsMap();

if(crMap != null && crMap.size() >0)

{

ArrayList criteriaObjects = (ArrayList) crMap.get("criteria");

if(criteriaObjects != null && criteriaObjects.size() > 0)

{

Map criteriaFieldsMap = (java.util.HashMap)criteriaObjects.get(0);

Date crDate=(Date)criteriaFieldsMap.get("value");

//{_constructor=AdvancedCriteria, criteria=[{fieldName=startDate, value=Tue Dec 03 00:00:00 IST 2024, operator=greaterOrEqual}], __normalized=true, operator=and}

Query query=session.createQuery("from MinimalCriminalCaseCivil c where c.startDate>=?");

query.setDate(0, crDate);

response.setData(query.list());

}

else

{

Query query=session.createQuery("from MinimalCriminalCaseCivil");

response.setData(query.list());

}

}

else

{

Query query=session.createQuery("from MinimalCriminalCaseCivil");

response.setData(query.list());

}

tx.commit();



}

catch (Exception e) {

tx.rollback();

e.printStackTrace();

logger.error(e.getMessage(), e);



} finally {

HibernateSessionFactory.closeSession(session);

}



return response;

[revanbhapri]

This what I get in sql profiler.

exec sp_executesql N'select count(*) as col_0_0_ from hwe.minimal_criminal_case_civil minimalcri0_ where (((minimalcri0_.start_date>=@P0 )and(minimalcri0_.start_date is not null )))',N'@P0 varbinary(8000)',0xACED00057372001B636F6D2E69736F6D6F72706869632E7574696C2E49534344617465BA2D522ADDA4FEB60200007872000E6A6176612E7574696C2E44617465686A81014B597419030000787077080000019388A2F04078

[revanbhapri]

I did not do any changes in the implementation or tables or Hibernate mappings.

I verified the jars as well. there are no additional hibernate jars.

I can not change my datasoruce to sql datasources as it going to be very big effort.

In server side I noticed below difference between old version and 14.0

14.0 Server side parameter - Gives error

{criteria={_constructor=AdvancedCriteria, criteria=[{fieldName=startDate, __normalized=true, value=Mon Dec 02 00:00:00 IST 2024, operator=greaterOrEqual}], __normalized=true, operator=and}, operationConfig={dataSource=CriminalCaseSearchDAODS, repo=null, operationType=fetch, textMatchStyle=substring}, startRow=0, endRow=75, sortBy=[-startDate], componentId=CriminalCase_CriminalCaseSearchDAODS_1733209170484, appID=builtinApplication, operation=CriminalCaseSearchDAODS_fetch, oldValues={_constructor=AdvancedCriteria, operator=and, criteria=[{fieldName=startDate, operator=greaterOrEqual, value=Mon Dec 02 00:00:00 IST 2024}]}, tenantId=null, _subQuerySeparation={criteria=[{}]}}

Old vesrion - which works

{criteria={_constructor=AdvancedCriteria, criteria=[{fieldName=startDate, operator=greaterOrEqual, value=Tue Dec 03 00:00:00 IST 2024, _constructor=AdvancedCriteria}], operator=and}, operationConfig={dataSource=CriminalCaseSearchDAODS, repo=null, operationType=fetch, textMatchStyle=substring}, startRow=0, endRow=75, sortBy=[-startDate], componentId=CriminalCase_CriminalCaseSearchDAODS_1733214548293, appID=builtinApplication, operation=CriminalCaseSearchDAODS_fetch, oldValues={_constructor=AdvancedCriteria, operator=and, criteria=[{fieldName=startDate, operator=greaterOrEqual, value=Tue Dec 03 00:00:00 IST 2024, _constructor=AdvancedCriteria}]}}

---------
In 14.0 - criteria is appnding iwth __normalized=true - Not sure if that is causing the problem ?

[Isomorphic]

This is something going on in the guts of Hibernate, and it seems like a nonsense error. Meanwhile date filtering continues to work with Hibernate, as shown multiple times in the Showcase.

Like your other two recent reports, this is likely going to turn out to have nothing to do with your upgrade to 14.0.

Some hints:

1) you may have regenerated your tables as part of the upgrade, and the column types are now different

2) you may have changed Hibernate mappings as part of the upgrade, so now Hibernate's idea of the field type mismatches the DB column's actual type

3) you may have multiple copies of .jars in the classpath, causing basic confusion inside Hibernate, or even confusion as to which values are of Date type

If none of the above help, you might also try direct use of Hibernate (without SmartClient involved). If this also doesn't work, there's your problem.

This sample shows some simple code for issuing Hibernate requests that are equivalent (but much simpler) than what SmartClient generates.

https://smartclient.com/smartgwtee/showcase/#orm_ds

Finally, you could just get rid of Hibernate and use the SQLDataSource instead. The SQLDataSource doesn't require you to create a useless POJO bean or maintain "mapping" files, is faster, and if far more flexible and powerful.

[revanbhapri]

Hi Support Team,

All Date seaches broken in upgrade version of application

Getting : com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting datetime from binary/varbinary string.

Below are sample of manual filter and list gird filter

Tried with three different way.

Criterion dateCr = new Criterion("startDate", OperatorId.GREATER_OR_EQUAL,RelativeDate.TODAY);

Criterion dateCr = new Criterion("startDate", OperatorId.GREATER_OR_EQUAL,DateUtil.create());

Criterion dateCr = new Criterion("startDate", OperatorId.GREATER_OR_EQUAL,new Date());

Attached List Grid search..

List Grid Query and Error:

12:04:10,020 INFO [STDOUT] 29-11-2024 12:04:10.020 [DefaultQuartzScheduler_Worker-7] INFO com.legaledge.harmony.index.Updater - Closing persister...
12:04:19,128 INFO [STDOUT] 29-11-2024 12:04:19.128 [http-0.0.0.0-8080-3] DEBUG org.hibernate.util.JDBCExceptionReporter - could not execute query [select count(*) as col_0_0_ from hwe.my_open_events myopeneven0_ where (((myopeneven0_.start_date_>=? )and(myopeneven0_.start_date_ is not null ))and((myopeneven0_.start_date_<=? )or(myopeneven0_.start_date_ is null ))and(myopeneven0_.person_id=? )and(myopeneven0_.person_id is not null ))]
com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting datetime from binary/varbinary string.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:5448) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1771) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:1029) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at org.jboss.resource.adapter.jdbc.WrappedResultSet.next(WrappedResultSet.java:520) ~[jboss-common-jdbc-wrapper.jar:4.0.2 (build: CVSTag=JBoss_4_0_2 date=200505022023)]
at org.hibernate.loader.Loader.doQuery(Loader.java:825) ~[hibernate3.jar:3.6.10.Final]