Fast Forward-Only Cursors (ODBC)

When connected to Microsoft® SQL Server™ version 7.0, 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:

1. Set SQL_SOPT_SS_CURSOR_OPTIONS to SQL_CO_FFO_AF.
2. Set SQL_ATTR_ROW_ARRAY_SIZE to n + 1.
3. Bind the result columns to arrays of n + 1 elements (to be safe if n + 1 rows are actually fetched).
4. Open the cursor with either SQLExecDirect or SQLExecute.
5. 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.

See Also

SQLSetStmtAttr