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 CLICursor 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® for Linux, UNIX, and Windows 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.