Hi,

I need help from you with ResultSet.HOLD_CURSORS_OVER_COMMIT implementation.
I am thinking about the way to implement it in driver, though I have no
experience with it and the real-world usage patterns. Can those that use
this feature with other databases describe the way it is used there?

So far I see advantage only in auto-commit mode with updatable result sets.
Application can call ResultSet.updateRow() method without closing the result
set being traversed. Something like:

ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
rs.updateXXX(...); // modify value
rs.updateRow();
}
rs.close();

to make changes available for other read-committed transactions. However, I
am not sure whether this is correct approach from the JDBC point of view. I
would like to understand what should happen with the transactions in each
line. Is old transaction committed before the rs.updateRow() is called or is
it committed only after result set is closed? JDBC specification does not
give an answer on this question... Ideas?

If result set is forward only, it is available only until COMMIT is called,
which closes all open cursors on the server. So, calling rs.next() would
fail already on the second run if we say that rs.updateRow() commits is
performed in separate transaction. However, if we say that auto-commit
transaction is committed only after result set being closed (that's what
specification says for auto-commit mode), then we're fine - cursor remains
open till end of the transaction. The only "disadvantage" is that all
updates will become visible only after the rs.close() call. What
interpretation is used in other databases?

If result set is scrollable, it is completely cached on the client anyway so
keeping it open after commit is an easy task. Still the question of
transaction management remains, but only to answer whether the
rs.updateRow() makes changes committed on the server or not in auto-commit
case.

Any input is greatly appreciated.

Thanks!
Roman