Fetch

Valid in: ESQL

The Fetch statement retrieves the results of the select statement that is executed when a cursor is opened. When a cursor is opened, the cursor is positioned immediately before the first result row. The fetch statement advances the cursor to the first (or next) row and loads the values in that row into the specified variables. Each fetch statement advances the cursor one row.

There must be a one-to-one correspondence between variables specified in the into or using clause of fetch and expressions in the select clause of the declare cursor statement. If the number of variables does not match the number of expressions, the preprocessor generates a warning and, at runtime, the SQLCA variable sqlwarn3 is set to W.

The variables listed in the into clause can include structures that substitute for some or all of the variables. The structure is expanded by the preprocessor into the names of its individual variables; therefore, placing a structure name in the into clause is equivalent to enumerating all members of the structure in the order in which they were declared.

Syntax

The Fetch statement has the following formats:

Non-dynamic version:

EXEC SQL FETCH [fetch_orientation] [FROM] cursor_name
              INTO variable[:indicator_var] {, variable[:indicator_var]};

• fetch_orientation

  Specifies one of these orientations: NEXT, PRIOR, FIRST, LAST, ABSOLUTE n, and RELATIVE n (where n is a positive or negative integer or 0).

  The row retrieved is based upon the current position of the cursor in the result set.

  Default: NEXT

  Note: To retrieve long varchar columns, specify a datahandler clause in place of the host language variable. For details about data handler routines, see the chapter "Working with Embedded SQL" and the Embedded SQL Companion Guide. The syntax for the datahandler clause is as follows:

  DATAHANDLER(handler_routine ([handler_arg]))[:indicator_var]

Dynamic version:

EXEC SQL FETCH [FROM] cursor_name USING DESCRIPTOR descriptor_name;

The descriptor associated with the USING DESCRIPTOR clause must identify an SQLDA that contains type descriptions of one or more host language variables. Each element of the SQLDA is assigned the corresponding value in the current row of the cursor. For details, see the chapter "Working with Embedded SQL."

The variables listed in the INTO clause or within the descriptor must be type-compatible with the values being retrieved. If a result expression is nullable, the host language variable that receives that value must have an associated null indicator.

If the statement does not fetch a row--a condition that occurs after all rows in the set have been processed--the sqlcode of the SQLCA is set to 100 (condition not found) and no values are assigned to the variables.

The cursor identified by cursor_name must be an open cursor. Cursor_name can be either a string constant or a host language variable.

The statement must be terminated according to the rules of the host language.

Readonly Cursors and Performance

The performance of the FETCH statement is improved if the cursor associated with the statement is opened as a read-only cursor.

For read-only cursors, the DBMS Server prefetches rows to improve performance. To disable prefetching or specify the number of rows that are prefetched, use the SET_SQL(PREFETCHROWS) statement.

Permissions

All users are permitted to use this statement.

Related Statements

Delete Cursor

Open

Close

Select (interactive)

Update

Examples: Fetch

The following are Fetch statement examples:

1. Typical fetch, with associated cursor statements.

   exec sql begin declare section;
       name character_string(20);
       age integer;
   exec sql end declare section;
   
   exec sql declare cursor1 cursor for
       select ename, age
       from employee
       order by ename;
   
   ...
   
   exec sql open cursor1 for readonly;
   
   loop until no more rows
       exec sql fetch cursor1
           into :name, :age;
       print name, age;
   end loop;
   
   exec sql close cursor1;

   Assuming the structure:

   Emprec
       name character_string(20),
       age integer;

   the fetch in the above example can be written as:

   exec sql fetch cursor1
       into :emprec;

   The preprocessor interprets that statement as though it had been written:

   exec sql fetch cursor1
       into :emprec.name, :emprec.age;

2. Fetch, using an indicator variable.

   exec sql fetch cursor2 into :name,
    :salary:salary_ind;