SQLException: Operation not allowed after ResultSet closed

[H Bhoon]

Hi all,
Please help me with this problem. I use plain JDBC statements/resultsets to do queries on my db. I retrieve history records for a mobile device record like this:

for (int j = 0; j < mobileKeys.length; j++) {
	ResultSet history = dao.getHistory(mobileKeys[j]);
	historyMap.put(mobileKeys[j], history);
}

When I later get the history resultset from my historyMap and try to do a historyResultSet.next() I get a "java.sql.SQLException: Operation not allowed after ResultSet closed" exception. Why is this? According to my understanding I specifically create a new resultset object in my loop every time the loop executes precisely so that I DON'T get this error (thus that I don't re-use a resultset object).

Thanks all!

[David O'Meara]

a ResultSet is a Database resource, and you should never share or retain it like you have. With database reads you should open the connection, read the data, then return all of the resources (ie close them)
What is possibly happening is that the DB detects that the ResultSet is still open and overdue and cuts it ioff, or the Connection is closed, which causes the ResultSet to be closed. You can't and shouldn't keep them open for extended periods.

[Max Maxx]

For JDBC datasource that was set in websphere 6.1 server i solved similar issue from admin console in the following way:

Resources -> JDBC Provider -> select your Provider -> Data Sources -> Custom Properties.
Finally set the key "resultSetHoldability" to the value "1". The possible values are: 1 (HOLD_CURSORS_OVER_COMMIT), 2 (CLOSE_CURSORS_AT_COMMIT).

[H Bhoon]

Hey guys,
Thanks for your replies! I finally used a work around (following from the suggestions of David O'Meara). I created a POJO (HistoryContainer) to hold all data returned from the resultset. After I get my resultset back from running the query, I copy all the data I need from the resultset into my POJO and then I continue working with my POJO instead of with the resultset. This POJO contains an ArrayList to cater for multiple records in my resultset.

for (int j = 0; j < mobileKeys.length; j++) {
	historyResultSet = dao.getHistory(mobileKeys[j]);
	historyMap.put(mobileKeys[j], new HistoryContainer(historyResultSet));
}

Thanks all for replying.

[David O'Meara]

That's more like it