How can I avoid ResultSet is closed exception in Java?

Question

As soon as my code gets to my while(rs.next()) loop it produces the ResultSet is closed exception. What causes this exception and how can I correct for it?

EDIT: I notice in my code that I am nesting while(rs.next()) loop with another (rs2.next()), both result sets coming from the same DB, is this an issue?

Answer 1

The exception states that your result is closed. You should examine your code and look for all location where you issue a ResultSet.close() call. Also look for Statement.close() and Connection.close(). For sure, one of them gets called before rs.next() is called.

Answer 2

You may have closed either the Connection or Statement that made the ResultSet, which would lead to the ResultSet being closed as well.

Answer 3

Also, you can only have one result set open from each statement. So if you are iterating through two result sets at the same time, make sure they are executed on different statements. Opening a second result set on one statement will implicitly close the first. http://java.sun.com/javase/6/docs/api/java/sql/Statement.html

Answer 4

Sounds like you executed another statement in the same connection before traversing the result set from the first statement. If you're nesting the processing of two result sets from the same database, you're doing something wrong. The combination of those sets should be done on the database side.

Answer 5

Proper jdbc call should look something like:

try { 
    try {

        Connection conn = DriverManager.getConnection(myUrl,"",""); 
        Statement stmt = conn.createStatement(); 
        ResultSet rs; 

        rs = stmt.executeQuery(myQuery); 
        while ( rs.next() ) { 
            // process results
        } 

    } catch (SqlException e) { 
        System.err.println("Got an exception! "); 
        System.err.println(e.getMessage()); 
    } finally {
        // you should release your resources here 
        rs.close();
        stmt.close();
        conn.close();
    }
} catch (SqlException e) {
    System.err.println("Got an exception! "); 
    System.err.println(e.getMessage()); 
}

you can close connection (or statement) only after you get result from result set. Safest way is to do it in finally block. However close() could also throe SqlException, hence the other try-catch block.

Answer 6

This could be caused by a number of reasons, including the driver you are using.

a) Some drivers do not allow nested statements. Depending if your driver supports JDBC 3.0 you should check the third parameter when creating the Statement object. For instance, I had the same problem whit the JayBird driver to Firebird, but the code worked fine with the postgres driver. Then I added the third parameter to the createStatement method call and set it to ResultSet.HOLD_CURSORS_OVER_COMMIT, and the code started working fine for Firebird too.

static void testNestedRS() throws SQLException {

 Connection con =null;
 try {
  // GET A CONNECTION
  con = ConexionDesdeArchivo.obtenerConexion("examen-dest");
  String sql1 = "select * from reportes_clasificacion";

  Statement st1 = con.createStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_READ_ONLY, 
    ResultSet.HOLD_CURSORS_OVER_COMMIT);
  ResultSet rs1 = null;

  try {
   // EXECUTE THE FIRST QRY
   rs1 = st1.executeQuery(sql1);

   while (rs1.next()) {
    // THIS LINE WILL BE PRINTED JUST ONCE ON
                                    // SOME DRIVERS UNLESS YOU CREATE THE STATEMENT 
    // WITH 3 PARAMETERS USING 
                                    // ResultSet.HOLD_CURSORS_OVER_COMMIT
    System.out.println("ST1 Row #: " + rs1.getRow());

    String sql2 = "select * from reportes";
    Statement st2 = con.createStatement(
      ResultSet.TYPE_SCROLL_INSENSITIVE,
      ResultSet.CONCUR_READ_ONLY);

    // EXECUTE THE SECOND QRY.  THIS CLOSES THE FIRST 
    // ResultSet ON SOME DRIVERS WITHOUT USING 
                                    // ResultSet.HOLD_CURSORS_OVER_COMMIT

    st2.executeQuery(sql2);

    st2.close();
   }
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   rs1.close();
   st1.close();
  }

 } catch (SQLException e) {

 } finally {
  con.close();

 }

}

b) There could be a bug in your code. Remember that you cannot reuse the Statement object, once you re-execute a query on the same statement object, all the opened resultsets associated with the statement are closed. Make sure you are not closing the statement.