Oracle Timestamp / Json conversion

[agalataud]

Hi,

I'm currently using SmartGWT 4.1p.
I'm facing a json conversion issue of database TIMESTAMP fields that leads to exceptions and unresponsive UI.

My data source definition:

<DataSource
    ID="reg_classification"
	serverType="sql"
	tableName="REG_CLASSIFICATION"
>
    <fields>
    	<field name="ID" type="sequence" sequenceName="SQ_REG_CLASSIFICATION" hidden="true" primaryKey="true"/>
    	<field name="REGULATION_ID" type="integer" foreignKey="reg_regulation.ID"/>
    	<field name="REGULATION_NAME" includeFrom="reg_regulation.NAME"/>
    	<field name="CREATED" type="creatorTimestamp" title="$date"/>
    	<field name="CREATED_BY" type="integer" foreignKey="operator.ID"/>
    	<field name="REASON" type="text" />
    	<field name="NB_PROCESSED" type="integer"/>
    	<field name="NB_QUALIFIED" type="integer"/>
    	<field name="NB_DISQUALIFIED" type="integer"/>
    	<field name="NB_PAPERSEARCH" type="integer"/>
    </fields>
    
    <operationBindings>
    	<operationBinding operationType="fetch" operationId="allCustomerClassifications" dropExtraFields="false">
    		<selectClause>$defaultSelectClause, d.UPDATED, d.UPDATED_BY, a.NAME as RESULT</selectClause>
    		<tableClause>$defaultTableClause, REG_DECISION d, REG_STATUS a</tableClause>
    		<whereClause>$defaultWhereClause 
    			and d.CLASSIFICATION_ID = REG_CLASSIFICATION.ID
    			and d.CUSTOMER_ID = $criteria.CUSTOMER_ID
    			and d.STATUS_ID = a.ID
    		</whereClause>
    	</operationBinding>
    </operationBindings>
</DataSource>

Note that in allCustomerClassifications binding, I'm fetching data from columns that are not defined nor referenced in the the DS fields (d.UPDATED). That makes a difference, because TIMESTAMP fields defined in the DS don't suffer this problem.
The query runs normally, referring to server logs:

=== 2014-03-13 08:50:29,656 [0-15] DEBUG SQLDataSource - [builtinApplication.allCustomerClassifications] JDBC driver windowed select rows 0->75, result size 75. Query: SELECT REG_CLASSIFICATION.CREATED, REG_CLASSIFICATION.CREATED_BY, REG_CLASSIFICATION.ID, REG_CLASSIFICATION.NB_DISQUALIFIED, REG_CLASSIFICATION.NB_PAPERSEARCH, REG_CLASSIFICATION.NB_PROCESSED, REG_CLASSIFICATION.NB_QUALIFIED, REG_CLASSIFICATION.REASON, REG_CLASSIFICATION.REGULATION_ID, REG_REGULATION.NAME AS REGULATION_NAME, d.UPDATED, d.UPDATED_BY, a.NAME as RESULT FROM REG_REGULATION, REG_CLASSIFICATION, REG_DECISION d, REG_STATUS a WHERE ('1'='1') 
    			and d.CLASSIFICATION_ID = REG_CLASSIFICATION.ID
    			and d.CUSTOMER_ID = '12590'
    			and d.STATUS_ID = a.ID
    		 AND REG_CLASSIFICATION.REGULATION_ID = REG_REGULATION.ID ORDER BY REG_CLASSIFICATION.CREATED DESC
=== 2014-03-13 08:50:29,662 [0-15] DEBUG SQLDataSource - [builtinApplication.allCustomerClassifications] Using paging strategy 'jdbcScroll' - scrolling to absolute position 1
=== 2014-03-13 08:50:29,663 [0-15] DEBUG SQLDataSource - [builtinApplication.allCustomerClassifications] Scrolling / positioning took 1ms
=== 2014-03-13 08:50:29,664 [0-15] INFO  DSResponse - [builtinApplication.allCustomerClassifications] DSResponse: List with 1 items

On client side, I never get any result and grid get stuck in loading. I get the following exception:

