API Server Cursors
The OLE DB, ODBC, ADO, and DB-Library APIs support mapping cursors over the result sets of executed SQL statements. The Microsoft® SQL Server™ OLE DB provider, SQL Server ODBC driver, and DB-Library dynamic-link library (DLL) implement these operations through the use of API server cursors. API server cursors are cursors implemented on the server and managed by API cursor functions. As the application calls the API cursor functions, the cursor operation is transmitted to the server by the OLE DB provider, ODBC driver, or DB-Library DLL.
When using an API server cursor in OLE DB, ODBC, and ADO, use the functions or methods of the API to:
- Open a connection.
- Set attributes or properties defining the characteristics of the cursor the API automatically maps over each result set.
- Execute one or more Transact-SQL statements.
- Use API functions or methods to fetch the rows in the result sets.
In DB-Library, use the special DB-Library Cursor Library functions to work with an API server cursor.
When the API cursor attributes or properties are set to their default settings, the SQL Server OLE DB provider and SQL Server ODBC driver use default result sets. Although the API is technically asking for a cursor, the default cursor characteristics match the behavior of a default result set. The OLE DB provider and ODBC driver, therefore, implement the default cursor options using a default result set because it is the most efficient way to retrieve rows from the server. When using default result sets, an application can execute any Transact-SQL statement or batch, but it can only have one outstanding statement on a connection. This means the application must process or cancel all the result sets returned by one statement before it can execute another statement on the connection.
When the API cursor attributes or properties are set to anything other than their defaults, the OLE DB provider for SQL Server and the SQL Server ODBC driver use API server cursors instead of default result sets. Each call to an API function that fetches rows generates a roundtrip to the server to fetch the rows from the API server cursor.
DB-Library applications use the DB-Library Cursor Library functions to request cursors. If DBCLIENTCURSOR is not set, the DB-Library Cursor Library functions use API server cursors in the same way as the SQL Server OLE DB provider and SQL Server ODBC driver.
API Server Cursor Restrictions
An application cannot execute the following statements when using API server cursors:
- Transact-SQL statements that SQL Server does not support in server cursors.
- Batches or stored procedures that return multiple result sets.
- SELECT statements that contain COMPUTE, COMPUTE BY, FOR BROWSE, or INTO clauses.
- An EXECUTE statement referencing a remote stored procedure.
API Server Cursor Implementation
The OLE DB provider for SQL Server, the SQL Server ODBC driver, and the DB-Library DLL use these special system stored procedures to signal cursor operations to the server:
- sp_cursoropen defines the SQL statement to be associated with the cursor and the cursor options, then populates the cursor.
- sp_cursorfetch fetches a row or block of rows from the cursor.
- sp_cursorclose closes and deallocates the cursor.
- sp_cursoroption is used to set various cursor options.
- sp_cursor is used to request positioned updates.
- sp_cursorprepare compiles the Transact-SQL statement or batch associated with a cursor into an execution plan but does not create the cursor.
- sp_cursorexecute creates and populates a cursor from the execution plan created by sp_cursorprepare.
- sp_cursorunprepare discards the execution plan from sp_cursorprepare.
These system stored procedures will show up in SQL Profiler traces of ADO, OLE DB,, ODBC, and DB-Library applications that are using API server cursors. They are intended only for the internal use of the SQL Server Provider for OLE DB, the SQL Server ODBC driver, and the DB-Library DLL. The full functionality of these procedures is available to the applications through the use of the cursor functionality of the database APIs. Specifying the procedures directly in an application is not supported.
When SQL Server executes a statement for a connection, no other statements can be executed on the connection until all the results from the first statement have been processed or canceled. This rule still holds when using API server cursors, but to the application it looks like SQL Server has started supporting multiple active statements on a connection. This is because the full result set is stored in the server cursor and the only statements being transmitted to SQL Server are the executions of the sp_cursor system stored procedures. SQL Server executes the stored procedure, and as soon as the client retrieves the result set it can execute any other statement. The OLE DB provider and ODBC driver always retrieve all the results from an sp_cursor stored procedure before they return control to the application. This lets applications interleave fetches against multiple active server cursors.
This table shows how an application can process two cursors at the same time on a connection using two statement handles.
| Statement handle 1 | Statement handle 2 |
|---|---|
| Set cursor attributes such that an API server cursor will be used. | |
| SQLExecDirect an SQL statement. The ODBC driver calls sp_cursoropen and retrieves the result set returned by the procedure. | |
| Set cursor attributes such that an API server cursor will be used. | |
| SQLExecDirect an SQL statement. The ODBC driver calls sp_cursoropen and retrieves the result set returned by the procedure. | |
| SQLFetchScroll to retrieve the first block of rows. The driver calls sp_cursorfetch and then retrieves the result set returned by the procedure. | |
| SQLFetchScroll to retrieve the first block of rows. The driver calls sp_cursorfetch and then retrieves the result set returned by the procedure. | |
| SQLFetchScroll to retrieve another block of rows. The driver calls sp_cursorfetch and then retrieves the result set returned by the procedure. | |
| SQLFetchScroll to retrieve another block of rows. The driver calls sp_cursorfetch and then retrieves the result set returned by the procedure. | |
| Call SQLFreeStmt or SQLCloseCursor. The driver calls sp_cursorclose. | |
| Call SQLFreeStmt or SQLCloseCursor. The driver calls sp_cursorclose. |
Because no results are left outstanding on the connection after any call to an sp_cursor stored procedure, you can execute multiple Transact-SQL statements concurrently on a single connection, provided they are all executed with API server cursors.
Specifying API Server Cursors
Here is a summary of how API server cursors are used in the APIs:
- OLE DB
- Open a session object, open a command object, and specify the command text.
- Set rowset properties such as DBPROP_OTHERINSERT, DBPROP_OTHERUPDATEDELETE, DBPROP_OWNINSERT, DBPROP_OWNUDPATEDELETE to control cursor behaviors.
- Execute the command object.
- Fetch the rows in the result set using such methods as IRowset::GetNextRows, IRowsetLocate::GetRowsAt, IRowsetLocate::GetRowsAtBookmark, and IRowsetScroll::GetRowsAtRatio.
- Open a session object, open a command object, and specify the command text.
- ODBC
- Open a connection and call SQLAllocHandle to allocate statement handles.
- Call SQLSetStmtAttr to set the SQL_ATTR_CURSOR_TYPE, SQL_ATTR_CONCURRENCY, and SQL_ATTR_ROW_ARRAY_SIZE attributes. Alternatively, you can specify cursor behaviors by setting the attributes SQL_ATTR_CURSOR_SCROLLABLE and SQL_ATTR_CURSOR_SENSITIVITY.
- Execute a Transact-SQL statement using either SQLExecDirect or SQLPrepare and SQLExecute.
- Fetch rows or blocks of rows using SQLFetch or SQLFetchScroll.
- Open a connection and call SQLAllocHandle to allocate statement handles.
- ADO
- Define a Connection object and a Recordset object, and then execute the Open method on the Connection object.
- Execute the Open method on the Recordset object specifying a CursorType and/or a LockType parameter.
- Fetch rows using the Move, MoveFirst, MoveLast, MoveNext, and MovePrevious recordset methods.
- Define a Connection object and a Recordset object, and then execute the Open method on the Connection object.
- DB-Library
- The DB-Library core functions always use a default result set.
- Use the DB-Library Cursor Library functions without setting DBCLIENTCURSOR to use API server cursors.
- The DB-Library core functions always use a default result set.