I cannot select records where a datetime field is null

[sidchekhar]

I am using SmartClient Version: v8.3p_2013-04-23/PowerEdition Deployment (built 2013-04-23)
My browser is Firefox version 11.

I am trying to read records from a Currency table but I only want to return the records where the field SERVICE is equal to "CREST" and the field TIME_DELETED is NULL.
My DS.XML file looks as shown below. All returned records have their SERVICE fields set to "CREST", which is correct; however, my problem is that the 'TIME_DELETED is null' criteria is ignored and some of the records returned have their TIME_DELETED field set to a valid datetime (i.e. not null). What am I doing wrong?

<DataSource
  ID="currency"
  serverType="sql"
  tableName="CURRENCY"
  >
  <fields>
    <field name="OID" title="OID" type="integer" primaryKey="true" required="true" hidden="true" />
    <field name="SERVICE" title="Service" length="10" type="text" />
    <field name="CCY_CODE" title="Currency" type="text" length="3" />
    <field name="DESCRIPTION" title="Description" type="text" length="20" detail="true" />
    <field name="TIME_DELETED" title="Time Deleted" type="datetime" detail="true" />
  </fields>

  <operationBindings>
    <operationBinding operationType="fetch">
      <criteria fieldName="SERVICE" value="CREST" />
      <criteria fieldName="TIME_DELETED" operation="isNull" />
    </operationBinding>
  </operationBindings>
</DataSource>

[Isomorphic]

For all server problems, post the complete server-side log for the request.

[sidchekhar]

Please find the server side log below.

I would have expected the where clause in the query on the line with timestamp '2013-06-01 17:21:24,933' to be:

WHERE (CURRENCY.SERVICE='CREST' AND CURRENCY.TIME_DELETED IS NULL)

instead of

WHERE (CURRENCY.SERVICE='CREST')

SERVER SIDE LOG
===============

=== 2013-06-01 17:21:23,920 [l0-4] DEBUG IDACall - Header Name:Value pair: Host:localhost:8443
=== 2013-06-01 17:21:23,921 [l0-4] DEBUG IDACall - Header Name:Value pair: User-Agent:Mozilla/5.0 (Windows NT 6.1; WOW64; rv:11.0) Gecko/20100101 Firefox/11.0
=== 2013-06-01 17:21:23,921 [l0-4] DEBUG IDACall - Header Name:Value pair: Accept:text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
=== 2013-06-01 17:21:23,921 [l0-4] DEBUG IDACall - Header Name:Value pair: Accept-Language:en-gb,en;q=0.5
=== 2013-06-01 17:21:23,921 [l0-4] DEBUG IDACall - Header Name:Value pair: Accept-Encoding:gzip, deflate
=== 2013-06-01 17:21:23,924 [l0-4] DEBUG IDACall - Header Name:Value pair: DNT:1
=== 2013-06-01 17:21:23,924 [l0-4] DEBUG IDACall - Header Name:Value pair: Connection:keep-alive
=== 2013-06-01 17:21:23,924 [l0-4] DEBUG IDACall - Header Name:Value pair: Referer:https://localhost:8443/?gwt.codesvr=127.0.0.1:9997
=== 2013-06-01 17:21:23,924 [l0-4] DEBUG IDACall - Header Name:Value pair: Cookie:JSESSIONID=xpy31df2rf6m
=== 2013-06-01 17:21:23,924 [l0-4] DEBUG IDACall - Header Name:Value pair: Content-Type:application/x-www-form-urlencoded; charset=UTF-8
=== 2013-06-01 17:21:23,925 [l0-4] DEBUG IDACall - Header Name:Value pair: Content-Length:1102
=== 2013-06-01 17:21:23,925 [l0-4] DEBUG IDACall - Header Name:Value pair: Pragma:no-cache
=== 2013-06-01 17:21:23,925 [l0-4] DEBUG IDACall - Header Name:Value pair: Cache-Control:no-cache
=== 2013-06-01 17:21:23,925 [l0-4] DEBUG IDACall - session exists: xpy31df2rf6m
=== 2013-06-01 17:21:23,925 [l0-4] DEBUG IDACall - remote user: sc
=== 2013-06-01 17:21:23,926 [l0-4] INFO  RequestContext - URL: '/TraficCrest/sc/IDACall', User-Agent: 'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:11.0) Gecko/20100101 Firefox/11.0': Moz (Gecko) with Accept-Encoding header
=== 2013-06-01 17:21:23,932 [l0-4] DEBUG XML - Parsed XML from (in memory stream): 3ms
=== 2013-06-01 17:21:23,936 [l0-4] DEBUG RPCManager - Processing 1 requests.
=== 2013-06-01 17:21:23,939 [l0-4] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
    },
    operationConfig:{
        dataSource:"currency",
        operationType:"fetch",
        textMatchStyle:"exact"
    },
    startRow:0,
    endRow:75,
    sortBy:[
        "CCY_CODE"
    ],
    componentId:"isc_TGListGrid_0",
    appID:"builtinApplication",
    operation:"currency_fetch",
    oldValues:{
    }
}
=== 2013-06-01 17:21:23,940 [l0-4] INFO  IDACall - Performing 1 operation(s)
=== 2013-06-01 17:21:23,940 [l0-4] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
=== 2013-06-01 17:21:23,940 [l0-4] DEBUG DeclarativeSecurity - DataSource currency is not in the pre-checked list, processing...
=== 2013-06-01 17:21:23,942 [l0-4] DEBUG AppBase - [builtinApplication.currency_fetch] No userTypes defined, allowing anyone access to all operations for this application
=== 2013-06-01 17:21:23,942 [l0-4] DEBUG AppBase - [builtinApplication.currency_fetch] No public zero-argument method named '_currency_fetch' found, performing generic datasource operation
=== 2013-06-01 17:21:23,943 [l0-4] INFO  SQLDataSource - [builtinApplication.currency_fetch] Performing fetch operation with
	criteria: {SERVICE:"CREST"}	values: {SERVICE:"CREST"}
