Fast Forward-Only Cursors (ODBC)
When connected to an instance of Microsoft® SQL Server™ 2000, the SQL Server ODBC driver supports performance optimizations for forward-only, read-only cursors. Fast forward-only cursors are implemented internally by the driver and server in a manner very similar to default result sets. Besides having high performance, fast forward-only cursors also have these characteristics:
- SQLGetData is not supported. The result set columns must be bound to program variables.
- The server automatically closes the cursor when the end of the cursor is detected. The application must still call SQLCloseCursor or SQLFreeStmt(SQL_CLOSE), but the driver does not have to send the close request to the server. This saves a roundtrip across the network to the server.
If a result set contains a text, ntext, or image column, a fast forward-only cursor is implicitly converted to a dynamic cursor and SQL_SUCCESS_WITH_INFO is returned to the application. SQLGetData is enabled for the dynamic cursor.
The application requests fast forward-only cursors using the driver-specific statement attribute SQL_SOPT_SS_CURSOR_OPTIONS. When set to SQL_CO_FFO, fast forward-only cursors are enabled without autofetch. When set to SQL_CO_FFO_AF, the autofetch option is also enabled. For more information about autofetch, see Using Autofetch with ODBC Cursors.
Fast forward-only cursors with autofetch can be used to retrieve a small result set with only one roundtrip to the server. In these steps, n is the number of rows to be returned:
- Set SQL_SOPT_SS_CURSOR_OPTIONS to SQL_CO_FFO_AF.
- Set SQL_ATTR_ROW_ARRAY_SIZE to n + 1.
- Bind the result columns to arrays of n + 1 elements (to be safe if n + 1 rows are actually fetched).
- Open the cursor with either SQLExecDirect or SQLExecute.
- If the return status is SQL_SUCCESS, then call SQLFreeStmt or SQLCloseCursor to close the cursor. All data for the rows will be in the bound program variables.
With these steps, the SQLExecDirect or SQLExecute sends a cursor open request with the autofetch option enabled. On that single request from the client, the server:
- Opens the cursor.
- Builds the result set and sends the rows to the client.
- Because the rowset size was set to 1 more than the number of rows in the result set, the server detects the end of the cursor and closes the cursor.