CODEWITHSUNDEEP
javah2resultset
Optical Carrier
Optical Carrier

Reputation: 368

org.h2.jdbc.JdbcSQLException: The object is already closed

For the life of me I cannot see how it "is already closed"

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class RsetTest2 {

    public static void main(String[] args) throws Exception {
        String dbpath = "jdbc:h2:c:/mydb;IFEXISTS=TRUE;DB_CLOSE_ON_EXIT=FALSE;AUTO_SERVER=TRUE";

        Connection conn = null;
        System.setProperty("h2.bindAddress", "127.0.0.1");
        Class.forName("org.h2.Driver");
        conn = DriverManager.getConnection(dbpath, "sa", "sa"); 
        conn.setAutoCommit(false);
        System.out.println("success.  querying database for latest values...");

        Statement qry = conn.createStatement();
        String sql = "select id from CONSTITUENTS where manager = 'abc' limit 1";                               
        ResultSet rset = qry.executeQuery(sql);
        while (rset.next()) {
            int id = rset.getInt("id");
            System.out.println(id);         
            qry.executeUpdate("insert into PAYREQUESTS (constituent, inblock) values (" + id + ", 238)");
        }
        rset.close();
        qry.close();    
    }
}

here is the output:

success.  querying database for latest values...
103
Exception in thread "main" org.h2.jdbc.JdbcSQLException: The object is already closed [90007-196]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.get(DbException.java:155)
    at org.h2.message.DbException.get(DbException.java:144)
    at org.h2.jdbc.JdbcResultSet.checkClosed(JdbcResultSet.java:3208)
    at org.h2.jdbc.JdbcResultSet.next(JdbcResultSet.java:130)
    at RsetTest2.main(RsetTest2.java:22)

where 22 corresponds to the "while (rset.next()) {" line

the DB is returning values, see that println statement that gives us 103.

and even weirder, if I // comment out the executeUpdate line, it all completes normally

Upvotes: 3

Views: 13943

Answers (2)

karma_police
karma_police

Reputation: 352

Also for simplifying your code you can use try-with-resources statement that utilizes
java.lang.AutoCloseable interface, thus you can get rid of lines:

    rset.close();
    qry.close();

The whole block can look somehow like this:

        try (ResultSet rset = conn.createStatement().executeQuery("select id from CONSTITUENTS where manager = 'abc' limit 1")) {
            String insertSQL = "insert into PAYREQUESTS (constituent, inblock) values ('%d', 238)";
            while (rset.next()) {
                int id = rset.getInt("id");
                Savepoint savePoint = conn.setSavepoint("beforeInsert");
                try {
                    conn.createStatement().executeUpdate(String.format(insertSQL, id));
                    conn.commit();
                } catch (SQLException ex) {
                    conn.rollback(savePoint);
                    //log exception
                }
            }
        } catch (SQLException e) {
            //log exception
        }

Since your connection has auto-commit mode equals false maybe it make sense to possible harmful action.

Upvotes: 0

Gray
Gray

Reputation: 116908

Exception in thread "main" org.h2.jdbc.JdbcSQLException: The object is already closed [90007-196]

Your problem is that you are reusing the SQL Statement inside of your while loop. As soon as you call the qry.executeUpdate(...) method in the loop, the ResultSet rset associated with the previous statement is closed, hence the error. It is the while(rset.next()) statement that is called after the first executeUpdate(...) in the loop that fails.

If you use a new statement in the loop then it should work.

Statement qry = conn.createStatement();
String sql = "select id from CONSTITUENTS where manager = 'abc' limit 1";
ResultSet rset = qry.executeQuery(sql);
while (rset.next()) {
    int id = rset.getInt("id");
    System.out.println(id);
    // we can't reuse the same Statement here so we need to create a new one
    conn.createStatement().executeUpdate("insert into PAYREQUESTS ...");
}

You might consider keeping a collection of necessary updates and then issue the updates at the end of the loop.

and even weirder, if I // comment out the executeUpdate line, it all completes normally

Yep, that sounds right. Not weird at all. :-)

Upvotes: 6

Related Questions