Announcement

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

    problem with AdvancedCriteria in DSRequestModifier

    SmartClient Version: SNAPSHOT_v12.1d_2019-04-04/EVAL Deployment (expires 2019.06.03_07.20.41) Licensed to: Isomorphic Software (#ISC_EVAL_NIGHTLY)

    Hello, as a follow up to:
    https://forums.smartclient.com/forum...376#post257376

    I'm trying the same advancedCriteria as a DSRequestModifier:

    Code:
            <operationBinding operationType="fetch" requires="true">
                <criteria _constructor="AdvancedCriteria" operator="or">
                    <criteria>
                        <Criterion fieldName="DATA_CESSAZIONE" operator="isNull"/>
                        <Criterion operator="and">
                            <criteria>
                                <Criterion fieldName="DATA_DECORRENZA" operator="lessThan" value="$currentDateTime"/>
                                <Criterion fieldName="DATA_CESSAZIONE" operator="greaterThan" value="$currentDateTime"/>
                            </criteria>
                        </Criterion>
                    </criteria>
                </criteria>
            </operationBinding>
    In the log I see:

    Code:
    2019-04-09 11:51:10,630 DEBUG DSTransaction About to add advancedCriteria to criteria: {
        _constructor:"AdvancedCriteria",
        operator:"or",
        criteria:[
            {
                operator:"isNull",
                fieldName:"DATA_CESSAZIONE"
            },
            {
                operator:"and",
                criteria:[
                    {
                        fieldName:"DATA_DECORRENZA",
                        value:new Date(1554803470630),
                        operator:"lessThan"
                    },
                    {
                        fieldName:"DATA_CESSAZIONE",
                        value:new Date(1554803470630),
                        operator:"greaterThan"
                    }
                ]
            }
        ]
    }
    so far so good. But then the generated query whereClause doesn't match the criteria:

    Code:
    2019-04-09 11:57:59,643 INFO  SQLDataSource [builtinApplication.JOFC_PRESIDENTE_IN_CARICA_fetch] Performing fetch operation with
        criteria: {_constructor:"AdvancedCriteria",criteria:[{criteria:[{fieldName:"DATA_CESSAZIONE",operator:"isNull"},{criteria:[{fieldName:"DATA_DECORRENZA",value:new Date(1554803879642),operator:"lessThan"},{fieldName:"DATA_CESSAZIONE",value:new Date(1554803879642),operator:"greaterThan"}],operator:"and"}],operator:"or"}],operator:"and"}    values: {_constructor:"AdvancedCriteria",criteria:[{criteria:[{fieldName:"DATA_CESSAZIONE",operator:"isNull"},{criteria:[{fieldName:"DATA_DECORRENZA",value:new Date(1554803879642),operator:"lessThan"},{fieldName:"DATA_CESSAZIONE",value:new Date(1554803879642),operator:"greaterThan"}],operator:"and"}],operator:"or"}],operator:"and"} 
    2019-04-09 11:57:59,643 DEBUG SQLDataSource [builtinApplication.JOFC_PRESIDENTE_IN_CARICA_fetch] DataSource 5988 acquired SQLDriver instance 1291894653 during initialization 
    2019-04-09 11:57:59,644 INFO  SQLDataSource [builtinApplication.JOFC_PRESIDENTE_IN_CARICA_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause 
    2019-04-09 11:57:59,644 INFO  SQLDataSource [builtinApplication.JOFC_PRESIDENTE_IN_CARICA_fetch] 5988: Executing SQL query on 'DBJOFC': SELECT JOFC_PRESIDENTI.ID_REC, JOFC_PRESIDENTI.NOME, JOFC_PRESIDENTI.COGNOME, JOFC_PRESIDENTI.EMAIL, JOFC_PRESIDENTI.TELEFONO, JOFC_PRESIDENTI.DATA_DECORRENZA, JOFC_PRESIDENTI.DATA_CESSAZIONE, JOFC_PRESIDENTI.ID_CLUB_FK, JOFC_PRESIDENTI.CREATOR, JOFC_PRESIDENTI.CREATOR_TIMESTAMP, JOFC_PRESIDENTI.MODIFIER, JOFC_PRESIDENTI.MODIFIER_TIMESTAMP FROM DBJOFC.JOFC_PRESIDENTI WHERE (((JOFC_PRESIDENTI.DATA_CESSAZIONE IS NULL) OR ((JOFC_PRESIDENTI.DATA_DECORRENZA IS NULL) AND '0'='1'))) 
    2019-04-09 11:57:59,646 DEBUG PoolableSQLConnectionFactory [builtinApplication.JOFC_PRESIDENTE_IN_CARICA_fetch] makeObject() created an unpooled Connection '1999261600' 
    2019-04-09 11:57:59,646 DEBUG SQLConnectionManager [builtinApplication.JOFC_PRESIDENTE_IN_CARICA_fetch] Borrowed connection '1999261600' 
    2019-04-09 11:57:59,646 DEBUG SQLTransaction [builtinApplication.JOFC_PRESIDENTE_IN_CARICA_fetch] Started new DBJOFC transaction "1999261600" 
    2019-04-09 11:57:59,646 DEBUG SQLDataSource [builtinApplication.JOFC_PRESIDENTE_IN_CARICA_fetch] Setting DSRequest as being part of a transaction 
    2019-04-09 11:57:59,646 INFO  SQLDriver [builtinApplication.JOFC_PRESIDENTE_IN_CARICA_fetch] Executing SQL query on 'DBJOFC' using connection '1999261600': SELECT JOFC_PRESIDENTI.ID_REC, JOFC_PRESIDENTI.NOME, JOFC_PRESIDENTI.COGNOME, JOFC_PRESIDENTI.EMAIL, JOFC_PRESIDENTI.TELEFONO, JOFC_PRESIDENTI.DATA_DECORRENZA, JOFC_PRESIDENTI.DATA_CESSAZIONE, JOFC_PRESIDENTI.ID_CLUB_FK, JOFC_PRESIDENTI.CREATOR, JOFC_PRESIDENTI.CREATOR_TIMESTAMP, JOFC_PRESIDENTI.MODIFIER, JOFC_PRESIDENTI.MODIFIER_TIMESTAMP FROM DBJOFC.JOFC_PRESIDENTI WHERE (((JOFC_PRESIDENTI.DATA_CESSAZIONE IS NULL) OR ((JOFC_PRESIDENTI.DATA_DECORRENZA IS NULL) AND '0'='1')))
    am I missing something?

    #2
    Hello, what about this issue, where the substitution of the velocity variable $currentDateTime doesn't work?

    Comment


      #3
      Also note that $currentDate seems to work, ie:
      Code:
      <Criterion fieldName="DATA_PUBBLICAZIONE" operator="lessOrEqual" value="$currentDate"/>
      produces this condition:
      Code:
      (JOFC_CONTENT_MANAGEMENT.DATA_PUBBLICAZIONE <= TO_DATE('2019-04-30 15:54:33','YYYY-MM-DD HH24:MI:SS') OR JOFC_CONTENT_MANAGEMENT.DATA_PUBBLICAZIONE IS NULL)
      Typo:
      but it uses a date, not a datetime.
      I meant:
      but it uses a datetime, not a date.
      I was expecting a date and maybe a trunc() for the column.

      $transactionDateTime also produces the IS NULL condition.
      Last edited by claudiobosticco; 1 May 2019, 23:28.

      Comment


        #4
        Apologies, this has been scheduled to be looked at, but has sat because of staff vacations. We will take a look today.

        Comment


          #5
          $currentDate works for fields of type "date", $currentDateTime does not. We are going to look at little deeper, because we suspect this may be a regression, but it does raise the question: why are you trying to use $currentDateTime rather than $currentDate?

          Comment


            #6
            Hello, I want to use $currentDateTime because my field is of type datetime.
            Note that there was a typo in my previous post, so $currentDate produces a condition based on a 'datetime', which for now I'm using as a workaround, but I was expecting it to use a date. Or am I misunderstanding how they work?

            Comment


              #7
              Issue with DSRequestModifier is fixed and available for download in nightly builds since May 8 (today).

              Regarding $currentDate vs $currentDateTime velocity variables, they both represent date/time. See these docs for details.

              Comment

              Working...
              X