Copies data values from the current cursor row into host variables or a dynamic descriptor.
exec sql [at connection_name] fetch [rebind | norebind] cursor_name
into {:host_variable [[indicator]:indicator_variable]
[,:host_variable
[[indicator]:indicator_variable]]… |
descriptor descriptor_name |
sql descriptor descriptor_name};
Specifies whether host variables require rebinding for this fetch statement. The rebind clause overrides precompiler options that control rebinding.
The name of the cursor. The name is defined in a preceding declare cursor statement.
A host language variable defined in a declare section.
A 2-byte host variable declared in a previous declare section. If the value for the associated variable is null, fetch sets the indicator variable to -1. If truncation occurs, fetch sets the indicator variable to the actual length of the result column. Otherwise, it sets the indicator variable to 0.
Identifies descriptor_name as a SQLDA structure.
Identifies descriptor_name as a SQL descriptor.
The name of the dynamic descriptor that will hold the result set.
exec sql begin declare section;
CS_CHAR title_id[6];
CS_CHAR title[80];
CS_CHAR type[12];
CS_SMALLINT i_title;
CS_SMALLINT i_type;
exec sql end declare section;
exec sql declare title_list cursor for
select type, title_id, title from titles
order by type;
exec sql open title_list
while (sqlca.sqlcode != 100) {
exec sql fetch title_list into
:type :i_type, :title_id, :title :i_title;
if (i_type != -1) {
printf("Type: %s\n", type);
}
else {
printf("Type: undecided\n");
}
printf("Title id: %s\n", title_id);
if (i_title <> -1) {
print "Title: ", title;
}
else {
print "Title: undecided";
}
}
exec sql close title_list;
The fetch statement can be used both with static cursors and with cursors in dynamic SQL.
The open statement must execute before the fetch statement executes.
The first fetch on an open cursor returns the first row or group of rows from the cursor’s result table. Each subsequent fetch returns the next row or group of rows.
You can fetch multiple rows into an array.
The “current row” is the row most recently fetched. To update or delete it, use the where current of cursor_name clause with the update or delete statement. These statements are not valid until after a row has been fetched.
After all rows have been fetched from the cursor, calling fetch sets SQLCODE to 100. If the select furnishes no results on execution, SQLCODE is set to 100 on the first fetch.
There must be one—and only one—host_variable for each column of the result set.
When neither the rebind nor the norebind is specified, the binding behavior is determined by the precompiler option -b. See “Guidelines for using persistent binding” for information on persistent binds and the Open Client/Server Programmer’s Supplement for your platform for details on precompiler options.
An indicator_variable must be provided for a host_variable that can receive a null value. A runtime error occurs when a null value is fetched for a host variable that has no indicator variable.
When possible, Client-Library converts the datatype of a result column to the datatype of the corresponding host variable. If Client-Library cannot convert a datatype, it issues an error message. If conversion is not possible, an error occurs.
allocate descriptor, close, declare, delete (positioned cursor), open, prepare, update
