TIMESTAMP field in Oracle - usage in SmartGWT .ds.xml file

[Blama]

Hello Isomorphic,

I'm doing some calculations in the background using Oracle (11g) Stored Procedures. These SPs log their start- and endtimes in TIMESTAMP fields:

DDL:

	...
	STARTDATE TIMESTAMP (5), 
	ENDDATE TIMESTAMP (5),
	...

The DS Generator creates this .ds.xml:

	...
	<field name="STARTDATE" [B]length="11" type="text"[/B] />
	<field name="ENDDATE" [B]length="11" type="text"[/B] />
	...

Is this expected behaviour? I found this thread and thought that it might be an error. I'm using ojdbc6.jar with Apache Tomcat/7.0.50 and SmartGWT v9.1p_2014-10-29 under Win 8.1/Java jdk1.7.0_71.

MANIFEST.FM inside ojdbc6.jar says:

Manifest-Version: 1.0
Ant-Version: Apache Ant 1.6.5
Created-By: 1.5.0_51-b10 (Sun Microsystems Inc.)
Implementation-Vendor: Oracle Corporation
Implementation-Title: JDBC
Implementation-Version: 11.2.0.3.0
Repository-Id: JAVAVM_11.2.0.4.0_LINUX.X64_130711
Specification-Vendor: Sun Microsystems Inc.
Specification-Title: JDBC
Specification-Version: 4.0
Main-Class: oracle.jdbc.OracleDriver
sealed: true

Name: oracle/sql/converter/
Sealed: false

Name: oracle/sql/
Sealed: false

Name: oracle/sql/converter_xcharset/
Sealed: false

Name: oracle/replay/driver/
Sealed: false

Further Data Dictionary information:

select * from user_tab_cols where table_name = 'T_SYS_CALC_BACKLOG' AND column_name like '%DATE';

TABLE_NAME	COLUMN_NAME	DATA_TYPE	DATA_TYPE_MOD	DATA_TYPE_OWNER	DATA_LENGTH	DATA_PRECISION	DATA_SCALE	NULLABLE	COLUMN_ID	DEFAULT_LENGTH	DATA_DEFAULT	NUM_DISTINCT	LOW_VALUE	HIGH_VALUE	DENSITY	NUM_NULLS	NUM_BUCKETS	LAST_ANALYZED	SAMPLE_SIZE	CHARACTER_SET_NAME	CHAR_COL_DECL_LENGTH	GLOBAL_STATS	USER_STATS	AVG_COL_LEN	CHAR_LENGTH	CHAR_USED	V80_FMT_IMAGE	DATA_UPGRADED	HIDDEN_COLUMN	VIRTUAL_COLUMN	SEGMENT_COLUMN_ID	INTERNAL_COLUMN_ID	HISTOGRAM	QUALIFIED_COL_NAME
T_SYS_CALC_BACKLOG	STARTDATE	TIMESTAMP(5)			11		5	Y	13			23	78720A1C0F1A012EABA1C0	78720A1C0F1A0202625A00	0,0434782608695652	6	1	28.10.14 20:39:42	30			YES	NO	10	0		NO	YES	NO	NO	12	13	NONE	STARTDATE
T_SYS_CALC_BACKLOG	ENDDATE	TIMESTAMP(5)			11		5	Y	14			22	78720A1C0F1A013178CB80	78720A1C0F1A0202EBAE40	0,0454545454545455	14	1	28.10.14 20:39:42	22			YES	NO	8	0		NO	YES	NO	NO	13	14	NONE	ENDDATE

Is the generation of type="text" expected behaviour? I did not find a timestamp-fieldtype that is more precise than datetime in the docs.

I know that I can display the values with a CustomSelectExpression and Oracle-CAST, but is this the way to go? Currently, this is fine for me as I only have to display data, but how about writing timestamps (with more than second-precision) from SmartGWT?

Thank you & Best regards,
Blama

[Isomorphic]

This is happening because of a quirk in the Oracle JDBC driver - for native TIMESTAMP columns, it returns a JDBC type of OTHER rather than TIMESTAMP. We can work around this by examining the rest of the metadata - the workround will be present in tomorrow's builds of 4.1p and greater.

If you need sub-second granularity, SQLDataSource can provide millisecond-level storage in native datetime/timestamp columns, but this support is only present in 5.1d. If that is an option for you, you would just need to enhance the auto-derived field in your .ds.xml file by adding the "storeMilliseconds" flag.

If you cannot move to 5.1 right now, consider using CHAR columns in the database combined with sqlStorageStrategy

[Blama]

Hi Isomorphic,

thanks for your answer.
As I'm currently only displaying these values (generated by Oracle), this is fine for me for now. Once I start using 5.1p, I'll revisit this thread and think about amending all my creatortimestamp- and modifiertimestamp-fields with storeMilliseconds="true".

Great to see that you (or other clients) obliviously stumble over the same problems and you are solving them.

Best regards,
Blama