Scrollable cursor characteristics in Db2 ODBC

In Db2 ODBC, scrollable cursors are defined by whether they are static or dynamic. Scrollable cursors are also defined by their sensitivity, and their concurrency.

Static or dynamic cursors

Scrollable cursors can be static or dynamic. Static and dynamic cursors differ in their ability to detect updates, deletes, and inserts into the result set. The definitions of static and dynamic cursors are:
Static
A read-only cursor. After the cursor is opened, it does not detect any inserts, deletes or updates that are made by its application, or by any other application.
Dynamic
A cursor that is sensitive to all inserts, deletes, and updates to the result set that occur after the cursor is opened. A dynamic cursor can insert into, delete from, or update the result set.

How to set the characteristics of a Db2 ODBC cursor

Before executing a query, an application can specify the cursor type by calling SQLSetStmtAttr(), with the statement attribute SQL_ATTR_CURSOR_TYPE. The default cursor type is forward-only.

An application can specify the characteristics of a cursor rather than specifying the cursor type. The application does this by setting the following statement attributes through SQLSetStmtAttr():
  • SQL_ATTR_CURSOR_SCROLLABLE
  • SQL_ATTR_CURSOR_SENSITIVITY

The ODBC driver selects the cursor type that most efficiently provides the characteristics that the application requests.

Whenever an application sets any of the following statement attributes, the ODBC driver changes the other statement attributes in this set, to keep the behavior consistent:
  • SQL_ATTR_CONCURRENCY
  • SQL_ATTR_CURSOR_SCROLLABLE
  • SQL_TTRCURSOR_SENSITIVITY
  • SQL_ATTR_CURSOR_TYPE

The following table lists the default attributes for each cursor type in Db2 ODBC.

Table 1. Cursor attributes for each cursor type
Cursor type Cursor sensitivity Cursor concurrency Cursor scrollability
Forward-only Unspecified Read-only concurrency Not scrollable
Static Insensitive Read-only concurrency Scrollable
Dynamic Sensitive Lock concurrency Scrollable

How to determine which characteristics are supported

Not all database servers support all types of scrollable cursors. Therefore, before you can use a scrollable cursor, you must determine whether scrollable cursors are supported.

To determine the types of scrollable cursors that are supported by the ODBC driver and the data source, and the capabilities that are supported for each scrollable cursor type, call SQLGetInfo(). Specify the following InfoType values:
  • SQL_CURSOR_SENSITIVITY
  • SQL_SCROLL_OPTIONS
  • SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES1
  • SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES2
  • SQL_KEYSET_CURSOR_ATTRIBUTES1
  • SQL_KEYSET_CURSOR_ATTRIBUTES2
  • SQL_DYNAMIC_CURSOR_ATTRIBUTES1
  • SQL_DYNAMIC_CURSOR_ATTRIBUTES2