Oracle oracle.sql.DATE not supported [SPR-2135]

[spring-projects-issues]

Roman Kalukiewicz opened SPR-2135 and commented

JdbcTemplate.queryForList returns rows with oracle.sql.DATE instead of java.sql.Timestamp so I have

Caused by: java.lang.ClassCastException: oracle.sql.DATE
at test.ServiceImpl.getRows(ServiceImpl.java:41)
at test.Bean.getRows(Bean.java:27)

This problem is handled for oracle.sql.TIMESTAMP in org.springframework.jdbc.support.JdbcUtils class, but still exists for oracle.sql.DATE.

In Oracle my database column is declared as TIMESTAMP(6).

Correct behavior is to convert oracle.sql.DATE to java.sql.Timestamp the same way it is done for oracle.sql.TIMESTAMP.

---

1 votes, 0 watchers

[spring-projects-issues]

Rick Evans commented

Hi Roman

This works fine for me; I am thus failing to reproduce this error.

I am using Oracle 9i, the ojdbc14.jar (Oracle JDBC Driver version - "10.2.0.1.0"), and the Spring CVS HEAD. My relation has only one field, a TIMESTAMP(6), and when I select from it using jdbcTemplate.queryForList I get back a 'class java.sql.Timestamp'. I'll try again in a minute on a 'proper' relation (i.e. more than one field) but I am dubious that that will surface the issue.

Could you perhaps send me a test case that replicates the issue? Or a snippet of your jdbc access code? Or the CREATE TABLE statement from the offending relation.

Cheers
Rick

[spring-projects-issues]

Roman Kalukiewicz commented

I think this oracle JDBC driver might be the problem here. As my ojdbc14.jar states it is "Oracle JDBC Driver version - 9.0.2.0.0".
so maybe there are differencies betwen those versions, and v. 9.0 returns oracle.sql.DATE for such column, while v. 10.2 returns oracle.sql.TIMESTAMP.

Anyway even if you cannot reproduce this error there should be no problem to fix this exacly the same way it is fixed for TIMESTAMP. You can believe me, that oracle returns this type ;)

Cheers
Roman

[spring-projects-issues]

Thomas Risberg commented

It's definitely a driver issue - it seems like Oracle can't make up their mind on what a getObject should return. Here is a sample of various driver versions:

==> 10.1.0.3.0
aDATE: java.sql.Date
aTIMESTAMP: oracle.sql.TIMESTAMP

==> 10.2.0.1.0
aDATE: java.sql.Date
aTIMESTAMP: oracle.sql.TIMESTAMP

==> 9.2.0.4.0
aDATE: java.sql.Timestamp
aTIMESTAMP: oracle.sql.DATE

==> 9.2.0.3.0
aDATE: java.sql.Timestamp
aTIMESTAMP: oracle.sql.TIMESTAMP

The switch from oracle.sql.TIMESTAMP in 9.2.0.3 to oracle.sql.DATE in 9.2.0.4 seems to be the problem here. Hard to figure out the reasoning behind this change, but I guess we'll have to accommodate this somehow.

[spring-projects-issues]

Andreas Senft commented

I also had my fun with these changes already :-/

See here for informations about this: http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm
In the table of contents have a look at the entry "Simple Data Types", subentry "What is going on with DATE and TIMESTAMP?"

Regards,
Andreas

[spring-projects-issues]

Thomas Risberg commented

Andreas,

Thanks for the link. Unfortunately that entry is a bit misleading, and so is the JDBC driver metadata. No other way than to experiment and go from there. Here is what I get back from thee different driver versions:

ColumnTypeName: getObject Classname / MetaData Classname

==> 9.2.0.3.0
DATE: java.sql.Timestamp / java.sql.Timestamp
TIMESTAMP: oracle.sql.TIMESTAMP / oracle.sql.TIMESTAMP

==> 9.2.0.5.0
DATE: java.sql.Timestamp / java.sql.Timestamp
TIMESTAMP: oracle.sql.DATE / oracle.sql.TIMESTAMP

==> 10.2.0.1.0
DATE: java.sql.Date / java.sql.Timestamp
TIMESTAMP: oracle.sql.TIMESTAMP / oracle.sql.TIMESTAMP

[spring-projects-issues]

Thomas Risberg commented

Added support to retrieve java.sql.Timestamp instead of oracle.sql.DATE (needed for 9i drivers since 9.2.0.4)