I haven't used updateable result sets, but here's a
HOLD_CURSORS_OVER_COMMIT example from DB2:

http://www.db2mag.com/story/showArticle.jhtml?articleID=23902546#list6
"Therefore, you can perform a group of update operations against a set
of records represented in the ResultSet and commit the changes to the
DB2 UDB server. This would free up DB2 server locks on the records,
provide other applications with visibility to the changed data, and
allow your application to continue with the processing of the
ResultSet."

So for DB2, the changes are available as soon as the transaction is
commited (either through auto-commit or explicit commit), not when the
result set is closed.

IMNSHO, both forward-only and scrollable result changes should be
visible the moment it is committed, regardless of how it is committed.

-----Original Message-----
From: Firebird-Java@yahoogroups.com
[mailto:Firebird-Java@yahoogroups.com] On Behalf Of Roman Rokytskyy
Sent: Wednesday, May 11, 2005 6:39 PM
To: Firebird-Java@yahoogroups.com
Subject: [Firebird-Java] Result set holdability

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




Yahoo! Groups Links