ASA Programming Guide
  Using SQL in Applications
    Adaptive Server Anywhere cursors

Cursor sensitivity example: a deleted row

This example uses a simple query to illustrate how different cursors respond to a row in the result set being deleted.

Consider the following sequence of events:

1. An application opens a cursor on the following query against the sample database.

   SELECT emp_id, emp_lname
   FROM employee
   ORDER BY emp_id

   emp_id	emp_lname
   102	Whitney
   105	Cobb
   160	Breault
   ...	...

2. The application fetches the first row through the cursor (102).

3. The application fetches the next row through the cursor (105).

4. A separate transaction deletes employee 102 (Whitney) and commits the change.

The results of cursor actions in this situation depend on the cursor sensitivity:

• Insensitive cursors    The DELETE is not reflected in either the membership or values of the results as seen through the cursor:

  Action	Result
  Fetch previous row	Returns the original copy of the row (102).
  Fetch the first row (absolute fetch)	Returns the original copy of the row (102).
  Fetch the second row (absolute fetch)	Returns the unchanged row (105).

• Sensitive cursors    The membership of the result set has changed so that row 105 is now the first row in the result set:

  Action	Result
  Fetch previous row	Returns Row Not Found error. There is no previous row.
  Fetch the first row (absolute fetch)	Returns row 105.
  Fetch the second row (absolute fetch)	Returns row 160.

• Value-sensitive cursors    The membership of the result set is fixed, and so row 105 is still the second row of the result set. The DELETE is reflected in the values of the cursor, and creates an effective "hole" in the result set.

  Action	Result
  Fetch previous row	Returns No current row of cursor. There is a hole in the cursor where the first row used to be.
  Fetch the first row (absolute fetch)	Returns No current row of cursor. There is a hole in the cursor where the first row used to be.
  Fetch the second row (absolute fetch)	Returns row 105.

• Asensitive cursors    The membership and values of the result set are indeterminate with respect to the changes. The response to a fetch of the previous row, the first row, or the second row depends on the particular optimization method for the query, whether that method involved the formation of a work table, and whether the row being fetched was prefetched from the client.

  The benefit of asensitive cursors is that for many applications, sensitivity is unimportant. In particular, if you are using a forward-only, read-only cursor, no underlying changes are seen. Also, if you are running at a high isolation level, underlying changes are disallowed.