ASA Programming Guide
Using SQL in Applications
Any cursor, once opened, has an associated result set. The cursor is kept open for a length of time. During that time, the result set associated with the cursor may be changed, either through the cursor itself or, subject to isolation level requirements, by other transactions. Some cursors permit changes to the underlying data to be visible, while others do not reflect these changes. The different behavior of cursors with respect to changes to the underlying data is the sensitivity of the cursor.
Adaptive Server Anywhere provides cursors with a variety of sensitivity characteristics. This section describes what sensitivity is, and describes the sensitivity characteristics of cursors.
This section assumes that you have read What are cursors?.
Changes to the underlying data can affect the result set of a cursor in the following ways:
Membership The set of rows in the result set, as identified by their primary key values.
Order The order of the rows in the result set.
Value The values of the rows in the result set.
For example, consider the following simple table with employee information (emp_id is the primary key column):
| emp_id | emp_lname |
|---|---|
| 1 | Whitney |
| 2 | Cobb |
| 3 | Chin |
A cursor on the following query returns all results from the table in primary key order:
SELECT emp_id, emp_lname FROM employee ORDER BY emp_id
The membership of the result set could be changed by adding a new row or deleting a row. The values could be changed by changing one of the names in the table. The order could be changed by changing the primary key value of one of the employees.
Subject to isolation level requirements, the membership, order, and values of the result set of a cursor can be changed after the cursor is opened. Depending on the type of cursor in use, the result set as seen by the application may change to reflect these changes or may not.
Changes to the underlying data may be visible or invisible through the cursor. A visible change is a change that is reflected in the result set of the cursor. Changes to the underlying data that are not reflected in the result set seen by the cursor are invisible.
Cursor sensitivity overview
Cursor sensitivity example: a deleted row
Cursor sensitivity example: an updated row
Insensitive cursors
Sensitive cursors
Asensitive cursors
Value-sensitive cursors
Cursor sensitivity and performance
Cursor sensitivity and isolation levels