Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

    Date Searches are broken

    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]
    Attached Files

    #2
    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.

    Comment


      #3
      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 ?
      Last edited by revanbhapri; Today, 01:44.

      Comment


        #4
        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

        Comment


          #5
          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;

          Comment


            #6
            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.

            Comment


              #7
              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.

              Comment

              Working...
              X