=== 2013-06-01 17:21:23,947 [l0-4] INFO  SQLDataSource - [builtinApplication.currency_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause ORDER BY $defaultOrderClause
=== 2013-06-01 17:21:23,949 [l0-4] DEBUG SQLDataSource - [builtinApplication.currency_fetch] Executing row count query: SELECT COUNT(*) FROM $defaultTableClause WHERE $defaultWhereClause
=== 2013-06-01 17:21:23,950 [l0-4] DEBUG SQLDataSource - [builtinApplication.currency_fetch] Eval'd row count query: SELECT COUNT(*) FROM CURRENCY WHERE (CURRENCY.SERVICE='CREST')
=== 2013-06-01 17:21:24,893 [l0-4] DEBUG PoolableSQLConnectionFactory - [builtinApplication.currency_fetch] makeObject() created an unpooled Connection '1582070371'
=== 2013-06-01 17:21:24,893 [l0-4] DEBUG SQLConnectionManager - [builtinApplication.currency_fetch] Returning borrowed connection '1582070371'
=== 2013-06-01 17:21:24,893 [l0-4] DEBUG SQLDriver - [builtinApplication.currency_fetch] About to execute SQL query in 'jdbc/traficcrest' using connection '1582070371'
=== 2013-06-01 17:21:24,893 [l0-4] INFO  SQLDriver - [builtinApplication.currency_fetch] Executing SQL query on 'jdbc/traficcrest': SELECT COUNT(*) FROM CURRENCY WHERE (CURRENCY.SERVICE='CREST')
=== 2013-06-01 17:21:24,933 [l0-4] DEBUG SQLDataSource - [builtinApplication.currency_fetch] JDBC driver windowed select rows 0->75, result size 75. Query: SELECT CURRENCY.CCY_CODE, CURRENCY.DESCRIPTION, CURRENCY.OID, CURRENCY.SERVICE, CURRENCY.TIME_DELETED FROM CURRENCY WHERE (CURRENCY.SERVICE='CREST') ORDER BY CURRENCY.CCY_CODE
=== 2013-06-01 17:21:24,969 [l0-4] DEBUG SQLDataSource - [builtinApplication.currency_fetch] Using paging strategy 'jdbcScroll' - scrolling to absolute position 1
=== 2013-06-01 17:21:24,969 [l0-4] DEBUG SQLDataSource - [builtinApplication.currency_fetch] Scrolling / positioning took 0ms
=== 2013-06-01 17:21:25,006 [l0-4] INFO  DSResponse - [builtinApplication.currency_fetch] DSResponse: List with 5 items
=== 2013-06-01 17:21:25,007 [l0-4] DEBUG SQLConnectionManager - About to close T4CConnection with hashcode "1582070371"
=== 2013-06-01 17:21:25,043 [l0-4] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
=== 2013-06-01 17:21:25,046 [l0-4] DEBUG RPCManager - non-DMI response, dropExtraFields: false

[Isomorphic]

Where does that expectation come from?

This log suggests there is server-side code adding to the criteria (since it comes in blank but later becomes {SERVICE:"CREST"}).

If the server-side code that manipulates the criteria does not add an IS_NULL operator then obviously the SQL will not include it..

.. but you haven't shared the code that attempts to add this criterion?

[sidchekhar]

The expectation comes from the criteria in the operationBindings section of the ds.xml (see my first posting, that section is also replicated below) which requests TIME_DELETED to be Null. I am not adding the criterion in the code, I am using the ds.xml to do that. As I said in my original posting, I don't know whether the criteria I set was syntactically correct. I could not find a single example (in the forum, the documentation or the Net in general) that showed how to set a criteria or advancedCriteria in a ds.xml that requested a field to be NULL. I tried using Operation="isNull", OperationId="isNull", operationId="isNull" just in case letter casing mattered or the syntax was incorrect.

Thank you.

  <operationBindings>
    <operationBinding operationType="fetch">
      <criteria fieldName="SERVICE" value="CREST" />
      <criteria fieldName="TIME_DELETED" operation="isNull" />
    </operationBinding>
  </operationBindings>

[Isomorphic]

operator not "operation".

[sidchekhar]

Thank you for the reply.
I amended the ds.xml to say operator instead of operation (as shown below) and it still does not work; i.e. the fetch operation is still returning records that have their TIME_DELETED set to a valid date.

  <operationBindings>
    <operationBinding operationType="fetch">
      <criteria fieldName="SERVICE" value="CREST" />
      <criteria fieldName="TIME_DELETED" operator="isNull" />
    </operationBinding>
  </operationBindings>

[Isomorphic]

A second problem is that your first <criteria> tag assumes simple criteria, whereas your second one uses the AdvancedCriteria format - these can't be mixed. Assuming you want exact match on the first <criteria> tag, you should add operator="equals".

[sidchekhar]

I added operator="equals" to my first criteria (see below) and it did not make a difference.

Is there a document or a web page that explains how we use advancedCriteria in a ds.xml?

Many thanks.

  <operationBindings>
    <operationBinding operationType="fetch">
      <criteria fieldName="SERVICE" operator="equals" value="CREST" />
      <criteria fieldName="TIME_DELETED" operator="isNull" />
    </operationBinding>
  </operationBindings>

[sidchekhar]

I'd be grateful if you could provide a ds.xml example that uses AdvancedCriteria?

Thank you.

[Isomorphic]

There was a bug here which was causing your "isNull" clause to be ignored because it does not specify a "value". We have fixed this, and the fix will be in nightly builds of 8.3/3.1 and later as of tomorrow, 7/10. Or, a quick workaround would be to provide a "value" in that clause (any value - it will be ignored downstream).

Incidentally, the most recent operationBinding you posted is the very example of usage that you've been looking for - that is indeed how to do it. As for a doc or webpage that describes the use of AdvancedCriteria in an operationBinding, it is documented in the SmartClient reference docs here