The FETCH on a scrollable cursor allows specification of a
direction. If there is no more data in the specified fetch
direction this causes the NOT FOUND exception to be raised, as with
the forward-only cursors. In addition to that if the row on which
the cursor is about to position has been deleted and the isolation
level & cursor type allows detecting that, then the exception
SQLSTATE 'HY109' (Row deleted) is raised.
Positioning on a bookmark is done the following way:
A bookmark value should be retrieved using the bookmark() function . The value returned by that
function can be stored, copied and retrieved. This value can also
survive a cursor close and reopen, even between transactions. How
the cursor will behave if a bookmark from a cursor with different
select statement or scroll type is used for positioning is
undefined and should be avoided. On some occasions it may signal an
error, on others it will position on a wrong or non-existing row.
As a general rule bookmark values should be used only on the cursor
from which they are generated.
The cursor should be in opened state. Now a FETCH .. BOOKMARK
bm_value INTO ... can be issued with the bookmark variable.
Bookmarks can serve for persisting the cursor position in an VSP
context. One can imagine a VSP page which on it's first go will
execute a cursor and will show the first so-many rows. Then it can
retrieve the bookmark value of the last displayed row, persist it
somehow (for example as an HTTP session variable), then close the
cursor and exit. On each subsequent hit it will open again the same
cursor, position on the bookmark persisted and return the next,
previous, first or last so-many rows.