Cursors in CLI applications

In CLI applications, you use a cursor to retrieve rows from a result set. A cursor is a moveable pointer that you use to select a row in the result table of an active query statement.

A cursor is opened when a dynamic SQL SELECT statement is successfully executed by SQLExecute() or SQLExecDirect(). There is typically a one-to-one correlation between application cursor operations and the operations performed by the CLI driver with the cursor.

Immediately after the successful execution, the cursor is positioned before the first row of the result set, and FETCH operations through calls to SQLFetch(), SQLFetchScroll(), or SQLExtendedFetch() will advance the cursor one row at a time through the result set. When the cursor has reached the end of the result set, the next fetch operation will return SQLCODE +100.

From the perspective of the CLI application, SQLFetch() returns SQL_NO_DATA_FOUND when the end of the result set is reached.

Types of cursors

There are two types of cursors supported by CLI:
non-scrollable
Forward-only non-scrollable cursors are the default cursor type used by the CLI driver. This cursor type is unidirectional and requires the least amount of resource utilization.
scrollable
There are three types of scrollable cursors supported by CLI:
static
This is a read-only cursor. When it is created, no rows can be added or removed, and no values in any rows will change. The cursor is not affected by other applications accessing the same data. The isolation level of the statement used to create the cursor determines how the rows of the cursor are locked, if at all.
keyset-driven
Unlike a static scrollable cursor, a keyset-driven scrollable cursor can detect and make changes to the underlying data. Keyset cursors are based on row keys. When a keyset-driven cursor is first opened, it stores the keys in a keyset for the life of the entire result set. The keyset is used to determine the order and set of rows that are included in the cursor. As the cursor scrolls through the result set, it uses the keys in this keyset to retrieve the most recent values in the database, which are not necessarily the values that existed when the cursor was first opened. For this reason, changes are not reflected until the application scrolls to the row.
There are various types of changes to the underlying data that a keyset-driven cursor might or might not reflect:
  • Changed values in existing rows. The cursor will reflect these types of changes. Because the cursor fetches a row from the database each time it is required, keyset-driven cursors always detect changes made by themselves and other cursors.
  • Deleted rows. The cursor will reflect these types of changes. If a selected row in the rowset is deleted after the keyset is generated, it will appear as a "hole" in the cursor. When the cursor goes to fetch the row again from the database, it will realize that the row is no longer there.
  • Added rows. The cursor will not reflect these types of changes. The set of rows is determined once, when the cursor is first opened. To see the inserted rows, the application must re-execute the query.
dynamic
Dynamic scrollable cursors can detect all changes (inserts, deletes, and updates) to the result set, and make insertions, deletions and updates to the result set. Unlike keyset-driven cursors, dynamic cursors:
  • detect rows inserted by other cursors
  • omit deleted rows from the result set (keyset-driven cursors recognize deleted rows as "holes" in the result set)
Note: A column with a LOB type, distinct type on a LOB type, A column with a LONG VARCHAR, LONG VARGRAPHIC, DATALINK, LOB, XML type, distinct type on any of these types, or structured type cannot be specified in the select-list of a scrollable cursor. CLI will downgrade the cursor type from scrollable to forward-only and return a CLI0005W (SQLSTATE 01S02) warning message.

Cursor attributes

The table 1 lists the default attributes for cursors in CLI.
Table 1. Default attributes for cursors in CLI
Cursor type Cursor sensitivity Cursor updatable Cursor concurrency Cursor scrollable
forward-onlya unspecified non-updatable read-only concurrency non-scrollable
static insensitive non-updatable read-only concurrency scrollable
keyset-driven sensitive updatable values concurrency scrollable
dynamicb sensitive updatable values concurrency scrollable
  • a Forward-only is the default behavior for a scrollable cursor without the FOR UPDATE clause. Specifying FOR UPDATE on a forward-only cursor creates an updatable, lock concurrency, non-scrollable cursor.
  • b Values concurrency is the default behavior, however, Db2® will also support lock concurrency, which will result with pessimistic locking.

Update of keyset-driven cursors

A keyset-driven cursor is an updatable cursor. The CLI driver appends the FOR UPDATE clause to the query, except when the query is issued as a SELECT ... FOR READ ONLY query, or if the FOR UPDATE clause already exists. The default keyset-driven cursor is a values concurrency cursor. A values concurrency cursor results in optimistic locking, where locks are not held until an update or delete is attempted. If lock concurrency has been explicitly asked for, then pessimistic locking will be used and locks will be held as soon as the row is read. This level of locking is only supported against Db2 on Linux®, UNIX and Windows servers. When an update or delete is attempted, the database server compares the previous values the application retrieved to the current values in the underlying table. If the values match, then the update or delete succeeds. If the values do not match, then the operation fails. If failure occurs, the application must query the values again and re-issue the update or delete if it is still applicable.

An application can update a keyset-driven cursor in two ways:
  • Issue an UPDATE WHERE CURRENT OF <cursor name> or DELETE WHERE CURRENT OF <cursor name> using SQLPrepare() with SQLExecute() or SQLExecDirect()
  • Use SQLSetPos() or SQLBulkOperations() to update, delete, or add a row to the result set.
    Note: Rows added to a result set through SQLSetPos() or SQLBulkOperations() are inserted into the table on the server, but are not added to the server's result set. Therefore, these rows are not updatable nor are they sensitive to changes made by other transactions. However, the inserted rows will appear to be part of the result set as they are cached on the client. Any triggers that apply to the inserted rows will appear to the application as if they have not been applied. To make the inserted rows updatable, sensitive, and to see the result of applicable triggers, the application must issue the query again to regenerate the result set.