wood burning stoves 2.0*
The moose likes JDBC and the fly likes SQLException, ResultSet closed but I can figure out why Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQLException, ResultSet closed but I can figure out why" Watch "SQLException, ResultSet closed but I can figure out why" New topic
Author

SQLException, ResultSet closed but I can figure out why

Marc Cracco
Ranch Hand

Joined: Mar 09, 2010
Posts: 79
    
    1
I'm having a problem with java.sql.ResultSet, I have a java.sql.PreparedStatement on which I run executeQuery() to return a ResultSet yet when I try to get the results from the query I'm getting an Exception thrown:

Exception: java.sql.SQLException Message: Operation not allowed after ResultSet closed.

From searching online it looks like a ResultSet can end up being closed for a few reason:

1) The PreparedStatement object that generated it is closed.
2) The PreparedStatement object that generated it is re-executed.
3) The PreparedStatement object that generated it is used to retrieve the next result from a sequence of multiple results.
4) Closing the Connection which was used to generate the PreparedStatement.

I checked my code and do none of those things. Below is a snippet of the code that causes the problem:



The Exception is thrown when trying to execute: while (partyIds.next()) {

Like I stated I never close the connection or statement and as you can see I don't reuse the statement prior to trying to view my result.

Thanks for he help...

Marc

Disclaimer: I did post this on StackOverflow but got no answers. Hoping to get more help here.

EDIT: changed code to full code.

SCJP 6 [ My stuff ]
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Your code does not look suspicious. Which database are you using? Are you sure there aren't other JDBC operations you forgot to show, which might be in some other method, even if they don't operate on the statement and resultset you use in your method? Is autocommit set on or off?

It might be worth posting complete code, with all of the methods that are called. See also PostRealCode.
Patryk Sosinski
Greenhorn

Joined: Sep 10, 2011
Posts: 18
And show the code of session.connection() method.
Marc Cracco
Ranch Hand

Joined: Mar 09, 2010
Posts: 79
    
    1
Patryk Sosinski wrote:And show the code of session.connection() method.





Connection is a java.sql.Connection
Marc Cracco
Ranch Hand

Joined: Mar 09, 2010
Posts: 79
    
    1
Martin Vajsar wrote:Which database are you using?


mySQL 5.5 with mysql-connector-java-5.1.13

Martin Vajsar wrote:t might be worth posting complete code, with all of the methods that are called.


I've update to include the whole code.
Patryk Sosinski
Greenhorn

Joined: Sep 10, 2011
Posts: 18
Put this code in line 58 (or use any other debugger to check this). Is it true or false?
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

Marc Cracco wrote:The Exception is thrown when trying to execute: while (partyIds.next()) {

So I found that line and then went to look at the loop it controlled, to see where you were closing the statement or the connection or something. But you didn't post that loop, although it's the most likely place for the error to occur.

Like I stated I never close the connection or statement and as you can see I don't reuse the statement prior to trying to view my result.

No, actually I can't see that because you didn't post the whole contents of the loop.
EDIT: changed code to full code.

Doesn't look like that to me.
Marc Cracco
Ranch Hand

Joined: Mar 09, 2010
Posts: 79
    
    1
Paul Clapham wrote:
Marc Cracco wrote:The Exception is thrown when trying to execute: while (partyIds.next()) {

So I found that line and then went to look at the loop it controlled, to see where you were closing the statement or the connection or something. But you didn't post that loop, although it's the most likely place for the error to occur.

Like I stated I never close the connection or statement and as you can see I don't reuse the statement prior to trying to view my result.

No, actually I can't see that because you didn't post the whole contents of the loop.
EDIT: changed code to full code.

Doesn't look like that to me.


It breaks on the first entry into the loop hence why I didn't post further into it. Here is the loop inner code:

picks up from line 59 of original code post:

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

I can see you commit in the loop. Resultset gets closed upon commit unless it is open in holdability mode (I don't know exactly where or how this mode is specified - it will be either on the connection, the statement or the resultset - mainly because I never commit with resultset open so I don't need to bother about it). Though this is not consistent with other information you provided (namely that the error occurs on the first pass), I strongly suggest to check this out. Eg. modify the code to read the records into a list and do the processing after all the records are read and resultset closed.

Edit: one more thought: would not it be possible to do all this processing in a single update statement? Don't know how complicated updates can MySQL handle, I've significant experience with Oracle only, but at the first blush this seems to be doable in Oracle.
Marc Cracco
Ranch Hand

Joined: Mar 09, 2010
Posts: 79
    
    1
Martin Vajsar wrote:I can see you commit in the loop. Resultset gets closed upon commit unless it is open in holdability mode (I don't know exactly where or how this mode is specified - it will be either on the connection, the statement or the resultset - mainly because I never commit with resultset open so I don't need to bother about it). Though this is not consistent with other information you provided (namely that the error occurs on the first pass), I strongly suggest to check this out. Eg. modify the code to read the records into a list and do the processing after all the records are read and resultset closed.

Edit: one more thought: would not it be possible to do all this processing in a single update statement? Don't know how complicated updates can MySQL handle, I've significant experience with Oracle only, but at the first blush this seems to be doable in Oracle.


As you stated though the biggest issue I see with all this is that it's close before it even enters the loop. I'll play with it some more and see if I get anywhere but I haven't so far.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Marc Cracco wrote:As you stated though the biggest issue I see with all this is that it's close before it even enters the loop. I'll play with it some more and see if I get anywhere but I haven't so far.

Then your only possibility is to simplify things down till they start to work and then put the removed parts back bit by bit, till the culprit is found. Eg. remove everything except creating the prepared statement, opening a resultset and looping through it. If even this does not work, try creating a small test project outside your main project, possibly with pure JDBC - without using Hibernate at all. (This might be a prejudice on my part, as I don't use any persistence framework, but I would not be surprised if combining persistence frameworks with pure JDBC had unusual consequences in a few rare occasions.)

My personal experience is that "unexplainable bugs" are usually a combination of two (or even more) different bugs that combine to produce a weird behavior.

I'd still suggest to re-check that the loop is never entered (printing messages to log maybe?). One can develop severe "local blindness" while hunting such pesky besetting bug for several hours - or even days. I certainly do.
Marc Cracco
Ranch Hand

Joined: Mar 09, 2010
Posts: 79
    
    1
Martin Vajsar wrote:I'd still suggest to re-check that the loop is never entered (printing messages to log maybe?). One can develop severe "local blindness" while hunting such pesky besetting bug for several hours - or even days. I certainly do.


Verified, it break on loop entry.

Going to work a a simplified version and see what I get.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQLException, ResultSet closed but I can figure out why
 
Similar Threads
Question on closing ResultSets
performance issue in iterating through large list of result set
ORA-01000 maximum open cursors exceeded
Could not locate DB driver
Resultset processes 1 record