Fast Forward-Only Cursors (ODBC)

  • 아티클

When connected to an instance of SQL Server 2005, the SQL Native Client 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.

참고 항목

개념

Cursor Programming Details (ODBC)

도움말 및 정보

SQL Server 2005 지원 받기