[ERROR] [application] - 08:50:29.725:TMR8:WARN:ResultSet:isc_ResultSet_15 (created by: (cacheAllData fetch)):Bad data returned, ignoring: "//isc_RPCResponseStart-->[{"affectedRows":0,"data":[{"NB_PAPERSEARCH":0,"REGULATION_ID":3,"CREATED_BY":2,"RESULT":"Specified US Person","NB_PROCESSED":1,"UPDATED_BY":2,"REGULATION_NAME":"FATCA for Companies","UPDATED":2014-03-13 08:46:36.94,"ID":8,"CREATED":"$$DATESTAMP$$:1394696796000","NB_QUALIFIED":1,"NB_DISQUALIFIED":0}],"endRow":1,"invalidateCache":false,"isDSResponse":true,"operationType":"fetch","queueStatus":0,"startRow":0,"status":0,"totalRows":1},{"affectedRows":0,"data":[{"NAME":"(System)","DESCRIPTION":"This is the main internal system operator.","CONNECTED":false,"APPROVED":true,"BUNIT_NAME":"(System)","CREATED":"$$DATESTAMP$$:1394092605272","ENABLED":true,"LOGIN":"(SYSTEM)","LANGUAGE_CODE":"en","UPDATED":"$$DATESTAMP$$:1394092605272","ID":1,"BUNIT_ID":1,"ACTIVE":1},{"NAME":"(Master)","DESCRIPTION":"This in the main system administrator.","CONNECTED":true,"APPROVED":true,"BUNIT_NAME":"(System)","CREATED":"$$DATESTAMP$$:1394092605279","ENABLED":true,"CREATED_BY":1,"LAST_LOGIN":"$$DATESTAMP$$:1394696568514","LOGIN":"(MASTER)","UPDATED_BY":1,"LANGUAGE_CODE":"en","UPDATED":"$$DATESTAMP$$:1394092605279","ID":2,"BUNIT_ID":1,"ACTIVE":1,"LOCKED":false},{"NAME":"xxx","CONNECTED":false,"APPROVED":true,"BUNIT_NAME":"(System)","CREATED":"$$DATESTAMP$$:1394093488000","SECURITY_LEVEL_ID":1,"ENABLED":true,"CREATED_BY":2,"LAST_LOGIN":"$$DATESTAMP$$:1394524864967","LOGIN":"xxx","UPDATED_BY":2,"LANGUAGE_CODE":"en","UPDATED":"$$DATESTAMP$$:1394093488000","ID":113,"SECURITY_LEVEL_NAME":"(System)","BUNIT_ID":1,"ACTIVE":1,"LOCKED":false},{"NAME":"xxx","CONNECTED":false,"APPROVED":true,"BUNIT_NAME":"(System)","CREATED":"$$DATESTAMP$$:1394445699000","SECURITY_LEVEL_ID":1,"ENABLED":true,"CREATED_BY":2,"LAST_LOGIN":"$$DATESTAMP$$:1394616270000","LOGIN":"xxx","UPDATED_BY":2,"LANGUAGE_CODE":"en","UPDATED":"$$DATESTAMP$$:1394616661000","ID":114,"SECURITY_LEVEL_NAME":"(System)","BUNIT_ID":1,"ACTIVE":1,"LOCKED":false},{"NAME":"XXXTestAudit","CONNECTED":false,"APPROVED":true,"BUNIT_NAME":"(System)","CREATED":"$$DATESTAMP$$:1394450994000","SECURITY_LEVEL_ID":1,"ENABLED":true,"CREATED_BY":113,"LAST_LOGIN":"$$DATESTAMP$$:1394455857865","LOGIN":"XXXTestAudit","UPDATED_BY":113,"LANGUAGE_CODE":"en","UPDATED":"$$DATESTAMP$$:1394455747000","ID":115,"SECURITY_LEVEL_NAME":"(System)","BUNIT_ID":1,"ACTIVE":1,"LOCKED":false},{"NAME":"xxx","CONNECTED":false,"APPROVED":true,"BUNIT_NAME":"Default","CREATED":"$$DATESTAMP$$:1394553242000","SECURITY_LEVEL_ID":1,"ENABLED":true,"CREATED_BY":2,"LAST_LOGIN":"$$DATESTAMP$$:1394553828493","LOGIN":"paa","UPDATED_BY":2,"LANGUAGE_CODE":"en","UPDATED":"$$DATESTAMP$$:1394553609000","ID":116,"EMAIL":"xxx@xxx.com","SECURITY_LEVEL_NAME":"(System)","DEPARTMENT":"IT","BUNIT_ID":219,"ACTIVE":1,"LOCKED":false},{"NAME":"Guest","DESCRIPTION":"This in the guest operator. To be used for testing only.","CONNECTED":false,"APPROVED":true,"BUNIT_NAME":"Default","CREATED":"$$DATESTAMP$$:1394619816612","ENABLED":true,"CREATED_BY":1,"LAST_LOGIN":"$$DATESTAMP$$:1394640019104","LOGIN":"Guest","UPDATED_BY":1,"LANGUAGE_CODE":"en","UPDATED":"$$DATESTAMP$$:1394619816612","ID":118,"BUNIT_ID":222,"ACTIVE":1,"LOCKED":false}],"endRow":7,"invalidateCache":false,"isDSResponse":true,"operationType":"fetch","queueStatus":0,"startRow":0,"status":0,"totalRows":7}]//isc_RPCResponseEnd"

I copied and pasted json in a validator that pointed to the problem:

Parse error on line 12:
...     "UPDATED": 2014-03-1308: 46: 36.94,
-----------------------^
Expecting '}', ',', ']'

A sample row that shows better the conversion problem happening only on the joined table field (UPDATED), not on the source table field defined in DS (CREATED):

"data": [
        {
            "NB_PAPERSEARCH": 0,
            "REGULATION_ID": 3,
            "CREATED_BY": 2,
            "RESULT": "Specified US Person",
            "NB_PROCESSED": 1,
            "UPDATED_BY": 2,
            "REGULATION_NAME": "FATCA for Companies",
            "UPDATED": 2014-03-1308: 46: 36.94,
            "ID": 8,
            "CREATED": "$$DATESTAMP$$:1394696796000",
            "NB_QUALIFIED": 1,
            "NB_DISQUALIFIED": 0
        }
    ]

This problem doesn't show up in MS Sql Server (fields defined as DATETIME).
For now, the workaround I found is to cast the value with CAST(d.UPDATED as DATE) or TO_DATE(TO_CHAR(d.UPDATED,'YYYY-MM-DD'),'YYYY-MM-DD')

Regards

[Isomorphic]

It looks like Oracle has changed the behavior of their JDBC driver yet again (they severely broke backcompat in an 11.1/11.2 dot revision). We'll see if there's a way to work around the new behavior, but in the meantime, your conversion to a proper date string is the right approach.

[agalataud]

Hi,

Thanks for the quick answer.
We're currently working with Oracle 11.2.0.1.0, so I assume this version of our driver is affected by the problem.

Can you confirm that only fields not declared in the datasource can suffer from this problem (auto discovery of data type based on metadata provided by the driver?)

Regards

[Isomorphic]

We've fixed this now on all current branches - please try tomorrow's nightly buiilds.

To confirm, yes, this issue only arose with Oracle TIMESTAMP columns that had no corresponding DataSource field to provide the framework with definitive type information.