Holdable Cursors

Note:

Non-holdable cursors are only available in Java^(TM) 2 Platform, Standard Edition, v 1.4 (J2SE) environments.

The holdable cursor feature permits an application to keep cursors open after implicit or explicit commits. By default, the cursors are held open after a commit. Starting with Java^(TM) 2 Platform, Standard Edition, v 1.4 (J2SE), cursors can be created with close when a commit occurs option. Such cursors will be automatically closed when a commit happens. Cursors are automatically closed when a transaction aborts, whether or not they have been specified to be held open.

Note:

Holdable cursors do not work with XA transactions, in Derby Version 10, therefore cursors should be opened with holdability false when working with XA transactions.

To specify whether a cursor 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

  Cursors are closed when an implicit or explicit commit is performed.

• HOLD_CURSORS_OVER_COMMIT

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

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

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

• Open ResultSets remain open. The cursor is positioned before the next logical row of the result set.
• When the session is terminated, the ResultSet is closed and destroyed.
• All locks are released, except locks protecting the current cursor position of open cursors specified to stay open after commits.
• Immediately following a commit, the only valid operations that can be performed on the ResultSet are:
  • positioning the ResultSet to the next valid row in the result with ResultSet.next().
  • closing the ResultSet with ResultSet.close().

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

• All open ResultSets are closed.
• All locks acquired during the unit of work are released.

When autocommit is on, positioned updates and deletes are not supported for ResultSet objects that hold cursors open. If you attempt an update or delete, an exception is thrown.

The following example uses Connection.createStatement to return a ResultSet that will close after a commit is performed:

Connection conn = ds.getConnection(user, passwd);
Statement stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                  ResultSet.CONCUR_READ_ONLY,
                  ResultSet.CLOSE_CURSORS_AT_COMMIT);