How Cursors Are Implemented
ODBC applications control the behavior of a cursor by setting one or more statement attributes before executing an SQL statement. ODBC has two different ways to specify the characteristics of a cursor:
- Cursor type
Cursor types are set using the SQL_ATTR_CURSOR_TYPE attribute of SQLSetStmtAttr. The ODBC cursor types are forward-only, static, keyset-driven, mixed, and dynamic. Setting the cursor type was the original method of specifying cursors in ODBC. - Cursor behavior
Cursor behavior is set using the SQL_ATTR_CURSOR_SCROLLABLE and SQL_ATTR_CURSOR_SENSITIVITY attributes of SQLSetStmtAttr. These attributes are modeled on the SCROLL and SENSITIVE keywords defined for the DECLARE CURSOR statement in SQL-92 and ISO SQL standards. These two SQL-92/ISO options were introduced in ODBC version 3.0.
The characteristics of an ODBC cursor should be specified using either one or the other of these two methods, with the preference being to use the ODBC cursor types.
In addition to setting the type of a cursor, ODBC applications also set other options, such as the number of rows returned on each fetch, concurrency options, and transaction isolation levels. These options can be set for either ODBC-style cursors (forward-only, static, keyset-driven, mixed, and dynamic) or SQL-92/ISO style cursors (scrollability and sensitivity).
The SQL Native Client ODBC driver supports several ways to physically implement the various types of cursors. The driver implements some types of cursors using a SQL Server default result set; it implements others as server cursors or by using the ODBC Cursor Library.