>>--EXEC SQL--.-------------------.------->
+-FOR :host_integer-+
>-----.-------------.--FETCH---.-------------.-->
+-AT db_name--+ +---PREVIOUS--+
+---LAST------+
+---PRIOR-----+
+---FIRST-----+
+---NEXT------+
>-----cursor_name---.-------------------------------------.------->
+-USING DESCRIPTOR :sqlda_struct------+
| +--------------------------,-+|
| V ||
+-INTO--.-:hvar----------------------.+
+-:hvar:ivar-----------------+
+-:hvar-.-----------.-:ivar--+
+-INDICATOR-+
>--END EXEC--><
| :host_integer | A host variable that specifies the maximum number of host array elements processed. Must be declared as PIC S9(4) COMP-5 or PIC S9(9) COMP-5. You can also declare the host variable as PIC S9(4) COMP-3 or PIC S9(9) COMP-3.[1] |
| AT db_name | The name of a database that has been declared using DECLARE DATABASE. This clause is not required, and if omitted, the connection automatically switches to the connection associated with the DECLARE CURSOR statement if different than the current connection, but only for the duration of the statement. Provided for backward compatibility. |
| cursor_name | A previously declared and opened cursor. |
| :sqlda_struct | An SQLDA data structure previously populated by the DESCRIBE statement and containing output value addresses. This option is used only with a cursor declared by a prepared SELECT statement. (SELECT statements are prepared using the PREPARE statement.) The colon is optional to provide compatibility with other embedded SQL implementations. |
| :hvar | Identifies one or more host variables to receive the data. |
| :ivar | Specifies either of the following:
|
After execution, SQLERRD(3) contains the number of elements processed. For FETCH it is the number of rows actually fetched.
* Declare a cursor for a given SQL statement.
EXEC SQL DECLARE C1 CURSOR FOR
SELECT last_name, first_name FROM staff
END-EXEC
EXEC SQL OPEN C1 END-EXEC
* Fetch the current values from the cursor into the host variables
* and if everything goes ok, display the values of the host
* variables
PERFORM UNTIL SQLCODE NOT = ZERO
EXEC SQL
FETCH C1 INTO :lname,:fname
END-EXEC
IF SQLCODE NOT = ZERO AND SQLCODE NOT = 100
DISPLAY 'Error: Could not perform fetch'
DISPLAY SQLERRML
DISPLAY SQLERRMC
EXEC SQL DISCONNECT ALL END-EXEC
STOP RUN
END-IF
DISPLAY 'First name: 'fname
DISPLAY 'Last name : 'lname
DISPLAY SPACES
END-PERFORM
Comments:
By default, the FETCH statement retrieves the next row, but you can also specify the previous row or last row or prior row or first row. If there are no more rows to fetch SQLCODE is set to 100 and SQLSTATE is set to "02000".
An OPEN cursor_name statement must precede a FETCH statement, and the cursor must be open while FETCH runs. If you use PREVIOUS, LAST, PRIOR, FIRST or NEXT, you must also set the appropriate cursor options via the DECLARE CURSOR statement or the SET SCROLLOPTION and SET CONCURRENCY statements. Also, the data type of the host variable must be compatible with the data type of the corresponding database column.
If the number of columns is less than the number of host variables, the value of SQLWARN3 is set to W. If an error occurs, no further columns are processed. (Processed columns are not undone.)
Alternatively, the :hvar variable can specify a COBOL record that contains several fields, each corresponding to a column in the select list of the cursor declaration statement. To use this form, you must specify the DB2 option of the SQL Compiler directive. (Note that this will cause PREPARE INTO and DESCRIBE statements to be rejected by the COBOL compiler).
If ANSI92ENTRY is set, then attempting to fetch a null value will set SQLCODE to -19425 if there is no null indicator. If ANSI92ENTRY is not set, SQLCODE will be 0. In both cases, SQLSTATE will be 22002 and SQLWARN2 will be W.
If one of the host variables in the INTO clause is an array, they must all be arrays.