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

Why don't you use two sessions then?

> Our first problem was that ResultSet are created with the default
> ResultSetHoldability=CLOSE_CURSORS_AT_COMMIT
> 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).

Should throw exception before, maybe there is some Hibernate caching
there...

> The only way to change the ResultSetHoldability when using
> hibernate seems to be to open the connection with a parameter
> like:
>
> ...
> properties.put("defaultResultSetHoldable", "");
>
> DriverManagerDataSource out = new DriverManagerDataSource();
> out.setJdbcUrl( jdbcUrl );
> out.setProperties( driverProps );
> Connection con = out.getConnection();

This parameter is for the applications that need this option by deafult
and cannot be changed to use appropriate JDBC API call.

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

That's what the parameter is about - make all prepared statements
scrollable by default.

Those applications that are "clever" enough would use
Connection.prepareStatement(String, int, int, int) call and those values
will override the defaults.

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

Use two sessions in Hibernate - it is also more memory efficient.
Firebird does not support the scrollable result sets, so we have to
fetch complete result set into memory first, in order provide the
holdability feature.

The only issue that might be with using two sessions, is that you might
require check your code if you use session-per-thread scenario. In this
case you have to open second session directly via Hibernate API and
ensure that you close the session afterwards.

Roman