Use of JDBC cursors----ResultSet objects

Understanding cursor Types

Operations in a relational database work on the entire rowset. The rowset returned by the SELECT statement includes all rows that meet the criteria in the WHERE clause of the statement. This complete set of rows returned by the statement is called a result set. Applications do not always handle the entire result set as a unit effectively. These applications require a mechanism to process one or more rows at a time. Cursors not only provide this mechanism but are an extension of the result set.

Cursors extend result set processing by doing the following:

Allows you to locate specific rows in a result set.

Retrieves a row or a subset of rows from the current position of the result set.

Supports data modification of rows at the current position in the result set.

Provides different levels of visibility support for changes made by other users to the database data displayed in the result set.

Attention:
For a complete description of the SQL Server cursor type, see the "Cursor type (Database engine)" topic in SQL Server Books Online.

A forward-only and scrollable cursor supported by the JDBC specification may or may not be sensitive to changes made to other jobs, and can be read-only or updatable. This functionality is provided by the Sqlserverresultset class of Microsoft SQL Server the Driver JDBC.

The JDBC driver supports downstream standard types:

result set (cursor) type	SQL Server Cursor Type	characteristic	Selection Method	response buffering	Description
Type_forward_only (concur_read_only)	does not apply	Forward only, Read only	Direct	Full	The application must make one (forward) pass in the result set. This is the default behavior, in the same way as type_ss_direct_forward_only cursors. During the execution of the statement, the driver reads the entire result set from the server into memory.
Type_forward_only (concur_read_only)	does not apply	Forward only, Read only	Direct	Adaptive	The application must make one (forward) pass in the result set. The behavior is the same as the behavior of the type_ss_direct_forward_only cursor. When an application requests a row, the driver reads the corresponding row from the server, which minimizes the client memory footprint.
Type_forward_only (concur_read_only)	Fast Forward	Forward only, Read only	Cursor	Full or adaptive	The application must be delivered one (forward) in the result set by using a server cursor. The behavior is the same as the behavior of the type_ss_server_cursor_forward_only cursor.
Type_forward_only (concur_updatable)	Dynamic (forward only)	Forward only, updatable	Direct or cursor	Full or adaptive	The application must make a (forward) pass in the result set before updating one or more rows. By default, the extraction size is fixed when the application invokes the Setfetchsize method of the Sqlserverresultset object. In this case, in order to obtain adaptive buffering, the application must provide a String value of "adaptive" to invoke the Sqlserverstatement object's setresponsebuffering Method.
Type_scroll_insensitive	Static	Scrollable, not updatable	Direct or cursor	Full or adaptive	The application requires a database snapshot.
Type_scroll_sensitive	Key set	can be scrolled and updatable. Row updates are visible and deletions appear as missing data. Inserting from the result set is visible, but inserting from outside the result set is not seen.	Direct or cursor	Full or adaptive	The application must see only the changed data for the existing row.
Type_ss_direct_forward_only	does not apply	Forward only, Read only	Direct or cursor	Full or adaptive	Integer value = 2003. Provides a fully buffered read-only client cursor. Server cursors are not created.
Type_ss_server_cursor_forward_only	Fast Forward	Only in	Direct or cursor	Full or adaptive	Integer value = 2004. Fast access to all data.
Type_ss_scroll_static	Static	Do not reflect updates from other users	Direct or cursor	Full or adaptive	Integer value = 1004. The application requires a database snapshot. This is a synonym for JDBC type_scroll_insensitive specific to SQL Server.
Type_ss_scroll_keyset	Key set	Reflect updates from other users; line memberships are fixed	Direct or cursor	Full or adaptive	Integer value = 1005. The application must see only the changed data for the existing row. This is a synonym for JDBC type_scroll_sensitive specific to SQL Server.
Type_ss_scroll_dynamic	Dynamic	can be scrolled and updatable. Row updates are visible, and deletions appear as temporary missing data in the current fetch buffer. Inserting from the result set and inserting from outside the result set are visible.	Direct or cursor	Full or adaptive	Integer value = 1006. The application must see the changed data for the existing rows and see the inserted and deleted rows during the lifetime of the cursor.

cursor Positioning

The Type_forward_only, type_ss_direct_forward_only, and type_ss_server_cursor_forward_only cursors only support the next positioning method.

Type_ss_scroll_dynamic cursors do not support absolute and GetRow methods. The absolute method is approximately equivalent to invoking the relative method for a dynamic cursor combination.

The GetRow method is only subject to type_forward_only, Type_ss_direct_forward_only, Type_ss_server_cursor_forward_only, TYPE_SS_ Scroll_keyset and type_ss_scroll_static cursor support. The GetRow method with all the forward-only cursor types returns the number of rows read by the cursor to date.

Attention:
When an application makes an unsupported cursor positioning call or an unsupported call to the GetRow method, an exception is thrown and displays the message "This cursor type does not support the requested operation."

Only Type_ss_scroll_keyset and equivalent type_scroll_sensitive cursors display deleted rows. If the cursor is positioned on a deleted row, the column value is not available and the RowDeleted method returns "true." A call to the get<type> method throws an exception and displays the message "Cannot get a value from a deleted row." The deleted row cannot be updated. If you try to call the update<type> method on a deleted row, an exception is thrown and the message "Cannot update deleted rows." The type_ss_scroll_dynamic cursor has this same behavior until the cursor is moved out of the current fetch buffer.

