ASA Programming Guide
  ODBC Programming
    Working with result sets

Choosing a cursor characteristics

ODBC functions that execute statements and manipulate result sets use cursors to carry out 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 Adaptive Server 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. Adaptive Server 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 Adaptive Server 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 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, 0 );