Home > Persistence > The DB2 “result set is closed” error

The DB2 “result set is closed” error

Your (IBM) DB2 JDBC driver might throw the following error in some situations:

Caused by: com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][4.12.55] 
Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null

If you look around the internets, you’ll find pages like this, which state things like this:

The IBM Data Server Driver for JDBC and SQLJ automatically closes the cursor 
when all rows have been retrieved from a ResultSet. When ResultSet.next is 
executed after the cursor is closed the SQLException is thrown.

It might even suggest an eccentric solution, like:

Alternatively, you can may enable the allowNextOnExhaustedResultSet 
datasource property. When the allowNextOnExhaustedResultSet property is set 
to DB2BaseDataSource.YES (1), and a forward-only cursor is positioned after 
the last row of a result set, a call to ResultSet.next returns false, instead 
of throwing an SQLException.

Don’t listen. Okay, maybe, in your case, the above applies. In that case, congratulations on fixing your bug and please get the heck out of here. If you’re still stumped, listen up to my extrapolations:

You’re running a query somewhere, and you need to put a transaction around it. That’s what this unreadable, confusing error means.

Let’s start with cursors: cursors are also subject to transaction boundaries. In fact, the way I see it, cursors were made for programmatic SQL languages, such as Transact-SQL or PL/SQL — and, okay, for SQL. More info on wiki.

But cursors are, in essence, database-level constructs that are exposed to users so that they can take advantage of them. What I mean to say by that is that cursors are sometimes used when you don’t expect them to be — like in a normal query.

And cursors need transactions! Because it’s a non-atomic set of operations on a set of results — but we’re expecting it to act atomically: get records from database. Bam.

So go find the query that’s causing the error, and put a transaction around it. It’ll help — or at least, it helped me.

Advertisements
Categories: Persistence
  1. Sachin
    9 January 2013 at 18:58

    Hi

    Thanks for the post. My code started working once I put the allowNextOnExhaustedResultSet = 1. But i want to try out your approach – what did you mean “put a transaction around the cursor”? Did you mean something that should be done in the stored procedure or a java side transaction? Do you have any example of how you put the transaction?

    Thanks
    Sachin

    • mriet
      15 January 2013 at 10:44

      The “cursor” is basically the query — well, not quite, but for the purposes of this discussion it is. Regardless of whether you’re invoking it via a procedure or java code, you need to put a transaction around the query. I can’t really help you with how to put a transaction around the query, you’ll have to figure that out yourself!

      • Sachin
        16 January 2013 at 16:08

        Hi

        Well I had tried various combinations of transactions around my cursor in the proc but didnt work – thats why i asked for an example! :)
        I finally didnt use the custom property either. Instead, my final solution was to use a mock resultset object (using java Proxy object) in which I used a custom next() method. This custom next() wd normally call the actual resultset’s next method unless the current row is already past the last row in the resultset – in this case, my next() simply returns a false.

        Regards
        Sachin M.

  2. somebodyWhoHelps
    29 January 2013 at 18:13

    When using your connection first time, your transaction starts. But to keep your transaction up you need to call
    connection.setAutoCommit(false);
    otherwise the transaction is commited after your first statement. But remember, do not forget to call connection.commit() when your work is done.

  3. Valdarez
    26 May 2014 at 22:23

    You’re the man. Such a confusing error, but pops up with working with jboss/arquillian/spring. Thanks for the information. So simple, yet so frustratingly hard.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: