FETCH Statement [ESQL] [SP]

Syntax

FETCH
{ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE row-count | RELATIVE row-count }
... cursor-name
... { [ INTO host-variable-list ]
| USING DESCRIPTOR sqlda-name
| INTO variable-list }
... [ PURGE ] [ BLOCK n ] [ ARRAY fetch-count ]
... INTO variable-list
... IQ CACHE row-count

Parameters

• cursor-name – identifier or host variable
• sqlda-name – identifier
• host-variable-list – may contain indicator variables
• row-count – number or host variable
• fetch-count – integer or host variable

Examples

• Example 1 – 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;

• Example 2 – Procedure example:

  BEGIN
  	DECLARE cur_employee CURSOR FOR
  		SELECT Surname
  		FROM Employees;
  	DECLARE name CHAR(40) ;
  	OPEN cur_employee;
  	LOOP
  		FETCH NEXT cur_employee into name ;
  		 .
  		 .
  		 .
  	END LOOP
  	CLOSE cur_employee;
  END

Usage

FETCH retrieves one row from the named cursor.

The ARRAY clause allows wide fetches, which retrieve more than one row at a time, and which might improve performance.

The cursor must have been previously opened.

One row from the result of SELECT is put into the variables in the variable list. The correspondence from the select list to the host variable list is one-to-one.

One or more rows from the result of SELECT are put either into the variables in the variable list or into the program data areas described by the named SQLDA. In either case, the correspondence from the select list to either the host variable list or the SQLDA descriptor array is one-to-one.

The INTO clause is optional. If it is not specified, then FETCH positions the cursor only (see the following paragraphs).

An optional positional parameter can be specified that allows the cursor to be moved before a row is fetched. The default is NEXT, which causes the cursor to be advanced one row before the row is fetched. PRIOR causes the cursor to be backed up one row before fetching.

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 backwards. Thus, 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.

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 is a short form for ABSOLUTE 1. LAST is a short form for ABSOLUTE -1.

OPEN initially positions the cursor before the first row.

A cursor declared FOR READ ONLY sees the version of table(s) on which the cursor is declared when the cursor is opened, not the version of table(s) at the time of the first FETCH

If the fetch includes a positioning parameter and the position is outside the allowable cursor positions, then the SQLE_NOTFOUND warning is issued.

The IQ CACHE clause specifies the maximum number of rows buffered in the FIFO queue. If you do not specify a value for IQ CACHE, the value of the CURSOR_WINDOW_ROWS database option is used. The default setting of CURSOR_WINDOW_ROWS is 200.

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

In the multiuser environment, rows can be fetched by the client more than one at a time. This is referred to as block fetching or multirow fetching. The first fetch causes several rows to be sent back from the server. The client buffers these rows and subsequent fetches are retrieved from these buffers without a new request to the server.

The BLOCK clause gives the client and server a hint as to how many rows may be fetched by the application. The special value of 0 means the request is sent to the server and a single row is returned (no row blocking).

The PURGE clause causes the client to flush its buffers of all rows and then send the fetch request to the server. This fetch request may return a block of rows.

If the SQLSTATE_NOTFOUND warning is returned on the fetch, then the sqlerrd[2] field of the SQLCA (SQLCOUNT) contains the number of rows that the attempted fetch exceeded the allowable cursor positions. (A cursor can be on a row, before the first row or after the last row.) 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 further beyond the end of the cursor, and negative if the attempted fetch was further 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.

To use wide fetches in Embedded SQL, include the FETCH statement in your code:

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.

The server returns in SQLCOUNT the number of records fetched and always returns a SQLCOUNT greater than zero unless there is an error. Older versions of the server only return a single row and the SQLCOUNT is set to zero. Thus a SQLCOUNT of zero with no error condition indicates one valid row has been fetched.

Standards

Permissions

The cursor must be opened and the user must have SELECT permission on the tables referenced in the declaration of the cursor.