Micro Focus Visual COBOL 2.3 Update 2 for Eclipse (Windows) > Programming > Data Access > Database Access > Reference > OpenESQL > Embedded SQL Statements > FETCH

FETCH

Retrieves a row from the cursor's results set and writes the values of the columns in that row to the corresponding host variables (or to addresses specified in the SQLDA data structure).

Syntax:

>>--EXEC SQL--.-------------------.--------------->
              +-FOR :host_integer-+
             
 >--FETCH-------.-------------.------cursor_name-->
                +---PREVIOUS--+
                +---LAST------+
                +---PRIOR-----+
                +---FIRST-----+
                +---NEXT------+ 

 >--.-------------------------------------.------->
    +-USING DESCRIPTOR :sqlda_struct------+
    |       +--------------------------,-+|
    |       V                            ||  
    +-INTO--.-:hvar----------------------.+
            +-:hvar:ivar-----------------+
            +-:hvar-.-----------.-:ivar--+
                    +-INDICATOR-+

 >--END EXEC--><

Parameters:

:`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.
`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: One or more host variables, each separated by a comma One or more host variable+indicator variable combinations, each combination separated by a comma

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 :hvarvariable 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.

Comments:

After execution, SQLERRD(3) contains the number of elements processed. For FETCH it is the number of rows actually fetched.

Example:

* 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