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][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.