smartgwt 2.5 fails to map Oracle Timestamp correctly

[msturzen]

Versions:
gwt: 2.3
smartgwt power: 2.5 (final) and 2.5p (2012-04-09)
DB: Oracle 11g R2
Driver: ojdbc6.jar
Browsers: Any (tested with safari, firefox, chrome, ie) in dev mode as well as deployed mode.
Related link: http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#08_01


Dear Isomorphic Team,

we observed some very strange behaviour im smartgwt 2.5.
The client displays columns marked as datetime in ...ds.xml and declared as TIMESTAMP within the ORACLE table without time, or to be precise the time part is always 12:00. That's why we call it the "high noon problem" ;)

Time is displayed correctly if you use ojdbc14.jar or smartgwtee 3.0p (eval, 2012-04-10).



Some observations:

Under failure conditions (sgwt 2.5 + ojdb6) the response contains an instance of ISCDate for the TIMESTAMP column (debugged our DMI before returning the response). The json repsonse on the client receives this as "Date.parseServerDate(04, 04, 2012)" - no time visible.


Under success conditions the TIMESTAMP column is not mapped to an ISCDate. It is either a java.sql.Timestamp (sgwt 2.5 + ojdbc14) or a java.sql.Date (sgwt 3.0 + ojdbc6). Both arrive at the client as "new Date(1337....)" and display the time part correctly.



We concluded from these findings there is a severe misbehaviour in sgwt 2.5 in collaboration with ojdbc6 drivers. Please note, I'm not saying this is a bug in sgwt, because Oracle plays a big part in this issue too.

Since there obviously is a fix available in sgwt 3.0, please apply it to sgwt 2.5 too! You'd save my day (and lots of sleepless nights).



And now for the minimalistic testcase:
Apply the different settings (sgwt 2.5/3.0 and ojdbc14/ojdbc6) as described above and you should observe the same as we did.


Create Table:

CREATE TABLE "TEST_SCHEMA"."COMMENT"
(
  COMMENT_ID                  NUMBER NOT NULL ENABLE,
  COMMENT_DEF_ID           NUMBER NOT NULL ENABLE,
  COMMENT_TEXT              VARCHAR2(512 BYTE) NOT NULL ENABLE,
  COMMENT_OPERATOR_ID   VARCHAR2(100 BYTE) NOT NULL ENABLE,
  COMMENT_CRETIMESTAMP TIMESTAMP (3) NOT NULL ENABLE,
  COMMENT_TYPE               VARCHAR2(10 BYTE) NOT NULL ENABLE,
  PRIMARY KEY (COMMENT_ID) 
)

Datasource ds.xml:

<DataSource ID="COMMENT"
	serverType="sql"
	dbName="Oracle"
	schema="TEST_SCHEMA"
	tableName="COMMENT">

	<fields>
		<field name="COMMENT_ID" primaryKey="true" type="sequence"></field>
		<field name="COMMENT_DEF_ID" type="integer"></field>
		<field name="COMMENT_TEXT" length="512" type="text"></field>
		<field name="COMMENT_OPERATOR_ID" length="45" type="text"></field>
		<field name="COMMENT_TYPE" length="10" type="text"></field>
		<field name="COMMENT_CRETIMESTAMP" type="datetime"></field>
	</fields>
</DataSource>

Entrypoint:

public void onModuleLoad() {
	DataSource ds = DataSource.get("COMMENT");
	
	VLayout layout = new VLayout();
	layout.setSize("800px", "600px");
	
	ListGrid commentsList = new ListGrid();
	commentsList.setMinWidth(1280);
	commentsList.setDefaultWidth(1280);
	commentsList.setWidth("100%");
	commentsList.setHeight100();
	commentsList.setHeaderHeight(20);
	commentsList.setOverflow(Overflow.VISIBLE);
	commentsList.setAlternateRecordStyles(true);
	
	commentsList.setDataSource(ds);
	commentsList.fetchData();
	
	layout.addMember(commentsList);
	
	RootPanel.get("comments").add(layout);
}

I hope this helps to narrow down and squash this annoying (at least for us) issue.

In the meantime: Do you have an idea how to work around it?

Best regards
martin

[msturzen]

Some additional info:

columns in ...ds.xml marked as datetime and declared as DATE in the table display times correctly (HH:mm) no matter which combination of sgwt and ojdbc driver is used.

Cheers
martin

[Isomorphic]

Porting this fix back to 2.5 has been bumped by higher priority tasks many times and is now unlikely to happen unless a customer with support needs the fix, sorry.

[msturzen]

This is bad - very bad.

Is there a work around we can apply so have at least correct time information to the minute (like DATE columns)?

[Isomorphic]

You've already got several:
1. downgrade JDBC drivers
2. upgrade SmartGWT
3. buy support from us so we work around Oracle's issues
4. buy support from Oracle and get their driver fixed (I think you can assume this is the most expensive option)

[msturzen]

Thanks for your help. Very helpful.

[msturzen]

After some elaboration we found a solution.

You can add a "custom select expression" to your field definition in your ds.xml.

This way your oracle TIMESTAMP columns pretend to be DATE columns which are behave as desired with sgwt 2.5 and ojdbc6.jar.

<field name="COMMENT_CRETIMESTAMP" type="datetime" customSelectExpression="CAST(COMMENT_CRETIMESTAMP as DATE)"></field>

The workaround works for us and delivers the desired results (display date and time (to the minute)). I hope other users find it useful too.

Regards
martin

[shortpasta]

This is bad - very bad.

Is there a work around we can apply so have at least correct time information to the minute (like DATE columns)?

Hey Ms Turzen... so you are paying for Oracle but not SmartGWT... But I bet you are getting quicker responses from SmartGWT than Oracle... are you?

[msturzen]

Dear short pasta,

we paid neither and didn't expect responses from either.

The behaviour we observed was indeed very unpleasant no matter who did it wrong.

Regards
martin

[Isomorphic]

Hmm, would you please clarify who you are referring to being unpleasant?

Please bear in mind that this forum contains several threads in which Isomorphic Support investigated and fixed bugs for you despite your not purchasing support, and despite your organization publicly and inaccurately bashing our product on a blog. From our perspective we have been gone well beyond what could be expected.

[msturzen]

I'm sorry if I wasn't clear enough.

Seeing a timestamp displayed without its time part is unpleasant. Nothing more and nothing less. I'd like to add, my statement "this is bad - very bad" solely referred to the fact that there will be no fix in sgwt 2.5 - the version we are stuck to for several reasons.


Regards
martin