FETCH statement [ESQL] [SP]

Positions, or re-positions, a cursor to a specific row, and then copies expression values from that row into variables accessible from within the stored procedure or application.

Syntax

Stored procedures

FETCH [ cursor-position ] cursor-name
INTO variable-list [ FOR UPDATE ]

Embedded SQL

FETCH [ cursor-position ] cursor-name
[ INTO { hostvar-list } | USING [ SQL ] DESCRIPTOR sqlda-name ]
[ PURGE ]
[ BLOCK n ]
[ FOR UPDATE ]
[ ARRAY fetch-count ]

cursor-position :
    NEXT | PRIOR | FIRST | LAST
| { ABSOLUTE | RELATIVE } row-count

row-count : number | hostvar

cursor-name : identifier | hostvar

hostvar-list : may contain indicator variables

variable-list :  stored procedure variables

sqlda-name :  identifier

fetch-count :  integer | hostvar

Parameters

INTO clause
The INTO clause is optional. If it is not specified, the FETCH statement positions the cursor only. The hostvar-list is for Embedded SQL use only.

Note If variable-list only contains one item, and there are multiple items in the result set of the cursor, then variable-list is considered a row variable and the returned column values are assigned to the fields of the row variable in order.
cursor position
An optional positional parameter allows the cursor to be moved before a row is fetched. If not specified, NEXT is assumed. If the fetch includes a positioning parameter and the position is outside the allowable cursor positions, the SQLE_NOTFOUND warning is issued and the SQLCOUNT field indicates the offset from a valid position.

The OPEN statement initially positions the cursor before the first row.
NEXT clause
Next is the default positioning, and causes the cursor to advance one row before the row is fetched.
PRIOR clause
Causes the cursor to back up one row before fetching.
RELATIVE clause
RELATIVE positioning is used to move the cursor by a specified number of rows in either direction before fetching. A positive number indicates moving forward and a negative number indicates moving backward. So, a NEXT is equivalent to RELATIVE 1 and PRIOR is equivalent to RELATIVE -1. RELATIVE 0 retrieves the same row as the last fetch statement on this cursor.
ABSOLUTE clause
The ABSOLUTE positioning parameter is used to go to a particular row. A zero indicates the position before the first row.

A one (1) indicates the first row, and so on. Negative numbers are used to specify an absolute position from the end of the cursor. A negative one (-1) indicates the last row of the cursor.
FIRST clause
A short form for ABSOLUTE 1.
LAST clause
A short form for ABSOLUTE -1.

Note
Inserts and some updates to DYNAMIC SCROLL cursors can cause problems with cursor positioning. The database server does not put inserted rows at a predictable position within a cursor unless there is an ORDER BY clause on the SELECT statement. Sometimes the inserted row does not appear until the cursor is closed and opened again.

This behavior occurs if a temporary table had to be created to open the cursor.

The UPDATE statement can cause a row to move in the cursor. This will happen if the cursor has an ORDER BY that uses an existing index (a temporary table is not created).
BLOCK clause
Rows may be fetched by the client application more than one at a time. This is referred to as block fetching, prefetching, or multi-row fetching. The first fetch causes several rows to be sent back from the database server. The client buffers these rows, and subsequent fetches are retrieved from these buffers without a new request to the database server.

The BLOCK clause is for use in Embedded SQL only. It gives the client and server a hint about how many rows may be fetched by the application. The special value of 0 means the request is sent to the database server and a single row is returned (no row blocking). The BLOCK clause will reduce the number of rows included in the next prefetch to the BLOCK value. To increase the number of rows prefetched, use the PrefetchRows connection parameter.

If you do not specify a BLOCK clause, the value specified on OPEN is used.

FETCH RELATIVE 0 always re-fetches the row.

If prefetch is disabled for the cursor, the BLOCK clause is ignored and rows are fetched one at a time. If ARRAY is also specified, then the number of rows specified by ARRAY are fetched.
PURGE clause
The PURGE clause is for use in Embedded SQL only. It causes the client to flush its buffers of all rows, and then send the fetch request to the database server. This fetch request may return a block of rows.
FOR UPDATE clause
The FOR UPDATE clause indicates that the fetched row will subsequently be updated with an UPDATE WHERE CURRENT OF CURSOR statement. This clause causes the database server to put an intent lock on the row. The lock is held until the end of the current transaction.
ARRAY clause
The ARRAY clause is for use in Embedded SQL only. It allows so-called wide fetches, which retrieve more than one row at a time, and which may improve performance.

