1

I have a simple question... Why this code below is not working?

  • JDK version: 1.8.0_92
  • Oracle DB version: 11.2.0.1.0

  • Oracle JDBC driver: ojdbc6.jar ---> I could not find this java code source :( 

    String SQL = "select systimestamp from dual";

Statement statement = null;
ResultSet rs = null;
try {
    statement = getConnection(name).createStatement();
    if (statement != null) {
        rs = statement.executeQuery(SQL);
    }

    // Need to use a CachedRowSet that caches its rows in memory, which
    // makes it possible to operate without always being connected to
    // its data source
    CachedRowSet rowset = new CachedRowSetImpl();
    rowset.populate(rs);
    return rowset;
} catch (SQLException ex) {
    throw new DatabaseException(ex.getMessage(), ex);
} finally {
    safeCloseResultSet(rs);
    safeCloseStatement(statement);
}

The stack trace:

java.sql.SQLException: Invalid SQL type for column
at javax.sql.rowset.RowSetMetaDataImpl.checkColType(RowSetMetaDataImpl.java:114)
at javax.sql.rowset.RowSetMetaDataImpl.setColumnType(RowSetMetaDataImpl.java:459)
at com.sun.rowset.CachedRowSetImpl.initMetaData(CachedRowSetImpl.java:761)
at com.sun.rowset.CachedRowSetImpl.populate(CachedRowSetImpl.java:639)

The line "rowset.populate(rs);" throws a "java.sql.SQLException: Invalid SQL type for column"

The error occurs when I try to execute the query:

select systimestamp from dual

But if I use the code below instead of "rowset.populate(rs);", it works:

rs.getTimestamp(1)

And if I try to execute the query below, everything works well:

select sysdate from dual

So, how can I use the rowset.populate(rs) to get the syscurrenttimestamp?

I start to think that it is a bug of oracle's jdbc implementation...

Sorry about my bad english :)

Jefferson
  • 180
  • 1
  • 2
  • 8
  • I don't know the answer to your question, sorry. I'd just ask - if I may - how precise does this value have to be? SYSDATE contains up to seconds; SYSTIMESTAMP additionally includes fractional seconds with timezone. I'm just hoping that SYSDATE *might* be enough. – Littlefoot Feb 08 '18 at 19:13
  • Could be a bug in the driver. Maybe you should try a newest version of the driver ? Review the README file on drivers download page: http://www.oracle.com/technetwork/database/features/jdbc/default-2280470.html, for example it could be this bug: `11670695 RESULTSET.GETTIMESTAMP() RETURNS WRONG TIMESTAMP TIMESTAMP WITH LOCAL TIME ZONE Possible wrong timestamp with TIMESTAMPLTZ if db and session timezone are equal.` – krokodilko Feb 09 '18 at 04:09
  • Hi @Littlefoot! Thanks for your attention! The problem with SYSDATE is that the time information hh:mm:ss depends of how the Oracle NLS_DATE_FORMAT is configured, and in my case, I can not change it. Anyway I have business rules that require the milliseconds information. – Jefferson Feb 15 '18 at 00:28
  • Hi @krokodilko! Yes! I upgraded the JDBC driver with the [Oracle Database 12c Release 2 (12.2.0.1) drivers](http://www.oracle.com/technetwork/database/features/jdbc/jdbc-ucp-122-3110062.html) (I think that it is the most younger), but the problem still persists. – Jefferson Feb 15 '18 at 00:38
  • 1
    I ended up solving the problem with a palliative solution. Instead of performing the query to return a TIMESTAMP, I made it to return a STRING (`SELECT TO_CHAR(SYSTIMESTAMP, 'DD/MM/YYYY HH24:MI:SS.SSXFF TZR') FROM DUAL`). And then I had to do a parse to TIMESTAMP. I know that it's ugly, but is just until we find a correct solution. I still have hope that someone will help us with this matter. – Jefferson Feb 15 '18 at 00:56
  • @Jefferson please post this comment as the answer (answer your own question), this is a smart and very good workaround, it can be usefull to someone. – krokodilko Feb 15 '18 at 05:51

1 Answers1

0

As @krokodilko suggests, I will post here my comment as an answer:

I ended up solving the problem with a palliative solution. Instead of performing the query to return a TIMESTAMP, I made it to return a STRING:

SELECT TO_CHAR(SYSTIMESTAMP, 'DD/MM/YYYY HH24:MI:SS.SSXFF TZR') FROM DUAL

And then I had to do a parse to TIMESTAMP. I know that it's ugly, but is just until we find a correct solution. I still have hope that someone will help us with this matter.

Jefferson
  • 180
  • 1
  • 2
  • 8