How to set cursor options (ODBC)

How to Install SQL Server 2000

How To

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.

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);

See Also

Constructing SQL Statements for Cursors

SQLGetStmtAttr

SQLSetStmtAttr