Getting Invalid cursor state exception in java

Question

i ran select command and printed the result in system.out using below code. was getting expected result with invalid cursor error. could you please any one tell, why this error was occurred after printing the expected result and how to fix it? code:

try
        {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + "path";
            conn = DriverManager.getConnection(url);
    stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
    String select="SELECT DISTINCT col1,col2 FROM Tablename";                    
    ResultSet rs = stmt.executeQuery(select);
    ResultSetMetaData rsmd = rs.getMetaData();
    int columnsNumber = rsmd.getColumnCount();                
    String columnValue;                                         
    while (rs.next())
    {  
    for (int i = 1; i <= columnsNumber; i++) {                           
        columnValue= rs.getString(i);
        System.out.print(columnValue+" ");
        }                 
    }
}
catch(SQLException exc){
            exc.printStackTrace();
}

output:

    test1 result1
    test2 result2
    test3 result3
    java.sql.SQLException: [Microsoft][ODBC Driver Manager] Invalid cursor state
        at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6964)
        at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7121)
        at sun.jdbc.odbc.JdbcOdbc.SQLGetDataString(JdbcOdbc.java:3914)
        at sun.jdbc.odbc.JdbcOdbcResultSet.getDataString(JdbcOdbcResultSet.java:5697)
        at sun.jdbc.odbc.JdbcOdbcResultSet.getString(JdbcOdbcResultSet.java:353)

Answer 1

I was able to recreate your issue. It appears to be an "unfortunate behaviour" of the JDBC-ODBC Bridge and the Access ODBC driver when working with SELECT DISTINCT ... queries and ResultSet.TYPE_SCROLL_SENSITIVE.

The following kluge seems to work around the issue for me:

String select="SELECT * FROM (SELECT DISTINCT FirstName,LastName FROM Clients)";

Switching from ResultSet.TYPE_SCROLL_SENSITIVE to ResultSet.TYPE_FORWARD_ONLY also appears to avoid the issue.

However, since the JDBC-ODBC Bridge is obsolete and has been removed from Java 8 you might consider using the UCanAccess JDBC driver instead. For more details see

Manipulating an Access database from Java without ODBC