Resultset not open. Verify Autocommit is OFF. Apache Debry

Question

Hi,

I am using apache derby for my database. I am able to perform inserts into the database. The following is the excerpt from the code that attempts to display the contents of my only table 'MAINTAB'. The instance of java.sql.Connection is 'dbconn'.

    ResultSet word;

    Statement query;

    String getData="SELECT THEWORD FROM MAINTAB";
    try{
        System.out.println(dbconn.getAutoCommit());
        query = dbconn.createStatement();
        word = query.executeQuery(getData);
        query.close();

        dbconn.setAutoCommit(false);
        System.out.println(dbconn.getAutoCommit());

        for(;word.next();)
            System.out.println(word.getString(1));

    }catch(Throwable e){
        System.out.println("Table fetch failed or result data failed");}

And the following is the output.

org.apache.derby.jdbc.EmbeddedDriver loaded.
Database testDB connected
true
false
Table fetch failed or result data failed

---SQLException Caught---

SQLState:   XCL16
Severity: 20000
Message:  ResultSet not open. Operation 'getString' not permitted. Verify that autocommit is OFF.
java.sql.SQLException: ResultSet not open. Operation 'getString' not permitted. Verify that autocommit is OFF.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.ConnectionChild.newSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedResultSet.checkIfClosed(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedResultSet.getString(Unknown Source)
Closed connection
    at test.ShowData.main(ShowData.java:30)
Caused by: java.sql.SQLException: ResultSet not open. Operation 'getString' not permitted. Verify that autocommit is OFF.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(



Unknown Source)
    ... 9 more
Database shut down normally

---

When, it first asked to verify if AUTOCOMMIT is OFF, I have found from the Derby Documentation that AUTOCOMMIT is turned ON by default to any connection. So, I've turned it off using dbconn.setAutoCommit(false). Still, the error is thrown.

The output before the error explains that the result set was fetched without any error. Also, please note that the same error is thrown even if I do not set the AutoCommit to false. Between, I am running derby on eclipse.

Answer 1

The problem is that you have closed your query before reading your resultset. Closing the query, closes the resultset, hence why you get the "ResultSet not open" error. You should close the query right at the end, in a finally block:

ResultSet word;

Statement query=null;

String getData="SELECT THEWORD FROM MAINTAB";
try{
    System.out.println(dbconn.getAutoCommit());
    query = dbconn.createStatement();
    word = query.executeQuery(getData);


    dbconn.setAutoCommit(false);
    System.out.println(dbconn.getAutoCommit());

    for(;word.next();)
        System.out.println(word.getString(1));

}catch(Throwable e){
    System.out.println("Table fetch failed or result data failed");
} finally{
    if(query!=null) {
        try {
             query.close();
        }
        catch(SQLException ex) {
              System.out.println("Could not close query");
        }
   }
}