Rick Vicik
Created: March 20, 1992
A key new feature in Microsoft® SQL Server version 4.2 is the implementation of cursors. This article gives an overview of cursors and how they are implemented using DB-Library. The new DB-Library functions are listed and discussed.
Relational databases are set oriented. There is no concept of "next row" nor is there any way to operate on rows of a set individually, except with cursors. A cursor allows the individual rows of a results set to be processed one at a time, in a fashion similar to using a conventional disk file. The cursor is so called because it indicates the current position in a results set, as the cursor on a CRT screen indicates the current position.
A new feature of the Microsoft® DB-Library (version 4.2 and later) is the implementation of cursors. This implementation is unique because it permits forward and backward scrolling (within certain limitations), direct access by position in the results set, and positioned UPDATE (even if the results set was defined with an ORDER BY clause). The current row can be an entire screenful of rows, and changes to the locally buffered rows can be reflected to the database in a single operation. Sensitivity to changes made by users is adjustable, and there are several concurrency control options.
Cursors can be used in place of browse mode whenever scrolling through a results set, making occasional updates, is necessary. Cursors are less restrictive than browse mode because updating tables that do not contain timestamps is possible and because using multiple database connections is unnecessary. Unlike browse mode, cursors access data from the original tables, not from a snapshot copy. A unique index is required, but this should not be a problem because good relational database design requires that each table have a primary key, and the only efficient way to enforce uniqueness is with an index. A single front-end can have multiple cursors in use at any time through a single connection.
Catalog information is obtained by using the Microsoft catalog stored procedures rather than by accessing the catalog tables directly. Thus, DB-Library cursors can be used with the Micro Decisionware® DB2 gateway and with other gateways that implement the Microsoft catalog stored procedures.
Cursors can be classified into three main categories with regard to sensitivity to change: static, keyset-driven, and dynamic. Most databases on the market today are either static or keyset-driven.
In a static cursor, neither values, membership in the results set, nor ordering of the results set can change while the cursor is open. Making a snapshot copy of the entire results set or locking the entire results set ensures this behavior. You can implement static cursors by doing a SELECT ... INTO on a temporary table and then using the CURSOR application programming interfaces (APIs) on that table. Explicit static cursor functionality is unnecessary.
In a keyset-driven cursor, membership of rows in the results set as well as the ordering are fixed at open time (although changes to values made by the cursor owner or other users are visible). If a change causes the row to no longer qualify for membership or affects its ordering, the row does not disappear or move unless the cursor is closed and reopened. Deleted rows return a special "missing" error code when accessed, as do updates to the key field. Inserts by other users are not visible. In version 4.2, inserts made by the cursor owner are not visible either, but adding them to the end of the keyset is technically possible. Rows can be accessed by relative or absolute position in the results set.
In a dynamic cursor, the effect of all changes made by anyone is visible at the next SCROLL operation. Changes that affect membership or ordering are visible, as well as inserts and deletes (deleted rows do not leave "holes"). Rows can be accessed by relative (but not absolute) position in the results set.
If the results set is too large to fit in the keyset buffer, the cursor is "mixed" (keyset/dynamic). Within the keyset, the cursor behavior is keyset-driven. Cursor behavior is dynamic when a FETCH operation crosses a keyset boundary. In a mixed cursor, fetch previous cannot cross a keyset boundary.
The cursor definition can contain joins, views, or subselects. In Microsoft® SQL Server version 4.2, only a single view can be used. The view can contain joins, but the view cannot be joined with other views or base tables. ORDER BY, GROUP BY, and HAVING can be used in the cursor definition. FOR BROWSE, INTO, COMPUTE, and UNION cannot be used. Aggregates and expressions can be used but cannot be updated. Updates to a cursor on a view are allowed subject to the normal restrictions on view updates.
In SQL Server version 4.2, ORDER BY, GROUP BY, and HAVING cannot be used with a dynamic cursor. This restriction may be lifted in a later release. The other restrictions on what is allowed in the SELECT statement are the same as for static cursors.
A clustered index is necessary for optimum performance. If there is an ORDER BY or GROUP BY, be sure the clustered index can be used instead of sorting.
No results can be pending on the connection when a DB-Library CURSOR operation is executed. DB-Library row buffering cannot be used with DB-Library cursors. The old DB-Library row buffering should be unnecessary given the capabilities of the cursor fetch buffer.
The Microsoft implementation of DB-Library cursors provides several options for controlling concurrent access to the same data. At cursor open time, one of the following options must be specified: READONLY, LOCKCC, OPTCC, or OPTCCVAL.
If READONLY is specified, updates are not permitted. If LOCKCC is specified, the set of rows currently in the fetch buffer is locked, and no other user can update or read those rows. Updates issued by the cursor owner, however, are guaranteed to succeed. Locks set with the LOCKCC option require a BEGIN TRAN statement. If OPTCC or OPTCCVAL is specified, the rows currently in the fetch buffer are not locked, and other users can access them freely. Collisions between updates issued by the cursor owner and updates made by other users are detected by saving and comparing timestamps or column values. If either of the optimistic concurrency control options is specified, the application must handle collisions with the updates of other users. This might involve showing the user both original and current row contents, for example, and asking if the update should still be applied.
The two optimistic concurrency control options detect collisions differently. If OPTCC is specified, timestamps are used if available; otherwise, the value of each selected column is saved and compared with its previous value. If OPTCCVAL is specified, the value comparison is used regardless of whether a timestamp is available.
Five new functions were added to DB-Library to support cursors:
Declares and opens the cursor; specifies the fetch buffer size, the keyset size, and the concurrency control option.
hc=dbcursoropen(dbproc, stmt, keyset, ccopt, nrows, rowstat)
where:
Any other integer indicates mixed (keyset-driven within the keyset, fully dynamic outside the keyset).
The last two can apply at the same time. The values are ORed together.
This function sets up for the execution of the SELECT statement but does not actually execute it until the FETCH.
Error conditions:
Registers an array of datatype datatype and an array to receive the actual length of each instance of the column. This function works exactly like the original dbbind function.
dbcursorbind(hc, col, datatype, size, outlen, buffer)
where:
Error conditions:
Fills the fetch buffer with rows from the database. If program variables are registered with a valid dbbind type, the data is moved into those variables (they are assumed to be arrays). If nobind is specified, the address(es) of each row is placed in the array of buffer pointers specified in the dbcursorbind function.
dbcursorfetch(hc, fetchtype, rownumber)
where:
If a FETCH operation reaches a keyset boundary before completely filling the fetch buffer on mixed cursors, the FETCH operation terminates, and the remaining fetch buffer rows are not filled. The row-status array can be used to determine which rows were actually fetched.
Performs positioned UPDATE and DELETE with a single function that also does inserts, updates for specific rows, and read-for-updates (which prevent other users from changing the data read).
dbcursor(hc, optype, bufno, table, values)
where:
Four formats are possible for the values parameter: two for UPDATE and two for INSERT. Each has a full format and an abbreviated format. The format must match the optype (UPDATE or INSERT). The full format is a complete SQL statement (UPDATE or INSERT) without a WHERE clause. The abbreviated format is the SET clause (UPDATE) or the VALUES clause (INSERT) only. When the full format is used, the specified table name overrides the table specified in the table parameter of dbcursor.
Updates and deletes are guaranteed to succeed if the cursor is defined with CUR_LOCKCC. If the rows are not locked, optimistic concurrency control is used, and the UPDATE or DELETE may fail with "row not found" or "row changed." If a row has changed, the buffer value can be updated with CRS_REFRESH. To guarantee that a row cannot change without locking all the rows in the fetch buffer, use the dbcursor function with optype CRS_LOCKCC to refetch a specific row and acquire an exclusive lock on it. These locks are released at COMMIT time. Locks are not automatically released when the next FETCH is executed.
If a positioned operation that applies to multiple rows fails, the array of row status indicators (declared on the dbcursoropen) can be used to determine which failed.
The specified row in the fetch buffer is used to construct a searched UPDATE. The WHERE clause is constructed from the values in the specified fetch buffer. The WHERE clause includes only the key columns if the row was locked when fetched. If the row was not locked, the WHERE clause also contains either the timestamp or the rest of the selected columns. The SET clause is constructed from the values in the specified fetch buffer or from the expressions supplied in the values parameter. If the cursor definition involves a join, the table parameter must be used to specify that the update applies to only one of the tables in the join. An update with a values parameter updates the specified row in the fetch buffer. If the update changes the unique index value, the row appears as missing.
Error conditions:
This is a convenient way to construct an INSERT statement from the buffers bound with dbcursorbind. The data from the specified buffer is used to construct an INSERT statement. The buffer is not updated until the next dbcursorfetch is executed.
Error conditions:
The specified row in the fetch buffer is updated using the concurrency control specified with the cursor definition. This function is used to update the specified row if an UPDATE or DELETE fails as a result of optimistic concurrency control. It can also be used to update the buffer after a delete or an update to a join. For example, a positioned UPDATE to a column in the master base table of a master-detail join would require that the entire buffer be updated so that all rows in the buffer would have the new value. Whether deletes disappear or leave holes depends on whether the cursor is keyset-driven or dynamic.
Error conditions:
An exclusive lock is acquired on the database page corresponding to the specified row in the fetch buffer. This function is used to gain exclusive control over a single row for the purpose of updating it. Set a "deadman" timer to avoid holding this lock forever.
Error conditions:
Closes the specified cursor and releases all resources such as buffered keys and timestamps, bound columns, and so on.
dbcursorclose(hc)
If the results set is small or if static membership is appropriate for the application, set Scroll-Option to Keyset Driven. Relative and absolute row access is possible, and deletes leave holes. Values change between fetches, but rows do not move around if the changes affect ORDER BY columns; nor do they disappear if they no longer satisfy the WHERE clause. The example demonstrates page forward and page back and direct access positioning by means of the scroll bar slider position.
DBPROC *dbproc;
DBCURSOR *hc;
DBINT rowstats[20],idl[20],namel[20],cols,rows,r,RC;
DBINT id[20];
char name[20][40];
.
.
.
hC=dbcursoropen(dbproc,"SELECT id,name FROM emp",
CUR_KEYSET, CUR_READONLY, 20, rowstats);
dbcursorinfo(hC,&cols,&maxrows);
dbcursorbind(hC, 1, INTBIND, sizeof(id), idl, id );
dbcursorbind(hC, 2, STRINGBIND, sizeof(name), namel, name );
.
.
.
case SB_PAGEDOWN:
RC=dbcursorfetch(hC, FETCH_NEXT, NULL);
break;
case SB_PAGEUP:
RC=dbcursorfetch(hC, FETCH_PREV, NULL);
break;
case SB_THUMBPOSITION:
RC=dbcursorfetch(hC, FETCH_RANDOM, (lParam*,maxrows)/100 );
break;
.
.
.
if( RC!=ERROR ){
for( r=0,y=y0; r<20; r++,y+=VSpacing ){
if( rowstats[r]==FTC_MISSING )
SetTextColor(hDC, RGB(255,0,0) );
else
SetTextColor(hDC, RGB(0,0,0) );
TextOut(hDC, x1, y, id[r], idl[r] );
TextOut(hDC, x2, y, name[r], namel[r] );
}
}
.
.
.
For very large results sets, buffering all the keys may be impossible. In such cases, set Scroll-Option to the maximum number of rows of keys to be buffered. Within the keyset, fast relative and absolute row access is possible, regardless of the availability of a suitable index. The keyset window can scroll as the buffered row set is about to leave the keyset. If a clustered index is available, scrolling the keyset forward is relatively fast, but scrolling it back is slow. If the SELECT statement contains an ORDER BY clause that cannot be executed by using a clustered index, scrolling the keyset forward is slow because repeated sorting is necessary.
If dynamic membership is appropriate for the application, set Scroll-Option to Dynamic. The keyset size then equals the buffered keyset size. For adequate performance, fetch 10–20 rows. Paging forward is relatively fast if a clustered index matches the ORDER BY clause, but paging back is slow. Absolute positioning is impossible.
DBPROC *dbproc;
DBCURSOR *hc;
DBINT rowstats[20],idl[20],namel[20],cols,rows,r;
DBINT RC,delta,CurRow=0,maxrows=40;
DBINT *idptrs[20];
char *nameptrs[20];
.
.
.
hC=dbcursoropen(dbproc,"SELECT id,name FROM emp",
CUR_DYNAMIC, CUR_READONLY, 20, rowstats);
dbcursorbind(hC, 1, NOBIND, sizeof(id), idl, idptrs );
dbcursorbind(hC, 2, NOBIND, sizeof(name), namel, nameptrs );
.
.
.
case SB_PAGEDOWN:
RC=dbcursorfetch(hC, FETCH_NEXT, NULL);
delta=20;
break;
case SB_PAGEUP:
RC=dbcursorfetch(hC, FETCH_PREV, NULL);
delta=-20;
break;
case SB_THUMBPOSITION:
delta=(lParam*maxrows)/100 -CurRow;
RC=dbcursorfetch(hC, FETCH_RANDOM, delta );
break;
.
.
.
if( RC!=ERROR ){
CurRow+=delta;
if( maxrows/CurRow<=2 ) maxrows+=maxrows*(CurRow/delta);
for( r=0,y=y0; r<20; r++,y+=VSpacing ){
if( rowstats[r]==FTC_MISSING )
SetTextColor(hDC, RGB(255,0,0) );
else
SetTextColor(hDC, RGB(0,0,0) );
TextOut(hDC, x1, y, *idptrs[r], idl[r] );
TextOut(hDC, x2, y, *nameptrs[r], namel[r] );
}
}
.
.
.
For typical interactive browse or update applications, set Scroll-Option to handle the greatest page-backward request anticipated. The keyset size is specified in units of the local buffer size (20 * 50 = 1000 rows). Fetch one screenful of rows at a time. Rely on optimistic concurrency control to protect against lost updates. The bufno parameter in dbcursor is specified as row+1 because it is 1-based; references to array elements in C are 0-based.
DBPROC *dbproc;
DBCURSOR *hc;
DBINT rowstats[20],idl[20],namel[20],cols,rows,r,RC;
DBINT id[20];
char name[20][40];
.
.
.
hC=dbcursoropen(dbproc,"SELECT id,name FROM emp",
CUR_KEYSET, CUR_OPTCC, 20, rowstats);
dbcursorbind(hC, 1, INTBIND, sizeof(id), idl, id );
dbcursorbind(hC, 2, STRINGBIND, sizeof(name), namel, name );
.
.
.
case VK_PAGEDOWN:
RC=dbcursorfetch(hC, FETCH_NEXT, NULL);
break;
case VK_PAGEUP:
RC=dbcursorfetch(hC, FETCH_PREV, NULL);
break;
case VK_ENTER:
for( r=0,y=y0,RC=SUCCEED; r<20; r++,y+=VSpacing ){
if( SendMessage( hID[r], EM_GETMODIFY, ... )
|| SendMessage( hName[r], EM_GETMODIFY, ... ) ){
SendMessage( hID, EM_GETLINE, 0, id[r] );
SendMessage( hName, EM_GETLINE, 0, name[r] );
RC=dbcursor( hC, CRS_UPDATE, r+1, NULL, NULL );
}
if( SendMessage( hDelFlag[r], EM_GETMODIFY, ... ) )
RC=dbcursor( hC, CRS_DELETE, r+1, NULL, NULL );
if( RC!=SUCCEED ){
RC=dbcursor( hC, CRS_REFRESH, r+1, NULL, NULL );
SetTextColor(hDC, RGB(255,0,0) );
TextOut(hDC, x1, y[r], id[r], idl[r] );
TextOut(hDC, x2, y[r], name[r], namel[r] );
}
}
.
.
.
In high-contention environments, refetching the row on which the user is operating with the lock option may be more desirable than locking an entire screenful of rows. In the example below, when an entry field gets focus, an array of entry field window IDs is scanned. If there is a match, the row and column numbers corresponding to the entry field are known. The appropriate row can then be refetched with lock. Whenever focus is lost, the entry field is asked whether it was changed. If it was, the row is updated. The application must issue BEGIN TRAN and COMMIT at the appropriate places to control the holding and releasing of locks.
Fetching the entire buffered row set with lock may be appropriate in some environments, although concurrency will be reduced.
DBPROC *dbproc;DBCURSOR *hc;DBINT rowstats[20],idl[20],namel[20],cols,rows,r,RC;DBINT id[20];char name[20][40];...hC=dbcursoropen(dbproc,"SELECT id,name FROM emp",CUR_KEYSET, CUR_OPTCC, 20, rowstats);dbcursorbind(hC, 1, INTBIND, sizeof(id), idl, id );dbcursorbind(hC, 2, STRINGBIND, sizeof(name), namel, name );...case EN_SETFOCUS:execsql(dbproc,"begin tran");for( r=0; r<20; r++ ){if( lParam==hID[r] || lParam==hName[r] )RC=dbcursor(hC, CRS_LOCKCC, r+1 NULL, NULL);}break;case EN_KILLFOCUS:for( r=0; r<20; r++ ){if( SendMessage( hID[r], EM_GETMODIFY, ... )|| SendMessage( hName[r], EM_GETMODIFY, ... ) ){SendMessage( hID, EM_GETLINE, 0, id[r] );SendMessage( hName, EM_GETLINE, 0, name[r] );RC=dbcursor( hC, CRS_UPDATE, r+1, NULL, NULL );}}execsql(dbproc,"commit tran");break;...