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 cursor_name
into : host_variable
[[ indicator]: indicator_variable ]
[,: host_variable
[[ indicator]: indicator_variable ]...];
where there is one host_variable 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.
An indicator_variable is a 2-byte signed integer declared in a previous declare section. If a value retrieved from Adaptive Server is null, the runtime system sets the corresponding indicator variable to -1. Otherwise, the indicator is set to 0.
The data that the fetch statement retrieves depends on the cursor position. The cursor points to the current row. The fetch statement always returns the current row. The first fetch retrieves the first row and copies the values into the host variables indicated. Each fetch advances the cursor to the next result row.
Normally, you should place the fetch statement within a loop so that all values returned by the select statement can be assigned to host variables.
The following loop uses the whenever not found statement:
/* Initialize error-handling routines */
exec sql whenever sqlerror call err_handle();
exec sql whenever not found goto end_label;
for (;;)
{
exec sql fetch cursor_name
into :host_variable [, host_variable];
...
}
end_label:
This loop continues until all rows are returned or an error occurs. In either case, sqlcode or sqlstate, which the whenever statement checks after each fetch, indicates the reason for exiting the loop. The error-handling routines ensure that an action is performed when either condition arises, as described in Chapter 8, “Handling Errors.”
