Choosing ODBC cursor characteristics

ODBC functions that execute statements and manipulate result sets, use cursors to perform their tasks. Applications open a cursor implicitly whenever they execute a SQLExecute or SQLExecDirect function.

For applications that move through a result set only in a forward direction and do not update the result set, cursor behavior is relatively straightforward. By default, ODBC applications request this behavior. ODBC defines a read-only, forward-only cursor, and SQL Anywhere provides a cursor optimized for performance in this case.

For a simple example of a forward-only cursor, see Retrieving data.

For applications that need to scroll both forward and backward through a result set, such as many graphical user interface applications, cursor behavior is more complex. What does the application when it returns to a row that has been updated by some other application? ODBC defines a variety of scrollable cursors to allow you to build in the behavior that suits your application. SQL Anywhere provides a full set of cursors to match the ODBC scrollable cursor types.

You set the required ODBC cursor characteristics by calling the SQLSetStmtAttr function that defines statement attributes. You must call SQLSetStmtAttr before executing a statement that creates a result set.

You can use SQLSetStmtAttr to set many cursor characteristics. The characteristics that determine the cursor type that SQL Anywhere supplies include the following:

  • SQL_ATTR_CURSOR_SCROLLABLE   Set to SQL_SCROLLABLE for a scrollable cursor and SQL_NONSCROLLABLE for a forward-only cursor. SQL_NONSCROLLABLE is the default.

  • SQL_ATTR_CONCURRENCY   Set to one of the following values:

    • SQL_CONCUR_READ_ONLY   Disallow updates. SQL_CONCUR_READ_ONLY is the default.

    • SQL_CONCUR_LOCK   Use the lowest level of locking sufficient to ensure that the row can be updated.

    • SQL_CONCUR_ROWVER   Use optimistic concurrency control, comparing row versions such as SQLBase ROWID or Sybase TIMESTAMP.

    • SQL_CONCUR_VALUES   Use optimistic concurrency control, comparing values.

For more information, see [external link] SQLSetStmtAttr in the Microsoft ODBC Programmer's Reference.

Example

The following fragment requests a read-only, scrollable cursor:

SQLAllocHandle( SQL_HANDLE_STMT, dbc, &stmt );
SQLSetStmtAttr( stmt, SQL_ATTR_CURSOR_SCROLLABLE,
      SQL_SCROLLABLE, SQL_IS_UINTEGER );