SET SCROLLOPTION
The SET SCROLLOPTION statement sets the scrolling functionality and row membership for standard cursors.
Syntax
SET SCROLLOPTION {DYNAMIC | FORWARD | KEYSET}
Arguments
DYNAMIC
Specifies that row membership in the cursor is updated for every FETCH statement, and that the cursor scrolls forward and backward.
FORWARD (default)
Specifies that row membership in the cursor is updated for every FETCH statement, and that the cursor scrolls only forward.
KEYSET
Specifies fixing row membership in the cursor at OPEN time.
Remarks
After this statement is issued, it affects all subsequent cursor OPEN statements. This statement is ignored if you are using browse cursors.
Row membership is determined by the subset of rows defined by the SELECT statement criteria and available for retrieval by using FETCH statements. This includes the specific data rows available and what (if any) order those rows are in.
For KEYSET cursors, row membership is fixed when the OPEN statement is issued. After a KEYSET cursor has been opened, if additional rows that meet the SELECT statement criteria are added by another user, they are not visible within the opened cursor. You must close and reopen a KEYSET cursor to see the added rows.
For DYNAMIC and FORWARD cursors, row membership is dynamic. Each FETCH statement retrieves the most current data available. After a DYNAMIC or FORWARD cursor has been opened, if rows that meet the SELECT statement criteria are added by another user, they are potentially visible within the opened cursor.
When using the DYNAMIC or FORWARD scroll options, the SELECT statement used to open the cursor cannot contain a GROUP BY or HAVING clause or an ORDER BY clause without a unique index.
Examples
EXEC SQL SET SCROLLOPTION DYNAMIC;