queryForRowSet() can't return full date/time precision?

[slocke]

I have a Query class that can return data based on a query. Initially, this was coded using java.sql.Resultset:


import java.sql.ResultSet;

public class QueryDAO extends JdbcDaoSupport {

public ResultSet loadResultSet(String sSQL)
{
ResultSet rset = null;
try{
Connection conn = getConnection();
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE, ResultSet.CONCUR_READ_ONLY);
rset = stmt.executeQuery(sSQL);
}
catch (java.sql.SQLException e){
e.printStackTrace();
}
return rset;
}

}


I recently switched from ResultSets to SqlRowSet, thusly:


import org.springframework.jdbc.support.rowset.SqlRowSet;

public class QueryDAO extends JdbcDaoSupport {

public SqlRowSet loadSqlRowSet(String sSQL)
{
return getJdbcTemplate().queryForRowSet(sSQL);
}

}


When I did so, I noticed that date fields that were in the ResultSet as java.sql.Timestamp are now being returned as java.sql.Date. The time aspect of the date fields are all being set to 0.

If I query the ResultSet metadata for the date field thusly:

System.out.println("Column Name " + rsmd.getColumnName(i) +
" ; ClassName " + rsmd.getColumnClassName(i) +
" ; Type " + rsmd.getColumnTypeName(i) + "\n");


I get this:

Column Name DISCHARGE_DATE_0 ; ClassName java.sql.Timestamp ; Type DATE

If I query the contents of the discharge_date field in WinSQL, I get this: "2000-07-08 08:05:00"

If I do a .getTimestamp() on the field, I get this: "2000-07-08 08:05:00.0" (seems to match what WinSQL produces).

Now, here's what SqlRowSet does:

If I query the SqlRowSet metadata, I get this:

Column NameDISCHARGE_DATE_0 ; ClassName java.sql.Date ; Type DATE

It is saying the field is a Date, not a Timestamp.

If I do a .getTimestamp() on the field, I get this: "2000-07-08 00:00:00.0"

You can see that the TIME precision has been lost.

Anyone know why SqlRowSet can't return full precision for date/time fields?

Thanks,

s.l.

[trisberg]

Assuming you are using Oracle here since this looks like a known issue with the 10g JDBC driver.

The 10g driver reports DATE columns as Timestamp in the metadata but if you do a getObject() you get a java.sql.Date. Oracle claims you should use the TIMESTAMP datatype instead if you want the time portion. Drawback here is that getObject() returns an oracle.sql.TIMESTAMP which does not extend any of the standard JDBC classes. To work around this you would have to do something like this:


SqlRowSet rset = jdbcTemplate.queryForRowSet(
"select cast(sysdate as timestamp) from dual");
try {
while (rset.next()) {
System.out.println("-> " +
((oracle.sql.TIMESTAMP)rset.getObject(1)).timestam pValue());
}
} catch (SQLException e) {
e.printStackTrace();
}

We have a workaround for this in the queryForList methods but for queryForRowSet we depend on Sun's RowSet implementation to extract the data from the original ResultSet.

[slocke]

Thanks for the reply, Thomas.

I'm a little hesitant about implementing this workaround. Our app can generate some pretty involved SQL, and implementing this casting method will only further complicate things.

You say this is a known issue with Oracle's JDBC driver. Do you know if anyone has ever entered a TAR for this? If not, is the correct course of action to open a TAR with them? Or should this go to Sun, with their RowSet implementation?

Thanks again,

s.l.

[trisberg]

I opened a TAR way back and the response was to use the getTimestamp method rather than getObject. File another TAR. that's the only way I can see. If there are enough TARs, maybe they will do something.

As for Sun's RowSet implementation, they seem to bee using getObject() which I think is reasonable in this situation. The problem is with the JDBC driver not having the correct metadata for DATE columns and also returning a product specific class for getObject on a TIMESTAMP column.