To use wide fetches in Embedded SQL, include the fetch statement in your code as follows:
```
EXEC SQL FETCH ... ARRAY nnn
```
where ARRAY nnn is the last item of the FETCH statement. The fetch count nnn can be a host variable. The SQLDA must contain nnn * (columns per row) variables. The first row is placed in SQLDA variables 0 to (columns per row)-1, and so on.

Remarks

The FETCH statement retrieves one row from the named cursor. The cursor must have been previously opened.

Table reference variables (variables defined as type TABLE REF) are not supported for use in FETCH statements.

Embedded SQL use
The Embedded SQL FETCH statement does not support arrays.

A DECLARE CURSOR statement must appear before the FETCH statement in the C source code, and the OPEN statement must be executed before the FETCH statement. If a host variable is being used for the cursor name, the DECLARE statement actually generates code and must be executed before the FETCH statement.

The server returns in SQLCOUNT the number of records fetched, and always returns a SQLCOUNT greater than zero unless there is an error or warning.

If the SQLSTATE_NOTFOUND warning is returned on the fetch, the sqlerrd[2] field of the SQLCA (SQLCOUNT) contains the number of rows by which the attempted fetch exceeded the allowable cursor positions. The value is 0 if the row was not found but the position is valid; for example, executing FETCH RELATIVE 1 when positioned on the last row of a cursor. The value is positive if the attempted fetch was beyond the end of the cursor, and negative if the attempted fetch was before the beginning of the cursor. The cursor is positioned on the last row if the attempted fetch was beyond the end of the cursor, and on the first row if the attempted fetch was before the beginning of the cursor.

After successful execution of the fetch statement, the sqlerrd[1] field of the SQLCA (SQLIOCOUNT) is incremented by the number of input/output operations required to perform the fetch. This field is actually incremented on every database statement.
Single row fetch
One row from the result of the SELECT statement is put into the variables in the variable list. The correspondence is one-to-one from the SELECT list to the host variable list.
Multi-row fetch
One or more rows from the result of the SELECT statement are put into either the variables in variable-list or the program data areas described by sqlda-name. In either case, the correspondence is one-to-one from the SELECT list to either the hostvar-list or the sqlda-name descriptor array.

Privileges

The cursor must be opened and you must have the SELECT object-level privilege on the tables, or be owner of the tables referenced in the declaration of the cursor, or have the SELECT ANY TABLE system privilege.

Side effects

A FETCH statement may cause multiple rows to be retrieved from the server to the client if prefetching is enabled.

Standards

ANSI/ISO SQL Standard
With minor exceptions, the stored procedure syntax of the FETCH statement is a Core Feature of the ANSI/ISO SQL Standard. Scrolling options other than NEXT constitute optional ANSI/ISO SQL Language Feature F431, "Read-only scrollable cursors". The software does not support the optional FROM clause of the FETCH statement as documented in the Standard.

The Embedded SQL syntax is not in the standard.

The FOR UPDATE, PURGE, ARRAY, BLOCK, and USING [SQL] DESCRIPTOR clauses are not in the standard.

Example

The following is an Embedded SQL example:

EXEC SQL DECLARE cur_employee CURSOR FOR
SELECT EmployeeID, Surname FROM Employees;
EXEC SQL OPEN cur_employee;
EXEC SQL FETCH cur_employee
INTO :emp_number, :emp_name:indicator;

The following is a procedure example:

BEGIN
   DECLARE cur_employee CURSOR FOR
      SELECT Surname
      FROM Employees;
   DECLARE name CHAR(40);
   OPEN cur_employee;
   lp: LOOP
      FETCH NEXT cur_employee into name;
      IF SQLCODE <> 0 THEN LEAVE lp END IF;
       ...
   END LOOP;
   CLOSE cur_employee;
END