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

User · Answer

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

User · Answer

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

User · Answer

Proper jdbc call should look something like   try        Connection conn      Statement stmt      ResultSet rs        try           conn   DriverManager getConnection myUrl                  stmt   conn createStatement             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         if  rs    null                 rs close                       if  stmt    null                stmt close                       if  conn    null                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

User · Answer

I got same error everything was correct only i was using same statement interface object to execute and update the database  After separating i e  using different objects of statement interface for updating and executing query i resolved this error  i e  do get rid from this do not use same statement object for both updating and executing the query

User · Answer

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

User · Answer

Check whether you have declared the method where this code is executing as static  If it is static there may be some other thread resetting the ResultSet

User · Answer

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 with 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

User · Answer

A ResultSetClosedException could be thrown for two reasons   1   You have opened another connection to the database without closing all other connections   2   Your ResultSet may be returning no values  So when you try to access data from the ResultSet java will throw a ResultSetClosedException

User · Answer

make sure you have closed all your statments and resultsets before running rs next  Finaly guarantees this  public boolean flowExists  Integer idStatusPrevious  Integer idStatus  Connection connection         LogUtil logRequestMethod         PreparedStatement ps   null      ResultSet rs   null      try           ps   connection prepareStatement  Constants SCRIPT SELECT FIND FLOW STATUS BY STATUS            ps setInt  1  idStatusPrevious            ps setInt  2  idStatus             rs   ps executeQuery             Long count   0L           if   rs    null                 while   rs next                       count   rs getLong  1                    break                                   LogUtil logSuccessMethod             return count  gt  0L        catch   Exception e             String errorMsg   String              format  Constants ERROR FINALIZED METHOD    e getMessage      null   e getMessage                     LogUtil logError  errorMsg  e             throw new FatalException  errorMsg          finally           rs close            ps close

User · Answer

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

[java] How can I avoid ResultSet is closed exception in Java?

Examples related to java

Examples related to sql

Examples related to exception