How to set cursor options (ODBC)

To set cursor options

Call SQLSetStmtAttr to set or SQLGetStmtAttr to get the statement options that control cursor behavior.

Foption	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. The Microsoft ODBC 3.0 Programmer’s Reference warns that this can prevent an ODBC driver from efficiently implementing the cursor.

Examples

A. Allocate a statement handle, set a dynamic cursor type with row versioning optimistic concurrency, and then execute 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. Allocate a statement handle, set a scrollable, sensitive cursor, and then execute 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);

Constructing SQL Statements for Cursors	SQLSetStmtAttr
SQLGetStmtAttr