Forward and dynamic cursors display a deleted row in a similar manner, but only if the cursor is still accessible in the extraction buffer. For forward cursors, this is fairly straightforward. For dynamic cursors, however, this becomes more complex when the extraction size is greater than 1 o'clock. The application may move the cursor back and forth within the window defined by the fetch buffer, but the deleted row disappears when you leave the original fetch buffer in which the deleted rows are updated. If your application does not want to view temporarily deleted rows by using dynamic Grand, you should use the Fetch relative (0).

If you use a cursor to update the key value of Type_ss_scroll_keyset or Type_scroll_sensitive Renter, the row remains in its original position in the result set, regardless of whether the updated row conforms to the cursor's selection criteria. If the row is updated outside the cursor, the deleted row appears in the original position of the row, however, it appears in the cursor only if another row with the new key value exists in the cursor but it has previously been deleted.

For dynamic cursors, the updated row retains its position in the fetch buffer until it leaves the window defined by the extraction buffer. The updated rows may then appear in different locations in the result set, or they may disappear completely. Applications that have temporary inconsistencies in the result set must be prevented from using a fetch size of 1 (the default value is 8 for the concur_ss_scroll_locks concurrency mechanism and 128 for other concurrency mechanisms). Cursor Conversions

SQL Server sometimes chooses to implement a different cursor type than the requested cursor type, which is called an implicit cursor conversion (or a cursor demotion). For more information about implicit cursor conversions, see the "Using Implicit cursor Conversions" topic in SQL Server Books Online.

For SQL Server 2000, when you update data through resultset.type_scroll_sensitive and resultset.concur_updatable result sets, Throws an exception and displays the message "cursor is read-only". This exception occurs because SQL Server 2000 has performed an implicit cursor conversion on the result set and does not return the requested updatable cursor.

To resolve this issue, you can use one of the following two solutions:

Confirm that the underlying table has a primary key

When you create a statement, you use sqlserverresultset.type_ss_scroll_dynamic instead of using resultset.type_scroll_sensitive. Cursor Update

For cursors, in-place updates to cursors are supported if the cursor type and concurrency support updates. If the cursor is not positioned on an updatable row in the result set (theget<type> method call is unsuccessful), calling the update<type> method throws an exception and displays the message "The result set has no current row". The JDBC specification provides that an exception is thrown when the Update method is called Concur_read_only the cursor column. In cases where rows cannot be updated because of such reasons as optimistic concurrency conflicts (such as competing updates or deletes), exceptions may not be thrown until insertrow, Updaterow, or deleterow are invoked.

After calling update<type> ,get<type> will not be able to access the affected columns until updaterow or cancelrowupdates is invoked. This avoids the associated problem of updating columns using a type different from the type returned by the server, and subsequent getter calls may invoke client type conversions (which give inaccurate results), and so on. Calling get<type> throws an exception and displays the message "Cannot access the updated column until the Updaterow () or cancelrowupdates ()" is invoked.

Attention:
If you call the Updaterow method when no columns are updated, the JDBC driver throws an exception and displays the message "called Updaterow () when no columns were updated."

After Movetoinsertrow is invoked, if the result set is invoked except get<type>,update<type>,InsertRow, And any other method than the cursor positioning method (including Movetocurrentrow), an exception is thrown. The Movetoinsertrow method effectively places the result set in insert mode, and the cursor positioning method terminates the insert mode. A relative cursor positioning call moves the cursor relative to the position it was in before the Movetoinsertrow was invoked. After the cursor positioning call, the final target cursor position becomes the new cursor position.

If the cursor positioning call made in insert mode is unsuccessful, the cursor position after the call failure is the original cursor position before the call to Movetoinsetrow . If the insertrow fails, the cursor remains in the inserted row, and the cursor remains in insert mode.

The column in the inserted row is initially in an uninitialized state. Call the update<type> method to set the column state to initialized. Calling the get<type> method for an uninitialized column throws an exception. Calling the InsertRow method returns all columns located in the inserted row to the uninitialized state.

If none of the columns are initialized when the InsertRow method is invoked, the column's default value is inserted. If there is no default value and the column is nullable, NULL is inserted. If there is no default value and the column is not nullable, the server returns an error, and an exception is thrown.

Attention:

In insert mode, the call to the GetRow method returns 0. The JDBC driver does not support locating updates or deletes. Depending on the JDBC specification, invoking the Setcursorname method will have no effect, and calling the Getcursorname method throws an exception. Read-only and static cursors are always unavailable for updating. SQL Server restricts server cursors to a single result set. If a batch or stored procedure contains more than one statement, you must use a read-only client-only cursor.

Cursor response buffering

For any result set that indicates its SQL Server cursor type in the previous table, the maximum number of rows specified by the Setfetchsize method of the Sqlserverresultset object can be stored in the buffer. In such cases, Microsoft SQL Server Driver JDBC version 1.2 ignores the value of the responsebuffering setting, but the application overrides the Responseb Uffering settings, such as those specified in Type_forward_only (concur_updatable) cases. In such cases, the application can invoke the Sqlserverstatement object by using the String value " Adaptive ". Setresponsebuffering method to override the responsebuffering setting.