How to use cursors (ODBC)
To use cursors
- 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.
- Call SQLSetStmtAttr to set the rowset size by using the SQL_ATTR_ROW_ARRAY_SIZE attribute.
- Optionally, call SQLSetCursorName to set a cursor name if positioned updates will be done by using the WHERE CURRENT OF clause.
- Execute the SQL statement.
- 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.
- Call SQLNumResultCols to get the number of columns (C) in the rowset.
- Use column-wise binding.
Or
Use row-wise binding.
- Fetch rowsets from the cursor as desired.
- 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.
- If it returns SQL_SUCCESS, another result set is available.
- Optionally, call SQLFreeStmt with an fOption of SQL_UNBIND to release any bound column buffers.
- 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.