0

I have looked at every single example on SO (and also looked elsewhere) and I'm stumped about this one.

tblNodes is the MS Access dbase table for storage of a JTree... at read-in I'm trying to find all cases where there is anomalous case of more than one record having the same parent ID and the same sibling index.

This is my SSCCE, although obviously it's not that useful without the dbase itself. But I think anyone with knowledge in this area will find the output pretty head-scratching.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class CursorProb {

    public static void main(String[] args) throws SQLException {
        // normalise where we find any duplicate sib_index+parent ID pairings...

        // this more complex SQL statement lists the parent nodes involved
//        String dup_sib_indices_sql = "SELECT Parent FROM ( SELECT Parent, SiblingIndex, Count(*) " +
//                "FROM tblNodes GROUP BY Parent, SiblingIndex HAVING Count(*)>1 ) AS ParentsToNormalise GROUP BY Parent";

        // but, bafflingly, even this simpler one produces the "Invalid cursor state" error 
        String dup_sib_indices_sql = 
                "SELECT tblNodes.Parent, tblNodes.SiblingIndex FROM tblNodes GROUP BY tblNodes.Parent, tblNodes.SiblingIndex";

        String data_source =  "jdbc:odbc:Copy of kernel3_1 DATA $USER";
        Connection dbase_conn = DriverManager.getConnection( data_source, "", "");
        PreparedStatement ps_dup_sib_indices = dbase_conn.prepareStatement( dup_sib_indices_sql, 
                java.sql.ResultSet.TYPE_SCROLL_SENSITIVE,  java.sql.ResultSet.CONCUR_READ_ONLY );
// NB various combos tried here:       
//              java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,  java.sql.ResultSet.CONCUR_UPDATABLE ); ... etc.

        ResultSet rs = ps_dup_sib_indices.executeQuery();

        // rs.beforeFirst(); <-- makes no difference
        int i = 0;
        while ( rs.next() ){
            int parent_id = rs.getInt( "Parent" );
            System.out.println( "=== parent_id " + parent_id + ", i " + i );
            i++;
        }
        System.out.println( "=== Never get to here..." );
    }
}

This is the output:

=== parent_id 0, i 0
=== parent_id 1, i 1
...
=== parent_id 2765, i 491
=== parent_id 2768, i 492
Exception in thread "main" 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.SQLGetDataInteger(JdbcOdbc.java:3818)
    at sun.jdbc.odbc.JdbcOdbcResultSet.getDataInteger(JdbcOdbcResultSet.java:5637)
    at sun.jdbc.odbc.JdbcOdbcResultSet.getInt(JdbcOdbcResultSet.java:582)
    at sun.jdbc.odbc.JdbcOdbcResultSet.getInt(JdbcOdbcResultSet.java:600)
    at root.CursorProb.main(CursorProb.java:34)

Running the SQL as a query in the MS Access dbase itself causes no problems (and produces the same output before the error: 493 rows).

I.e. "SELECT tblNodes.Parent, tblNodes.SiblingIndex FROM tblNodes GROUP BY tblNodes.Parent, tblNodes.SiblingIndex;"

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
mike rodent
  • 14,126
  • 11
  • 103
  • 157
  • 2
    What happens if you use `getInt(1)` instead? What happens if you don't specify scrollability and updatability (so it uses the defaults)? – Mark Rotteveel Oct 01 '15 at 09:06
  • @MarkRotteveel You're a genius, Sir! I had already attempted with various combos of "TYPE_SCROLL_INSENSTIVE" and "CONCUR_UPDATABLE" ... but only by removing these params entirely does the error not occur. The error also doesn't occur with the same SQL phrase minus the "GROUP BY" clause. This is a bug, right? Or... given the nature of an RS with a "GROUP BY" does the same thing apply maybe to any RS which by its nature is non-updatable, perhaps? – mike rodent Oct 01 '15 at 11:56
  • The JDBC-ODBC Bridge has its share of quirks, especially when working with the Access ODBC driver. Another example of the same issue can be seen in [this question](http://stackoverflow.com/q/29730766/2144390). As for it being a "bug": That may be true, although it would be a question of whether it is a bug in the JDBC-ODBC Bridge or a bug in Access ODBC (or both). It is also a moot point since the JDBC-ODBC Bridge is obsolete and has been removed from Java 8. – Gord Thompson Oct 01 '15 at 12:37
  • @GordThompson - yep, saw your Java 8 point (I'm still on 7) in one of the questions I looked at... and the new approach to take. Two more upgrades to implement. Sigh. – mike rodent Oct 01 '15 at 12:40
  • Please consider upvoting the [other answer](http://stackoverflow.com/a/29731452/2144390) so this question can be marked as a duplicate (since it really is the same issue). – Gord Thompson Oct 01 '15 at 13:09

0 Answers0