Subject Problem with Scrollable Resultset and ResultSetHoldability
Author barthel.steckemetz
Problem with Scrollable Resultset and ResultSetHoldability

We have a quite common scenario:
We like to loop over a large resultset using cursors, modify data
and commit them in chunks, i.e. perform a commit of the modified
data after each 1000 result.
In addition we are using hibernate.

Our first problem was that ResultSet are created with the default
So the cursor would be closed after the first commit.
(It seemed to last some more commits and only crashed
with an exception at the end).

The only way to change the ResultSetHoldability when using
hibernate seems to be to open the connection with a parameter

properties.put("defaultResultSetHoldable", "");

DriverManagerDataSource out = new DriverManagerDataSource();
out.setJdbcUrl( jdbcUrl );
out.setProperties( driverProps );
Connection con = out.getConnection();

The ScrollableResultSets now worked fine.
However we got a lot of warnings like:

Holdable result set must be scrollable.

This warning is created in org.firebirdsql.jdbc.AbstractConnection
public synchronized Statement createStatement(int resultSetType,
int resultSetConcurrency, int resultSetHoldability) throws SQLException

if (resultSetHoldability == FirebirdResultSet.HOLD_CURSORS_OVER_COMMIT &&
resultSetType == ResultSet.TYPE_FORWARD_ONLY) {

addWarning(new FBSQLWarning("Holdable result set must be scrollable."));
resultSetType = ResultSet.TYPE_SCROLL_INSENSITIVE;

Since resultSetHoldability == FirebirdResultSet.HOLD_CURSORS_OVER_COMMIT &&
resultSetType == ResultSet.TYPE_FORWARD_ONLY is now always true
we ended with all PreparedStatements being scrollable !
This is of course not wanted.

At the moment we have no idea to get our above described scenario to work.
We hope the somebody else may have solved this problem.

Thanks in advance.