need suggestion for datasource field conversion

[susanpemble]

Using SmartGWT Power 3.0 (NB from 11/08/12)

I have a number field in an Oracle DB that needs to be represented as a java.sql.Timestamp value in both a ListGrid and a FilterBuilder. Originally I used createRecordComponent() to format the value in the ListGrid but that left undesirable behavior in the FilterBuilder.

I've tried using a customSelectExpression in the ds.xml

<field name="TIMESTMP" type="datetime" customSelectExpression="CAST(TIMESTMP as NUMBER)"/>

but that doesn't work -- conversion fails.

There are so many approaches to this task (another strategy in ds.xml? some sort of handler on the Client side? something along the lines of the ORMDataSource sample in the Showcase?) I am hoping you might be able to make a suggestion.

[Isomorphic]

What behavior do you need for this field that differs from the default behavior of a datetime field (which represents a timestamp)?

[susanpemble]

I wasn't clear; all I need is the default behavior for a datetime field. Where is the best place to transform a number into a datetime in order to get that behavior?

[Isomorphic]

Depending on how the number is stored, it's likely you'll be able to use dataSourceField.sqlStorageStrategy.

[susanpemble]

Thanks for that - I tried it and get this:

=== 2013-02-20 18:52:49,872 [l0-0] WARN  SQLTransform - [builtinApplication.loggingEvent_fetch] Unable to parse a valid date, time or datetime out of value 1354112047207 using format string yyyyMMdd

Looks like I need to set the format string now.

We're using Logback, so this field's data is not manipulated or customized in any way. I mention that because I would imagine that other people have had this problem and solved it.

In my research I found that Oracle (http://docs.oracle.com/cd/E11882_01/...TIMESTAMP.html) reference shows this:

The TIMESTAMP class provides conversions between the Oracle Date (ldx_t) data type and Java classes java.sql.Date, java.sql.Time, java.sql.Timestamp

The internal data for this object is stored as a eleven byte array in the super class' storage area. The bytes are arranged as follows:
      Byte       Represents
       0         Century (119 for 1990)
       1         Decade  (190 for 1990)
       2         Month
       3         Day      
       4         Hour
       5         Minute
       6         Seconds
       7         Nanoseconds
       8         Nanoseconds
       9         Nanoseconds
      10        Nanoseconds

But I find that the java.text.SimpleDateFormat required by DataSourceField.sqlDataFormat() does not support the Century, Decade or Nanosecond mapping. Could your API be extended to support those fields, or do I need to go another route with this issue?

Thanks-
Susan

[Isomorphic]

As the docs for sqlStorageStrategy mention, sqlDateFormat is the property that controls it.

However it looks like your timestamps are in epoch time, and Java's SimpleDateFormat has no way of producing this.

In 4.0 there's a built-in setting to store via epoch time. For 3.1, this user figured out a strategy using customSelectExpression (see post #3).

[susanpemble]

That post you pointed me to was exactly what I needed.

[susanpemble]

Unfortunately we're using Oracle instead of PostgreSQL. I have this:

<field name="TIMESTMP" type="datetime"  customSelectExpression="TO_TIMESTAMP('01/01/1970 00:00:00.000','MM/dd/yyyy HH24:MI:SS.FF3')+numtodsinterval(TIMESTMP/1000,'SECOND')"/>

which results in

=== 2013-02-21 15:50:26,634 [0-20] DEBUG SQLDataSource - [builtinApplication.loggingEvent_fetch] JDBC driver windowed select rows 0->75, result size 75. Query: SELECT LOGGING_EVENT.CLASS_NAME, LOGGING_EVENT.ELEMENT_ID, LOGGING_EVENT.ENVIRONMENT, LOGGING_EVENT.EVENT_CAT_ID, LOGGING_EVENT.EVENT_ID, LOGGING_EVENT.FORMATTED_MESSAGE, LOGGING_EVENT.JOB_ID, LOGGING_EVENT.LEVEL_STRING, LOGGING_EVENT.LOGGER_NAME, LOGGING_EVENT.LOG_TYPE, LOGGING_EVENT.SATELLITE_ID, LOGGING_EVENT.SITE_ID, LOGGING_EVENT.SOURCE_HOST, TO_TIMESTAMP('01/01/1970 00:00:00.000','MM/dd/yyyy HH24:MI:SS.FF3')+numtodsinterval(TIMESTMP/1000,'SECOND') AS TIMESTMP, LOGGING_EVENT.USER_ID, LOGGING_EVENT.ZONE_ID FROM LOGGING_EVENT WHERE ('1'='1') ORDER BY TIMESTMP DESC
=== 2013-02-21 15:50:26,641 [0-20] WARN  RequestContext - dsRequest.execute() failed: 
java.lang.NumberFormatException: For input string: "2012-11-29 18:49:17.91"
	at sun.misc.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:1222)
	at java.lang.Float.parseFloat(Float.java:422)
	at com.isomorphic.sql.SQLTransform.toAttributeMap(SQLTransform.java:201)
	at com.isomorphic.sql.SQLTransform.toListOfMapsOrBeans(SQLTransform.java:674)
	at com.isomorphic.sql.SQLTransform.toListOfMapsOrBeans(SQLTransform.java:484)
	at com.isomorphic.sql.SQLTransform.toListOfMapsOrBeans(SQLTransform.java:478)
	at com.isomorphic.sql.SQLTransform.toListOfMapsOrBeans(SQLTransform.java:465)
	at com.isomorphic.sql.SQLDataSource.executeWindowedSelect(SQLDataSource.java:1998)
	at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1317)
	at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:293)
	at com.isomorphic.sql.SQLDataSource.executeFetch(SQLDataSource.java:237)
	at com.isomorphic.datasource.DataSource.execute(DataSource.java:1289)

This looks like it's the SmartGWT Datetime type that can't support fractional seconds?

[Isomorphic]

You don't show it in your <field> declaration, but this parseFloat() method call is only reachable if you still had sqlStorageStrategy declared as "integer".

[susanpemble]

You're right of course, when I removed the sqlStorageStrategy="number", left over from the initial suggestion you made, everything works perfectly.

Thank you very much!