Creating a connection with Java ODBC results in a java.sql.SQLException: Invalid Cursor Type exception

Question

I am trying to create a Java program that accesses a ODBC datasource. Using the following code...

Connection conn;

try {
    Driver d = (Driver)Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
    DriverManager.registerDriver(d);
    String URL = "jdbc:odbc:AR System ODBC Data Source";
    conn = DriverManager.getConnection(URL);
} catch (SQLException | InstantiationException | IllegalAccessException | ClassNotFoundException e) {
    Logger.error(this, e);
} 

Statement s = null;
ResultSet rs = null;

try {
    s = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    rs = s.executeQuery("select count(*) as rows from table");

    if (rs.next()) {
        System.out.print("Count of all rows is " + rs.getInt("rows"));
    }
} catch (SQLException e) { 
     e.printStackTrace();
} finally {
    DBUtils.safelyClose(s, rs);
}

...I get the following Exception:

java.sql.SQLException: The result set type is not supported.
   at sun.jdbc.odbc.JdbcOdbcStatement.initialize(Unknown Source)
   at sun.jdbc.odbc.JdbcOdbcConnection.createStatement(Unknown Source)
   at sun.jdbc.odbc.JdbcOdbcConnection.createStatement(Unknown Source)
   at com.csc.remedyarchiver.data.input.ODBCConnection.main(ODBCConnection.java:38)

Originally, when I was attempting to resolve this on my own, I was using the empty argument createStatement() call but this lead to the above exception (hence is why I used the TYPE_FORWARD_ONLY result set type but still the same result):

Is there anything else I can try with this or does this need a different approach?

Answer 1

From the Oracle Documentation for retrieving data sets:

Note: Not all databases and JDBC drivers support all ResultSet types. The method DatabaseMetaData.supportsResultSetType returns true if the specified ResultSet type is supported and false otherwise.

https://docs.oracle.com/javase/tutorial/jdbc/basics/retrieving.html

So to start, you may want to check that your connection supports the result sets you are trying to use.

connection.getMetaData().supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY)
connection.getMetaData().supportsResultSetType(ResultSet.CONCUR_READ_ONLY)

The above methods both return true for my configuration. And for this reason the code you have posted works in my environment. I would bet that one of them (or both) will return false for you and my guess is that it is a problem with your database itself or the version of Oracle's JDBC driver you are using. You may want to ensure you are using the latest OJDBC driver which you can get from here:

http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

If you have verified that you are using the latest driver, I would verify the version of the Database you are using, and the result sets supported by it.