Set Cursor Options (ODBC)
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
To set cursor options, Call SQLSetStmtAttr to set or SQLGetStmtAttr to get the statement options that control cursor behavior.
| Attribute | Specifies |
|---|---|
| SQL_ATTR_CURSOR_TYPE | Cursor type of forward-only, static, dynamic, or keyset-driven |
| SQL_ATTR_CONCURRENCY | Concurrency control option of read-only, locking, optimistic using timestamps, or optimistic using values |
| SQL_ATTR_ROW_ARRAY_SIZE | Number of rows retrieved in each fetch |
| SQL_ATTR_CURSOR_SENSITIVITY | Cursor that does or does not show updates to cursor rows made by other connections |
| SQL_ATTR_CURSOR_SCROLLABLE | Cursor that can be scrolled forward and backward |
The default values for these attributes (forward-only, read-only, rowset size of 1) do not use server cursors. To use server cursors, at least one of these attributes must be set to a value other than the default, and the statement being executed must be a single SELECT statement or a stored procedure that contains a single SELECT statement. When using server cursors, SELECT statements cannot use clauses not supported by server cursors: COMPUTE, COMPUTE BY, FOR BROWSE, and INTO.
You can control the type of cursor used either by 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.
Examples
A. Set a dynamic cursor
The following sample allocates a statement handle, sets a dynamic cursor type with row versioning optimistic concurrency, and then executes a SELECT.
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);
retcode = SQLSetStmtAttr(hstmt1, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_DYNAMIC, SQL_IS_INTEGER);
retcode = SQLSetStmtAttr(hstmt1, SQL_ATTR_CONCURRENCY, SQLPOINTER)SQL_CONCUR_ROWVER, SQL_IS_INTEGER);
retcode = SQLExecDirect(hstmt1, SELECT au_lname FROM authors", SQL_NTS);
B. Set a scrollable, sensitive cursor
The following sample allocates a statement handle, sets a scrollable, sensitive cursor, and then executes a SELECT
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);
// Set the cursor options and execute the statement.
retcode = SQLSetStmtAttr(hstmt1, SQL_ATTR_CURSOR_SCROLLABLE, SQLPOINTER)SQL_SCROLLABLE, SQL_IS_INTEGER);
retcode = SQLSetStmtAttr(hstmt1, SQL_ATTR_CURSOR_SENSITIVITY, SQLPOINTER)SQL_INSENSITIVE, SQL_IS_INTEGER);
retcode = SQLExecDirect(hstmt1, select au_lname from authors", SQL_NTS);