dbcursoropenRick Vicik
Created: March 20, 1992
ABSTRACT
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 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:
dbcursoropen
dbcursorbind
dbcursorfetch
dbcursor
dbcursorclose
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:
hc (“cursor handle”) is returned and is used in subsequent cursor functions. All other cursor operations use hc, not dbproc, which allows multiple cursors to be used simultaneously with only one connection.
stmt is a SQL SELECT statement that defines the results set. It cannot contain FOR BROWSE, INTO, COMPUTE, or UNION. The statement can be a join, or it can reference a single view (which contains joins). Stored procedures and batches containing multiple SQL statements or flow-of-control logic are not allowed.
keyset specifies the size of the keyset in multiples of the fetch buffer window size (the maximum number of rows of keys to be buffered by DB-Library). keyset is specified not in rows but in units of nrows. The size of the keyset with respect to the size of the results set determines whether the cursor is forward-only, fully dynamic, keyset-driven, or mixed. The special values are:
CUR_FORWARD, which indicates scroll forward only
CUR_DYNAMIC, which indicates fully dynamic
CUR_KEYSET, which indicates fully keyset-driven
Any other integer indicates mixed (keyset-driven within the keyset, fully dynamic outside the keyset).
ccopt is the concurrency control option and specifies whether the cursor is read-only (CUR_READONLY) or if “intent-to-update” locks (CUR_LOCKCC) or optimistic concurrency control (CUR_OPTCC) is to be used. Positioned UPDATE and DELETE can be performed only within the buffered row set and only if the rows were fetched with CUR_LOCKCC, CUR_OPTCC, or CUR_OPTCCVAL. CUR_LOCKCC guarantees the success of the operation. CUR_OPTCC performs optimistic concurrency control by comparing timestamps, and CUR_OPTCCVAL performs optimistic concurrency control by comparing values; the operation fails if the row has changed since last fetched.
nrows is the fetch buffer size. All fetches attempt to fetch the specified number of rows. If fewer rows are fetched, rowstat indicates how many were fetched.
rowstat is an array of row status codes indicating which rows had a problem during multirow fetches of multirow updates, deletes, or inserts. rowstat must contain nrows elements. The possible status codes are:
FTC_SUCCEED, which indicates row fetched
FTC_MISSING, which indicates row deleted since cursor opened
FTC_ENDOFKEYSET, which indicates row not fetched because keyset reached
FTC_ENDOFRESULTS, which indicates row not fetched because end of results reached
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:
SELECT statement fails
No unique index
Not enough memory for keyset
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:
hc is the cursor handle created by a previous dbcursor function.
col is the column number in the select list (1-based, same as dbbind).
datatype is one of the dbbind datatypes. nobind is a special flag indicating that the data should not be moved into program buffers when the FETCH executes; instead, the addresses of the data are inserted into the BUFADR array. The application can then use those addresses to access the data directly from the DB-Library buffers.
size is the maximum buffer size (required for variable-length items only). Fixed-length items such as integers have an implicit length.
outlen is an optional array of integers to receive the actual length of each instance of this column. The number of elements must be the same as the nrows parameter used on the dbcursoropen function. outlen elements can also be used to indicate null input columns when updating or inserting from buffers bound with dbcursorbind (0=NULL). If a variable-length datatype (varycharbind or varybinarybind) or the nobind flag is specified, the actual lengths of the items must be inserted into this parameter before calling dbcursor with the UPDATE or INSERT optype. If a null-terminated datatype is specified (stringbind or ntbstringbind), nonzero values of this parameter are ignored, and the length of each item is determined by scanning for the null terminator.
buffer is an array program buffer to receive the column data. The number of elements must match the nrows parameter of the dbcursoropen function that created the hc. The maximum size of each element is either implied by the datatype parameter or specified by the size parameter.
Error conditions:
Invalid cursor handle
Invalid datatype
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:
fetchtype is either FETCH_NEXT, FETCH_PREV, FETCH_FIRST, FETCH_LAST, FETCH_RANDOM, or FETCH_RELATIVE. (RANDOM can be used only if the cursor was declared keyset-driven.)
rownumber is used only with RANDOM or RELATIVE. The buffer is filled starting with the specified row.
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.
Error conditions:
Out of memory
Server or connection failure
Can’t go back to previous keyset (mixed)
RANDOM and LAST require keyset-driven cursor
FORWARD-ONLY can use only NEXT or FIRST
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:
optype is the operation type: CRS_UPDATE, CRS_DELETE, CRS_INSERT, CRS_REFRESH, or CRS_LOCKCC.
bufno specifies the row on which the fetch buffer is to operate. “0” indicates “all buffers” (SQL Server version 4.2 implements this only for REFRESH). The new values for UPDATE and INSERT operations are taken from the specified buffer or from the values parameter if one is supplied. bufno is 1-based; references to array elements in C are 0-based.
table identifies the table to which the operation applies when the cursor definition involves a join. It may be specified as NULL when the cursor definition does not involve a join.
values is used only with the UPDATE or INSERT optype and only when the new value must be specified as an expression involving the old value (c1=c1+1). It must be a SET clause for updates or a VALUES clause for inserts. In most cases this parameter can be set to NULL, and the new value for each column is taken from the fetch buffer (the program variable specified by dbcursorbind).
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:
Row changed or deleted
Invalid bufno
Read-only cursor
Invalid SET clause
No buffers bound
Other update errors (security, rules, and so on)
The specified row in the fetch buffer is used to construct a searched DELETE. The WHERE clause is constructed in the same way as with UPDATE. If the cursor definition involves a join, the table parameter must be used to specify that the delete applies to only one of the tables in the join. The fetch buffer is not automatically updated after a delete.
Error conditions:
Row changed or already deleted
Invalid bufno
Read-only cursor
Other delete errors (security, rules, and so on)
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:
No buffers bound
Invalid bufno
Read-only cursor
Other insert failures (security, rules, and so on)
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:
Row locked
Row deleted
Row changed
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:
Row locked
Row deleted
Row changed
Read-only cursor
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;
.
.
.