SQLException not thrown after closing ResultSet

[SilverStray]

Hi,

I'm just curious, and I am hoping to find an explanation. Why is it that if I execute a query, and when I close the Resultset inside catch statement, it does not throw the SQLException to the calling method?

My code is as follows:

ResultSet rs = null;
try {
rs = ps.executeQuery();

while (rs.next()) {
count = rs.getInt("CNT");
}
}
catch (SQLException e) {
//rs.close();
ps.close();
conn.close();
throw e;
}

If I uncomment the rs.close(), the SQLException is not passed to the calling method, but if I remove it, everything is ok. Will I not encounter a ResultSet Exception for unclosed Resultsets in the future?

 

[sedj]

If you do not close a ResultSet, then no, an exception is not thrown - but you do leave an open cursor on the database - which is very dangerous.

Some connection pools will close the result set and statement objects for you if you forget, but if you really should get in the habit of closing it yourself.

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[timw]

The API Javadoc for Statement interface states that any current ResultSet on an implementing objects of Statement (such as PreparedStatement) is closed when the Statement is closed. So your ps.close will close the ResultSet anyway.

I tend to put my tidy-up code in a finally clause when doing JDBC.

PreparedStatement ps = ...
ResultSet rs = null;
try {
  ... fill-in ps parameters ...
  rs = ps.executeQuery();
  ... do some processing on rs ...
} catch (SQLException ex){
  ... handle the error ...
} finally {
  if ( rs != null ){
    try { rs.close() } 
    catch (SQLException ex){
      //Well I tried
    }
  }
  if ( ps != null ){
    try { ps.close() }
    catch (SQLException ex){
      //Well I tried
    }
  }
}

I don't allow a failure on a close() method to halt the tidy-up, ensuring that each resource is given a chance to close.

That way, no matter what happens, your ResultSet and PreparedStatement will always get closed. It depends on how you manage your Connection as to whether this goes in the finally here too.

Tim

 

[sedj]

Wait !!!

This is not always the case. The API docs may well state that cursors (ResultSets) are closed when the statement is closed, but this is not always true, and you should certainly not depend on it.

JDBC objects are implemented by db vendors, and they may not always conform.

I know for a fact that when using Oracle's 8 & 9i drivers, closing a Statement will NOT close an open cursor.

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[timw]

I know, sedj .. that's why I always close both. It's a good point to keep in mind though. (Just thought I'd mention it to get you going )

Tim

 

[SilverStray]

Heya, thanks for your inputs. I do recognize the danger of leaving unclosed connections, which is my worry with what I did in my code. But is there something wrong with the way I throw the SQLException. I also put 'throws' in the method declaration. But if i uncomment the rs.close(), the calling method does not receive the SQLException that is supposed to be thrown, even if I put the clean-up codes inside 'finally' and removing them in the 'catch' portion.

 

[timw]

Like I demonstrated in my example code, you don't want the SQLException thrown by the rs.close() to cause an exit from the method. You want to stay in there until all resources are released. That's why I've wrapped my rs.close() and ps.close() in their own try/catches which do nothing.

If you want any problem in the jdbc body code to be propagated out of the method you could just lose the top-most catch (SQLException ex) bit. The resources will still be cleared down since they are in the finally clause.

PreparedStatement ps = ...
ResultSet rs = null;
try {
  ... fill-in ps parameters ...
  rs = ps.executeQuery();
  ... do some processing on rs ...
  //Any SQLException thrown in here will propagate out
  //of the method (which must declare it to be thrown)
  //, but the finally will still clear down my resources.
} finally {
  if ( rs != null ){
    try { rs.close() } 
    catch (SQLException ex){
      //Close failed. Maybe log the fact
    }
  }
  if ( ps != null ){
    try { ps.close() }
    catch (SQLException ex){
      //Close failed. Maybe log the fact
    }
  }
}

Tim