Holdable result sets

The holdable result set feature permits an application to
keep result sets open after implicit or explicit commits. By default,
the cursor controlled by the result set is held open after a commit.

Note: Derby also
supports non-holdable result sets.

When you create a statement, you can specify that the result set
will be automatically closed when a commit occurs.
Result sets are automatically
closed when a transaction aborts, whether or not they have been specified
to be held open.

To specify whether a result set should be held open after a commit
takes place, supply one of the following ResultSet parameters to the Connection method createStatement, prepareStatement, or prepareCall:

CLOSE_CURSORS_AT_COMMIT

Result sets are closed when an
implicit or explicit commit is performed.

HOLD_CURSORS_OVER_COMMIT

Result sets are held open when
a commit is performed, implicitly or explicitly. This is the default behavior.

The method Statement.getResultSetHoldability() indicates
whether a result set generated by the Statement object stays open or closes, upon
commit. See the Java DB Reference Manual for
more information.

When an implicit or explicit commit occurs, result sets
that hold cursors open behave as follows:

Open result sets remain open. Non-scrollable result sets becomes
positioned before the next logical row of the result set. Scrollable
insensitive result sets keep their current position.

When the session is terminated, the result set is closed and destroyed.

All locks are released, including locks protecting the current
cursor position.

For non-scrollable result sets, immediately following a commit, the
only valid operations that can be performed on the ResultSet
object are:

positioning the result set to the next row with ResultSet.next().

closing the result set with ResultSet.close().

When a rollback or rollback to savepoint occurs, either explicitly or implicitly, the following
behavior applies:

All open result sets are closed.

All locks acquired during the unit of work are released.

Note: Holdable result sets do not work with XA
transactions in Derby. When working
with XA transactions, the result set should be opened with holdability
ResultSet.CLOSE_CURSORS_AT_COMMIT.