This example uses a simple query to illustrate how different cursor types respond to a row in the result set being updated in such a way that the order of the result set is changed.
Consider the following sequence of events:
An application opens a cursor on the following query against the sample database.
SELECT EmployeeID, Surname FROM Employees;
EmployeeID | Surname |
---|---|
102 | Whitney |
105 | Cobb |
160 | Breault |
... | ... |
The application fetches the first row through the cursor (102).
The application fetches the next row through the cursor (105).
A separate transaction updates the employee ID of employee 102 (Whitney) to 165 and commits the change.
The results of the cursor actions in this situation depend on the cursor sensitivity:
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). |
Action | Result |
---|---|
Fetch previous row | Returns SQLCODE 100. The membership of the result set has changed so that 105 is now the first row. The cursor is moved to the position before the first row. |
Fetch the first row (absolute fetch) | Returns row 105. |
Fetch the second row (absolute fetch) | Returns row 160. |
In addition, a fetch on a sensitive cursor returns a SQLE_ROW_UPDATED_WARNING warning if the row has changed since the last reading. The warning is given only once. Subsequent fetches of the same row do not produce the warning.
Similarly, a positioned update or delete through the cursor on a row since it was last fetched returns the SQLE_ROW_UPDATED_SINCE_READ error. An application must fetch the row again for an update or delete on a sensitive cursor to work.
An update to any column causes the warning/error, even if the column is not referenced by the cursor. For example, a cursor on a query returning Surname would report the update even if only the Salary column was modified.
Action | Result |
---|---|
Fetch previous row | Returns SQLCODE 100. The membership of the result set has changed so that 105 is now the first row: The cursor is positioned on the hole: it is before row 105. |
Fetch the first row (absolute fetch) | Returns SQLCODE -197. The membership of the result set has changed so that 105 is now the first row: The cursor is positioned on the hole: it is before row 105. |
Fetch the second row (absolute fetch) | Returns row 105. |