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} end-exec
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 is to hold a result set.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 TITLE-ID PIC X(6).
01 BOOK-NAME PIC X(80).
01 BOOK-TYPE PIC X(12).
01 I-TITLE PIC S9(9).
01 I-TYPE PIC S9(9).
EXEC SQL END DECLARE SECTION END-EXEC.
...
EXEC SQL DECLARE title_list CURSOR FOR
SELECT type, title_id, title FROM titles
ORDER BY type END-EXEC.
EXEC SQL OPEN title_list END-EXEC.
PERFORM FETCH-LOOP UNTIL SQLCODE = 100.
EXEC SQL CLOSE title_list END-EXEC.
...
FETCH-LOOP.
EXEC SQL FETCH title_list INTO
:BOOK-TYPE :I-TYPE,
:TITLE-ID,
:BOOK-NAME :I-TITLE END-EXEC
* Check the indicator value - if not null display the value, else
* display UNDECIDED.
IF I-TYPE <> -1
DISPLAY "TYPE : ", BOOK-TYPE
ELSE
DISPLAY "TYPE : UNDECIDED"
END-IF
DISPLAY "TITLE ID : ", TITLE-ID
IF I-TITLE <> -1
DISPLAY "TITLE : ", BOOK-NAME
ELSE
DISPLAY "TITLE : UNDECIDED"
END-IF.
END-FETCH-LOOP.
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 statement 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 option is specified, the binding behavior is determined by the precompiler option -b. See the Open Client/Server Programmer’s Supplement 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
