gt-jdbc-oracle loses time information when retrieving FeatureType from Oracle 10.2.0.4.0

[Kevin Brown]

I am using geotools 2.5 and querying against an oracle 10.2.0.4.0 instance.  I have created a FeatureType based on a schema in the oracle DB and all seems to work fine except fields that have a type of "Date"  The actual day comes back correct but the time of day information is lost.  According to the oracle jdbc FAQ at http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.html#08_01 this is expected behavior for oracle version 10 because the Date type in oracle maps to the Date type in SQL but the types are not the same (Oracle Date should map to a SQL Timestamp like it does in oracle 11.)

The FAQ recommends switching to using Timestamp as the type for the column but right now I cannot do this because we have a huge codebase accessing these schemas/tables with these columns as Date types.  We also cannot upgrade to oracle 11 for basically the same reason.  I have looked into how to implement the other two methods suggested by oracle to fix this (using getTimestamp() instead of getObject() or using OracleStatement instead of Statement) as part of the gt-jdbc call chain but so far have had no luck.

Does anyone have any suggestions on how I can fix this so I can get my seconds back?

Thanks in advance,

Kevin

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Geotools-gt2-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users

[Jody Garnett-2]

Some javascript/style in this post has been disabled (why?)

Hi Kevin

Looks like you will need to make a patch to introduce either an additional optional connection parameter; or a Hint for Query allowing you to set a flag to force the code to always request an sql timestamp.

Inside the oracle datastore (which datastore are you using?) you would then need to respect this flag and map result set values into feature attributes using getTimestamp().

Jody

On 21/10/2009, at 6:32 AM, Kevin Brown wrote:

I am using geotools 2.5 and querying against an oracle 10.2.0.4.0 instance.  I have created a FeatureType based on a schema in the oracle DB and all seems to work fine except fields that have a type of "Date"  The actual day comes back correct but the time of day information is lost.  According to the oracle jdbc FAQ at http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.html#08_01 this is expected behavior for oracle version 10 because the Date type in oracle maps to the Date type in SQL but the types are not the same (Oracle Date should map to a SQL Timestamp like it does in oracle 11.)

The FAQ recommends switching to using Timestamp as the type for the column but right now I cannot do this because we have a huge codebase accessing these schemas/tables with these columns as Date types.  We also cannot upgrade to oracle 11 for basically the same reason.  I have looked into how to implement the other two methods suggested by oracle to fix this (using getTimestamp() instead of getObject() or using OracleStatement instead of Statement) as part of the gt-jdbc call chain but so far have had no luck.

Does anyone have any suggestions on how I can fix this so I can get my seconds back?

Thanks in advance,

Kevin
------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference_______________________________________________
Geotools-gt2-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Geotools-gt2-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users