Use a fetch statement to retrieve data through a cursor and assign it to host variables. The syntax for the fetch statement is:
exec sql [at connect_name] fetch [fetch orientation]cursor_name
into : host_variable
[[ indicator]: indicator_variable ]
[,: host_variable
[[ indicator]: indicator_variable ]...];
where one host_variable exists for each column in the result rows.
Prefix each host variable with a colon, and separate it from the next host variable with a comma. The host variables listed in the fetch statement must correspond to Adaptive Server values that the select statement retrieves. Thus, the number of variables must match the number of returned values, they must be in the same order, and they must have compatible datatypes.
The fetch orientation specifies the fetch direction of the row to be fetched, if a cursor is scrollable. The options are: NEXT, PRIOR, FIRST, LAST, ABSOLUTE fetch_offset and RELATIVE fetch_offset. If fetch orientation is not specified, next is default. If fetch orientation is specified, the cursor must be scrollable.
The data that the fetch statement retrieves depends on the cursor position. The fetch statement typically retrieves single or multiple rows from the cursor result set, depending on the ROW_COUNT specification at cursor open time. If a cursor is not scrollable, fetch retrieves the next row in the result set. If a cursor is scrollable, commands in the fetch statement specify the row position to be fetched.
To declare a scrollable cursor and fetch rows at random, specify the scroll sensitivity and scrollability in the declare cursor, then specify the fetch orientation at fetch time. The following example demonstrates declaring an insensitive scrollable cursor and fetching rows at random:
exec sql declare c1 insensitive scroll cursor for
select title_id, royalty, ytd_sales from authors
where royalty < 25;
exec sql open c1;
In this example, scroll and insensitive are specified in the declare cursor. A fetch orientation can be specified at fetch time to indicate which row is required from the result set.
Once a cursor has been declared as scrollable and opened, a FETCH orientation can be specified at fetch time to indicate which row is wanted from the result set.
The following fetch example fetches the specified columns of the first row from the result set:
exec sql fetch first from c1 into :title,:roy,:sale;
The following fetch example fetches the specified columns of the previous row from the result set:
exec sql fetch prior from c1 into :title,:roy,:sale;
The following fetch example fetches the specified columns of row twenty from the result set:
exec sql fetch absolute 20 from c1 into :title,:roy,:sale;
Use sqlcode or sqlstate to determine if fetch statements return valid rows. For scrollable cursors, it is possible to fetch 0 rows if the cursor is positioned outside of result set boundaries, for example, before the first row or after the last row. In these circumstances, fetching 0 rows is not an error.
