Sybase Adaptive Server Anywhere - Page 507

Chapter 9 SQL Statements
489
NO SCROLL cursors A cursor declared NO SCROLL is restricted to
FETCH NEXT and FETCH RELATIVE 0 seek operations.
DYNAMIC SCROLL cursors DYNAMIC SCROLL is the default cursor
type. DYNAMIC SCROLL cursors can use all formats of the FETCH
statement.
SCROLL cursors A cursor declared SCROLL can use all formats of the
FETCH statement.
SCROLL cursors behave differently from DYNAMIC SCROLL cursors
when a row in the cursor is modified or deleted after the first time the row is
read. SCROLL cursors have more predictable behavior when changes
happen.
Adaptive Server Anywhere maintains more information about SCROLL
cursors than DYNAMIC SCROLL cursors; thus, DYNAMIC SCROLL
cursors are more efficient and should be used unless the consistent behavior
of SCROLL cursors is required. There is no extra overhead in Adaptive
Server Anywhere for DYNAMIC SCROLL cursors versus NO SCROLL
cursors.
Each row fetched in a SCROLL cursor is remembered. If one of these rows
is deleted, either by your program or by another program in a multiuser
environment, it creates a hole in the cursor. If you fetch the row at this hole
with a SCROLL cursor, Adaptive Server Anywhere returns the error
SQLE_NO_CURRENT_ROW indicating that the row has been deleted, and
leaves the cursor positioned on the hole. (DYNAMIC SCROLL cursors skip
the hole and retrieve the next row.)
This allows your application to remember row positions within a cursor and
be assured that these positions will not change. For example, an application
could remember that Cobb is the second row in the cursor for SELECT *
FROM employee. If the first employee (Whitney) is deleted while the
SCROLL cursor is still open, FETCH ABSOLUTE 2 will still position on
Cobb while FETCH ABSOLUTE 1 will return
SQLE_NO_CURRENT_ROW. Similarly, if the cursor is on Cobb, FETCH
PREVIOUS will return SQLE_NO_CURRENT_ROW.
A fetch on a SCROLL cursor returns the warning
SQLE_ROW_UPDATED_WARNING if the row has changed since it was
last read. The warning only happens once; fetching the same row a third time
does not produce the warning. Similarly, a positioned UPDATE or DELETE
statement on a row that has been modified since it was last fetched returns
the error SQLE_ROW_UPDATED_SINCE_READ and cancels the
statement. An application must FETCH the row again before the UPDATE
or DELETE will be permitted.