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:

• SQLCODE is set to +100, and SQLSTATE is set to '02000'.
• The cursor is positioned after the last row.
• Values are not assigned to host variables.

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:

• SQLN to indicate the number of SQLVAR occurrences provided in the SQLDA.
• SQLDABC to indicate the number of bytes of storage allocated for the SQLDA.
• SQLD to indicate the number of variables used in the SQLDA when processing the statement.
• SQLVAR occurrences to indicate the attributes of the variables.

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

• An open cursor has three possible positions:
  • Before a row
  • On a row
  • After the last row.
• If a cursor is on a row, that row is called the current row of the cursor. A cursor referenced in an UPDATE or DELETE statement must be positioned on a row. A cursor can only be on a row as a result of a FETCH statement.
• When retrieving into LOB locators in situations where it is not necessary to retain the locator across FETCH statements, it is good practice to issue a FREE LOCATOR statement before issuing the next FETCH statement, as locator resources are limited.
• It is possible for an error to occur that makes the state of the cursor unpredictable.
• It is possible that a warning may not be returned on a FETCH. It is also possible that the returned warning applies to a previously fetched row. This occurs as a result of optimizations such as the use of system temporary tables or pushdown operators (see Administration Guide).
• Statement caching affects the behavior of an EXECUTE IMMEDIATE statement. See the Notes for information.
• DB2 CLI supports additional fetching capabilities. For instance when a cursor's result table is read-only, the SQLFetchScroll() function can be used to position the cursor at any spot within that result table.

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