problem with AdvancedCriteria in DSRequestModifier

[claudiobosticco]

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:

        <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:

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:

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?

[claudiobosticco]

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

[claudiobosticco]

Also note that $currentDate seems to work, ie:

<Criterion fieldName="DATA_PUBBLICAZIONE" operator="lessOrEqual" value="$currentDate"/>

produces this condition:

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

[Isomorphic]

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

[Isomorphic]

$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?

[claudiobosticco]

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?

[Isomorphic]

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.