SQL Reference

FETCH

The FETCH statement positions a cursor on the next row of its result table and assigns the values of that row to host variables.

Invocation

Although an interactive SQL facility might provide an interface that gives the appearance of interactive execution, this statement can only be embedded within an application program. It is an executable statement that cannot be dynamically prepared.

Authorization

See DECLARE CURSOR for an explanation of the authorization required to use a cursor.

Syntax

>>-FETCH--+-------+---cursor-name------------------------------->
          '-FROM--'
 
              .-,----------------.
              V                  |
>-----+-INTO-----host-variable---+---------+-------------------><
      '-USING DESCRIPTOR--descriptor-name--'
 

Description

cursor-name
Identifies the cursor to be used in the fetch operation. The cursor-name must identify a declared cursor as explained in DECLARE CURSOR. The DECLARE CURSOR statement must precede the FETCH statement in the source program. When the FETCH statement is executed, the cursor must be in the open state.

If the cursor is currently positioned on or after the last row of the result table:

If the cursor is currently positioned before a row, it will be repositioned on that row, and values will be assigned to host variables as specified by INTO or USING.

If the cursor is currently positioned on a row other than the last row, it will be repositioned on the next row and values of that row will be assigned to host variables as specified by INTO or USING.

INTO host-variable, ...
Identifies one or more host variables that must be described in accordance with the rules for declaring host variables. The first value in the result row is assigned to the first host variable in the list, the second value to the second host variable, and so on. For LOB values in the select-list, the target can be a regular host variable (if it is large enough), a locator variable, or a file-reference variable.

USING DESCRIPTOR descriptor-name
Identifies an SQLDA that must contain a valid description of zero or more host variables.

Before the FETCH statement is processed, the user must set the following fields in the SQLDA:

The SQLDA must have enough storage to contain all SQLVAR occurrences. Therefore, the value in SQLDABC must be greater than or equal to 16 + SQLN*(N), where N is the length of an SQLVAR occurrence.

If LOB or structured type result columns need to be accommodated, there must be two SQLVAR entries for every select-list item (or column of the result table). See Effect of DESCRIBE on the SQLDA, which discusses SQLDOUBLED, LOB , and structured type columns.

SQLD must be set to a value greater than or equal to zero and less than or equal to SQLN. For more information, see Appendix C, SQL Descriptor Area (SQLDA).

The nth variable identified by the INTO clause or described in the SQLDA corresponds to the nth column of the result table of the cursor. The data type of each variable must be compatible with its corresponding column.

Each assignment to a variable is made according to the rules described in "Language Elements". If the number of variables is less than the number of values in the row, the SQLWARN3 field of the SQLDA is set to 'W'. Note that there is no warning if there are more variables than the number of result columns. If an assignment error occurs, the value is not assigned to the variable, and no more values are assigned to variables. Any values that have already been assigned to variables remain assigned.

Notes

Examples

Example 1:  In this C example, the FETCH statement fetches the results of the SELECT statement into the program variables dnum, dname, and mnum. When no more rows remain to be fetched, the not found condition is returned.

   EXEC SQL DECLARE C1 CURSOR FOR
     SELECT DEPTNO, DEPTNAME, MGRNO FROM TDEPT
       WHERE ADMRDEPT = 'A00';
 
   EXEC SQL OPEN C1;
 
   while (SQLCODE==0) {
     EXEC SQL FETCH C1 INTO :dnum, :dname, :mnum;
   }
 
   EXEC SQL CLOSE C1;

Example 2:  This FETCH statement uses an SQLDA.

   FETCH CURS USING DESCRIPTOR :sqlda3


[ Top of Page | Previous Page | Next Page ]