Oracle JDBC, TIMESTAMP and WebRowSetImpl

29 Dec 2010

A colleague at work had an interesting problem recently when retrieving a result set from an Oracle 10.x database via JDBC and translating it into XML using the com.sun.rowset.WebRowSetImpl class.

The basic code was something like this:

ResultSet rset = stmt.executeQuery("select * from ..");
WebRowSetImpl wrs = new WebRowSetImpl() ;
wrs.populate(rset) ;
wrs.writeXml(System.out) ;

All well and good, this worked very nicely for various arbitrary queries. However, when a query contained an Oracle TIMESTAMP column the code threw the following exception:

java.lang.ClassCastException: oracle.sql.TIMESTAMP cannot be cast to java.sql.Timestamp
at com.sun.rowset.CachedRowSetImpl.getTimestamp(CachedRowSetImpl.java:2262)
at com.sun.rowset.internal.WebRowSetXmlWriter.writeValue(WebRowSetXmlWriter.java:419)
at com.sun.rowset.internal.WebRowSetXmlWriter.writeData(WebRowSetXmlWriter.java:333)
at com.sun.rowset.internal.WebRowSetXmlWriter.writeRowSet(WebRowSetXmlWriter.java:130)
at com.sun.rowset.internal.WebRowSetXmlWriter.writeXML(WebRowSetXmlWriter.java:81)
at com.sun.rowset.WebRowSetImpl.writeXml(WebRowSetImpl.java:145)
at com.artechra.OraTimestampTest.runQuery(OraTimestampTest.java:66)
at com.artechra.OraTimestampTest.main(OraTimestampTest.java:15)

How rather odd – Timestamp is a type recognised by JDBC, other types work OK with WebRowSetImpl, Oracle JDBC works fine with Timestamps … what’s going on?

It turns out that the Oracle JDBC driver behaves a bit oddly with some of the database types that map to Oracle specific types. Specifically, what happens is:

  • Calling ResultSet.getTimestamp(<n>) returns a java.sql.Timestamp object
  • Calling ResultSet.getMetaData().getColumnType(<n>) returns java.sql.Types.TIMESTAMP
  • However if you call ResultSet.getObject(<n>) on the same column, the Oracle JDBC driver returns an oracle.sql.TIMESTAMP column

Inside WebRowSetImpl it extracts all of the data from the ResultSet object, using getObject() and extracts the type data using getMetaData(). Then when processing the data, it looks at the metadata and expects to find a java.sql.Timestamp object ... oops. In fact, it finds an oracle.sql.TIMESTAMP object, hence the error.

After quite a bit of reading, it turns out that thankfully there is a simple solution - set the oracle.jdbc.J2EE13Compliant system property to the value "true" and the JDBC driver will magically return a java.sql.Timestamp object from getObject() and all is well.

You can find out more about the mysteries and mystique of the Oracle JDBC drivers in their FAQ and the comprehensive Oracle Database JDBC Developer's Guide and Reference (the tip about the J2EE13Compliant property was in chapter 13).