Understanding cursor types

Download JDBC driver

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 can't always work effectively with the entire result set as a unit. These applications need a way 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 items:

• 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 to the database data by other users 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 JDBC Driver for SQL ServerSQLServerResultSet class.

Remarks

The JDBC driver supports the following result set and cursor types along with the specified behavior options.

The application has to make a single (forward) pass through the result set. This pass is the default behavior and behaves the same as a TYPE_SS_DIRECT_FORWARD_ONLY cursor. The driver reads the entire result set from the server into a memory during the statement execution time.

The application has to make a single (forward) pass through the result set. It behaves the same as a TYPE_SS_DIRECT_FORWARD_ONLY cursor. The driver reads rows from the server as the application requests them and minimizes client-side memory usage.

The application has to make a single (forward) pass through the result set by using a server cursor. It behaves the same as a TYPE_SS_SERVER_CURSOR_FORWARD_ONLY cursor.

Rows are retrieved from the server in blocks that are specified by the fetch size.

The application has to make a single (forward) pass through the result set to update one or more rows.

Rows are retrieved from the server in blocks that are specified by the fetch size.

By default, the fetch size is fixed when the application calls the setFetchSize method of the SQLServerResultSet object.

The application requires a database snapshot. The result set isn't updatable. Only CONCUR_READ_ONLY is supported. All other concurrency types will cause an exception when used with this cursor type.

Rows are retrieved from the server in blocks that are specified by the fetch size.

The application has to see changed data for existing rows only.

Rows are retrieved from the server in blocks that are specified by the fetch size.

The application may change data in the existing rows by using the ResultSet object. The application must also be able to see the changes to rows made by others from outside the ResultSet object.

Rows are retrieved from the server in blocks that are specified by the fetch size.

Integer value = 2003. Provides a read-only client-side cursor that is fully buffered. No server cursor is created.

Only CONCUR_READ_ONLY concurrency type is supported. All other concurrency types cause an exception when used with this cursor type.

Integer value = 2004. Fast and accesses all data using a server cursor. It's updatable when used with CONCUR_UPDATABLE concurrency type.

Rows are retrieved from the server in blocks that are specified by the fetch size.

To get adaptive buffering for this case, the application has to explicitly call the setResponseBuffering method of the SQLServerStatement object by providing a String value "adaptive". For an example code, see Updating large data sample.

Integer value = 1004. Application requires a database snapshot. This option is the SQL Server-specific synonym for the JDBC TYPE_SCROLL_INSENSITIVE and has the same concurrency setting behavior.

Rows are retrieved from the server in blocks that are specified by the fetch size.

Integer value = 1005. Application has to see changed data for existing rows only. This option is the SQL Server-specific synonym for the JDBC TYPE_SCROLL_SENSITIVE and has the same concurrency setting behavior.

Rows are retrieved from the server in blocks that are specified by the fetch size.

Integer value = 1005. Application has to change data or see changed data for existing rows. This option is the SQL Server-specific synonym for the JDBC TYPE_SCROLL_SENSITIVE and has the same concurrency setting behavior.

Rows are retrieved from the server in blocks that are specified by the fetch size.

Integer value = 1006. Application must see changed data for existing rows, and see inserted and deleted rows during the lifetime of the cursor.

Rows are retrieved from the server in blocks that are specified by the fetch size.

Integer value = 1006. The application may change data for existing rows, or insert or delete rows by using the ResultSet object. The application must also be able to see changes, inserts, and deletes made by others from outside the ResultSet object.

Rows are retrieved from the server in blocks that are specified by the fetch size.

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 doesn't 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 behavior 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 doesn't 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 keeps 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 keep their position within the fetch buffer until the window that is defined by the fetch buffer is left. Updated rows might later 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).

With SQL Server 2000 (8.x), 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 (8.x) has done an implicit cursor conversion for that result set and didn't return the updatable cursor that has been requested.

To work around this problem, you can do one of the following 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 isn't 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 behavior 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 doesn't 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's no default value but the column is nullable, then NULL is inserted. If there's no default value and the column is not nullable, the server will return an error and an exception will be thrown.

See also

Managing result sets with the JDBC driver