How to use cursors (ODBC)

To use cursors

1. Call SQLSetStmtAttr to set the desired cursor attributes:

   Set the SQL_ATTR_CURSOR_TYPE and SQL_ATTR_CONCURRENCY attributes (this is the preferred option).

   Or

   Set the SQL_CURSOR_SCROLLABLE and SQL_CURSOR_SENSITIVITY attributes.

2. Call SQLSetStmtAttr to set the rowset size by using the SQL_ATTR_ROW_ARRAY_SIZE attribute.
3. Optionally, call SQLSetCursorName to set a cursor name if positioned updates will be done by using the WHERE CURRENT OF clause.
4. Execute the SQL statement.
5. Optionally, call SQLGetCursorName to get the cursor name if positioned updates will be done by using the WHERE CURRENT OF clause and a cursor name was not supplied with SQLSetCursorName in Step 3.
6. Call SQLNumResultCols to get the number of columns (C) in the rowset.
7. Use column-wise binding.

   Or

   Use row-wise binding.

8. Fetch rowsets from the cursor as desired.
9. Call SQLMoreResults to determine if another result set is available.
   • If it returns SQL_SUCCESS, another result set is available.
   • If it returns SQL_NO_DATA, no more result sets are available.
   • If it returns SQL_SUCCESS_WITH_INFO or SQL_ERROR, call SQLGetDiagRec to determine if the output from a PRINT or RAISERROR statement is available.

   If bound statement parameters are used for output parameters or the return value of a stored procedure, use the data now available in the bound parameter buffers.

   When bound parameters are used, each call to SQLExecute or SQLExecDirect will have executed the SQL statement S times, where S is the number of elements in the array of bound parameters. This means that there will be S sets of results to process, where each set of results comprises all of the result sets, output parameters, and return codes usually returned by a single execution of the SQL statement.

   Note that when a result set contains compute rows, each compute row is made available as a separate result set. These compute result sets are interspersed within the normal rows and break normal rows into multiple result sets.

10. Optionally, call SQLFreeStmt with an fOption of SQL_UNBIND to release any bound column buffers.
11. If another result set is available, go to Step 6.

In step 9, calling SQLMoreResults on a partially processed result set clears the remainder of the result set. Another way to clear a partially processed result set is to call SQLCloseCursor.

You can control the type of cursor used by either setting SQL_ATTR_CURSOR_TYPE and SQL_ATTR_CONCURRENCY, or by setting SQL_ATTR_CURSOR_SENSITIVITY and SQL_ATTR_CURSOR_SCROLLABLE. You should not mix the two methods of specifying cursor behavior, the Microsoft ODBC 3.0 Programmer’s Reference warns that this can prevent an ODBC driver from efficiently implementing the cursor.

See Also