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?

check changing if to while.just telling while (rs.next()) { System.out.print("Count of all rows is " + rs.getInt("rows")); } — Piumi Wandana
– Piumi Wandana, Commented Oct 13, 2014 at 11:49
@PiumiWandana - That didn't work with the Invalid Cursor Type exception. — Ocracoke
– Ocracoke, Commented Oct 13, 2014 at 11:50
Try adding ResultSet.CLOSE_CURSORS_OVER_COMMIT parameter in createStatement() to see if it helps — Darshan Lila
– Darshan Lila, Commented Oct 13, 2014 at 11:51
Try s = odbc.getConn().createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); — Sunny
– Sunny, Commented Oct 13, 2014 at 11:54
There is a Remedy AR JDBC driver mentioned here that might be of interest to you. — Gord Thompson
– Gord Thompson, Commented Oct 13, 2014 at 15:37

Nthalk · Accepted Answer · 2014-11-24 21:43:35Z

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.

Kibadachi · Accepted Answer · 2014-10-13 14:17:25Z

0

"select count(*) as rows from table" . Try first to replace the alias from "rows" to "Result" or something . Rows is one of the reserved keywords in Oracle . I don't think that select is returning any result besides an error ora 00923

edited Oct 13, 2014 at 14:17

answered Oct 13, 2014 at 14:06

Kibadachi

11 bronze badge

1 Comment

Ocracoke Over a year ago

The code doesn't reach that far as it doesn't initialise the statement object. Nevertheless, it is something to bear in mind.

Robert Bain · Accepted Answer · 2014-10-23 19:02:26Z

0

Would it be worth trying the following, firstly to confirm that it's unsupported, then again with differing ResultSet types to find one that is?

System.out.println(conn.getMetaData().supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY));

answered Oct 23, 2014 at 19:02

Robert Bain

9,6668 gold badges46 silver badges66 bronze badges

2 Comments

Ocracoke Over a year ago

For all three ResultSet types that Java 1.7 has, they all return false.

Robert Bain Over a year ago

If they're returning false, it's never going to work for you. Perhaps an issue with the ODBC driver you're using? This guy seems to have the same problem and has worked around it using a different JDBC driver. mirthcorp.com/community/forums/archive/index.php/t-3966.html

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related