Subject Re: [Firebird-Java] Re: Strange error message: "Cursor"?!?
Author Roman Rokytskyy
> 1. Open a JDBC connection to Firebird using JayBird
> 1.5.5JDK_1.4 as driver (have not tried with 2.0 yet);
> 2. Set autoCommit to false;
> 3. Execute a query wich returns a ResultSet with some rows (say 5 rows);
> 4. Start iterating over the ResultSet using;
> 5. On each iteration, execute an update using the same connection (say
> an insert on some arbitrary table) and commit the changes
> (Connection.commit());

You are just [not] lucky that it did not happen sooner. JayBird fetches
couple of records during, and then iterates through them in
memory (see Statement.setFetchSize and ResultSet.setFetchSize methods). But
right on the next attempt to get data from it will complain about closed

> 6. Just before the last iteration, the call to will
> generate an exception with the stack trace I reported before (see below).

> Is there anything wrong with these steps?


> Is it forbidden to call commit() while traversing a ResultSet?

In general - no. However this "no" depends on the value of
ResultSet.getHoldability(). If it is equal to
ResultSet.HOLD_CURSORS_OVER_COMMIT, you can call Connection.commit() while
iterating the result set. By default all result sets are constructed
non-holdable, so it is an error to call commit while iterating the "default"
result set.

That was JDBC 3.0 specification.

Now JayBird part. JayBird 1.5.x implements JDBC 2.0 specification (though on
interface level compatible with JDBC 3.0). The result set holdability was
first introduced in JDBC 3.0 and is not supported in JayBird 1.5.5. And
Firebird internally closes cursors during commit. All types of cursors.

JayBird 2.0 supports the JDBC 3.0 specification and, in particular, supports
holdable result sets. However, since Firebird does not support them, it must
cache complete result set on the client. But then you can call commit while
traversing the result set.

In order to solve your problem, you either:

- stop committing the same transaction from the loop, use another connection
for updates;
- switch to JayBird 2.0.0 and create statements that produce holdable result