Understanding Cursor Types

Operations in a relational database act on a complete set of rows. The set of rows returned by a SELECT statement consists of all the rows that satisfy the conditions in the WHERE clause of the statement. This complete set of rows returned by the statement is known as the result set. Applications cannot always work effectively with the entire result set as a unit. These applications need a mechanism to work with one row or a small block of rows at a time. Cursors are an extension to result sets that provide that mechanism.

Cursors extend result set processing by doing the following:

• Allowing positioning at specific rows of the result set.

• Retrieving one row or block of rows from the current position in the result set.

• Supporting data modifications to the row at the current position in the result set.

• Supporting different levels of visibility to changes made by other users to the database data that is presented in the result set.

The JDBC specification provides support for forward-only and scrollable cursors that are sensitive or insensitive to changes made by other jobs, and can be read-only or updatable. This functionality is provided by the Microsoft SQL Server JDBC Driver SQLServerResultSet class.

The JDBC driver supports the following cursor types:

Cursor Positioning

The TYPE_FORWARD_ONLY, TYPE_SS_DIRECT_FORWARD_ONLY, and TYPE_SS_SERVER_CURSOR_FORWARD_ONLY cursors support only the next positioning method.

The TYPE_SS_SCROLL_DYNAMIC cursor does not support the absolute and getRow methods. The absolute method can be approximated by a combination of calls to the first and relative methods for dynamic cursors.

The getRow method is supported by TYPE_FORWARD_ONLY, TYPE_SS_DIRECT_FORWARD_ONLY, TYPE_SS_SERVER_CURSOR_FORWARD_ONLY, TYPE_SS_SCROLL_KEYSET, and TYPE_SS_SCROLL_STATIC cursors only. The getRow method with all forward-only cursor types returns the number of rows read so far through the cursor.

Only the TYPE_SS_SCROLL_KEYSET and the equivalent TYPE_SCROLL_SENSITIVE cursors expose deleted rows. If the cursor is positioned on a deleted row, column values are unavailable, and the rowDeleted method returns "true". Calls to get<Type> methods throw an exception with the message, "Cannot get value from a deleted row". Deleted rows cannot be updated. If you try to call an update<Type> method on a deleted row, an exception is thrown with the message, "A deleted row cannot be updated". The TYPE_SS_SCROLL_DYNAMIC cursor has the same behavior until the cursor is moved out of the current fetch buffer.

Forward and dynamic cursors expose deleted rows in a similar way, but only while the cursors remain accessible in the fetch buffer. For forward cursors, this is fairly straightforward. For dynamic cursors it more complex when the fetch size is greater than 1. An application can move the cursor forward and backward within the window that is defined by the fetch buffer, but the deleted row will disappear when the original fetch buffer in which it was updated is left. If an application does not want to see transient deleted rows by using dynamic cursors, a fetch relative (0) should be used.

If the key values of a TYPE_SS_SCROLL_KEYSET or TYPE_SCROLL_SENSITIVE cursor row are updated with the cursor, the row retains its original position in the result set, regardless of whether the updated row meets the cursor’s selection criteria. If the row was updated outside the cursor, a deleted row will appear at the row’s original position, but the row will appear in the cursor only if another row with the new key values was present in the cursor, but has since been deleted.

For dynamic cursors, updated rows will retain their position within the fetch buffer until the window that is defined by the fetch buffer is left. Updated rows might subsequently reappear at different positions within the result set, or might disappear completely. Applications that have to avoid transient inconsistencies in the result set should use a fetch size of 1 (the default is 8 rows with CONCUR_SS_SCROLL_LOCKS concurrency and 128 rows with other concurrencies).

Cursor Conversion

SQL Server can sometimes choose to implement a cursor type other than the one requested, which is referred to as an implicit cursor conversion (or cursor degradation). For more information about implicit cursor conversion, see the "Using Implicit Cursor Conversions" topic in SQL Server Books Online.

With SQL Server 2000, when you update the data through the ResultSet.TYPE_SCROLL_SENSITIVE and ResultSet.CONCUR_UPDATABLE result set, an exception is thrown with a message "The cursor is READ ONLY". This exception occurs because the SQL Server 2000 has done an implicit cursor conversion for that result set and did not return the updatable cursor that has been requested.

To work around this problem, you can do one of the following two solutions:

• Ensure that the underlying table has a primary key

• Use SQLServerResultSet.TYPE_SS_SCROLL_DYNAMIC instead of ResultSet.TYPE_SCROLL_SENSITIVE while creating a statement.

Cursor Updating

In-place updates are supported for cursors where the cursor type and concurrency support updates. If the cursor is not positioned on an updatable row in the result set (no get<Type> method call succeeded), a call to an update<Type> method will throw an exception with the message, "The result set has no current row." The JDBC specification states that an exception arises when an update method is called for a column of a cursor that is CONCUR_READ_ONLY. In situations where the row is not updatable, such as because of an optimistic concurrency conflict such as a competing update or deletion, the exception might not arise until insertRow, updateRow, or deleteRow is called.

After a call to update<Type>, the affected column cannot be accessed by get<Type> until updateRow or cancelRowUpdates has been called. This avoids problems where a column is updated by using a different type from the type returned by the server, and subsequent getter calls could invoke client side type conversions that give inaccurate results. Calls to get<Type> will throw an exception with the message, "Updated columns cannot be accessed until updateRow() or cancelRowUpdates() has been called."

After moveToInsertRow has been called, an exception will be thrown if any method other than get<Type>, update<Type>, insertRow, and cursor positioning methods (including moveToCurrentRow) are called on the result set. The moveToInsertRow method effectively places the result set into insert mode, and cursor positioning methods terminate insert mode. Relative cursor positioning calls move the cursor relative to the position it was at before moveToInsertRow was called. After cursor positioning calls, the eventual destination cursor position becomes the new cursor position.

If the cursor positioning call made while in insert mode does not succeed, the cursor position after the failed call is the original cursor position before moveToInsetRow was called. If insertRow fails, the cursor remains on the insert row and the cursor remains in insert mode.

Columns in the insert row are initially in an uninitialized state. Calls to the update<Type> method set the column state to initialized. A call to the get<Type> method for an uninitialized column throws an exception. A call to the insertRow method returns all the columns in the insert row to an uninitialized state.

If any columns are uninitialized when the insertRow method is called, the default value for the column is inserted. If there is no default value but the column is nullable, then NULL is inserted. If there is no default value and the column is not nullable, the server will return an error and an exception will be thrown.

See Also

Other Resources

Managing Result Sets with the JDBC Driver