| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
Open Client DB-Library/C Reference Manual |
![[-]](/dwicons/i_colpse.gif)
| Appendix A: Cursors |
This appendix introduces DB-Library's cursor routines. The appendix:
Because relational databases are oriented toward sets, no concept of next row exists, meaning that you cannot operate on an individual row in a set. Cursor functionality solves this problem by letting a result set be processed one row at a time, similar to the way you read and update a file on a disk. A DB-Library cursor indicates the current position in a result set, just as the cursor on your screen indicates the current position in a block of text.
DB-Library cursors are client-side cursors. This means that they do not correspond to a SQL Server cursor, but emulate a cursor that appears to the user to be in the server. The DB-Library cursor transparently does keyset management, row positioning, and concurrency control entirely on the client side.
The DB-Library cursor routines offer the following capabilities, with certain limitations:
Cursors let the user scroll through and update a result set with fewer restrictions than browse mode. Although cursors require a unique index, they do not require a timestamp nor a second connection to a database for updates. Also, they do not create a copy of the entire result set. The following table summarizes these differences:
Item | Cursors | Browse Mode |
|---|---|---|
Row timestamps | Not required | Required |
Multiple connections for updates | Unnecessary | Necessary |
Table usage | Use original tables | Uses a copy of tables |
A DB-Library cursor does not correspond to an actual SQL Server cursor. Instead, at the time the cursor is declared with dbcursoropen, DB-Library fetches keysets from SQL Server "under the covers." It then builds qualifiers based on the keys for the current row and sends them to SQL Server. The server parses the query and returns a result set. When dbcursorfetch is called to retrieve more data, the DB-Library cursor may have to do additional selects. In addition, SQL Server may have to parse the query each time dbcursorfetch is called.
A Client-Library cursor corresponds to an actual cursor in SQL Server. It is sometimes referred to, therefore, as a native cursor. A new TDS protocol allows Client-Library to interact with the server to manage the cursor.
A Client-Library cursor is faster than a DB-Library cursor because it does not have to send SQL commands to the server, which causes multiple re-parsing of the query. But because the result set remains on the server side, it cannot offer the same options for concurrency control as a DB-Library cursor.
The following table summarizes these and additional differences between the two cursors:
DB-Library Cursor | Client-Library Cursor |
|---|---|
Cursor row position is defined by the client. | Cursor row position is defined by the server. |
Can define optimistic concurrency control (allows dirty reads). | Cannot define optimistic concurrency control (does not allow dirty reads). |
Can fetch backward (if CUR_KEYSET or CUR_DYNAMIC is specified for scrollopt during dbcursoropen). | Can only fetch forward. |
More memory may be required if you query very large row sizes, unless you specify a smaller number of rows in the fetch buffer during dbcursoropen. | More memory is not required, regardless of how large the row sizes are. |
You cannot access an Open Server application unless the application installs the required DB-Library stored procedures. | You can access a release 10.0 (or later) Open Server application that is coded to support cursors. |
Slower performance. | Faster performance. |
Three broad categories identify cursors according to their sensitivity to change:
In a static cursor, neither the cursor owner nor any other user can change the result set while the cursor is open. Values, membership, and order remain fixed until the cursor is closed. You can either take a snapshot of the result set (which begins to diverge from the snapshot as updates are made), or you can lock the entire result set to prevent updates.
It is not necessary for cursor routines to support static cursors directly. You can achieve static behavior through one of the following methods:
In a keyset-driven cursor, the order and the membership of rows in the result set are fixed at open time, but changes to values may be made by the cursor owner. Committed changes made by other users are visible. If a change affects a row's order, or results in a row no longer qualifying for membership, the row does not disappear or move unless the cursor is closed and reopened. If the cursor remains open, deleted rows, when accessed, return a special error code that says they are missing. Updating the key also causes the rows to be "missing."
Inserted data does not appear, but changes to existing data do appear when the buffer is refreshed. With or without order by, the user can access rows by either relative or absolute position.
To access a row by relative position, move the cursor relative to its current position. For example, if the cursor is on row three and you want to access row eight, tell the cursor to jump five rows relative to its current position. The cursor jumps five rows to row eight.
To access a row by absolute position, tell the cursor the number of the row you want to access. For example, if the cursor is on row three and you want to access row eight, tell the cursor to jump to row eight.
In a dynamic cursor, uncommitted changes made by the cursor owner and committed changes made by anyone become visible the next time the user scrolls. Changes include inserts and deletes as well as changes in order and membership. (Deleted rows do not leave holes.) The user can access rows by relative (but not absolute) position in the result set. Dynamic cursors cannot use an order by clause.
Cursors control, through several options, concurrent access. Concurrent access occurs when more than one user accesses and updates the same data at the same time. During concurrent access, data can become unreliable without some kind of control. To activate the particular concurrency control desired, specify one of the following options when you open a cursor:
Option | Result |
|---|---|
CUR_READONLY | Updates are not permitted. |
CUR_LOCKCC | The set of rows currently in the client buffer is locked when they are fetched inside a user-initiated transaction. No other user can update or read these rows. Updates issued by the cursor owner are guaranteed to succeed. |
CUR_OPTCC and CUR_OPTCCVAL | Rows currently in the buffer are not locked, and other users can update or read them freely. |
To detect collisions between updates issued by the cursor owner and those issued by other users, cursors save and compare timestamps or column values. Therefore, if you specify either of the optimistic concurrency control options (CUR_OPTCC or CUR_OPTCCVAL) your updates may fail because of collisions with other updates. You may want to design the application to refresh the buffer and then retry updates that fail.
The two optimistic concurrency control options differ in the way they detect collisions:
Option | Method of Detection |
|---|---|
CUR_OPTCC | Optimistic concurrency control based on timestamp values. Compares timestamps if available; otherwise, saves and compares the value of all non-text, non-image columns in the table with their previous values. |
CUR_OPTCCVAL | Optimistic concurrency control based on values. Compares selected values whether or not a timestamp is available. |
The following list summarizes the DB-Library cursor routines:
For details about an individual routine, see its reference page.
To retain the flexibility of the SQL Server transaction model, cursors do not automatically issue begin transaction or commit transaction. The duration of locks acquired during cursor operations is entirely under the control of the application. In other words, an application that uses CUR_LOCKCC on either the dbcursoropen or dbcursor routine must also issue begin transaction for the locking to have any effect.
To hold the lock on the currently buffered rows when CUR_LOCKCC is used on dbcursoropen, the application must issue commit transaction and begin transaction before each dbcursorfetch that scrolls the local buffer (except for the very first dbcursorfetch, which should be preceded only by begin transaction).
To use the short-duration locking feature, issue begin transaction before locking the row to be updated with the CUR_LOCKCC option of dbcursor. If each update is independent, issue commit transaction after each update. If multiple updates to the same screenful of data depend on each other, issue commit transaction when the screen is scrolled.
For repeatable-read consistency, specify holdlock in the select statement in dbcursoropen, and issue begin transaction before the first dbcursorfetch. Locks are obtained as the data is fetched and are retained until the application issues commit transaction or rollback transaction.
Although you can close and reopen a repeatable-read cursor, you can get the same effect with FETCH_FIRST.
Other combinations are possible as well. The important thing to remember is that locks are not held unless begin transaction is in effect. Locks acquired while begin transaction is in effect are held until a commit transaction or rollback transaction is issued.
DB-Library's cursor routines call the SQL Server's catalog stored procedures to find out table formats and identify key columns.
For more information about the catalog stored procedures, see the SQL Server Reference Manual.
|
|