FETCH statement [ESQL] [SP]

Reference Manual
CHAPTER 9. SQL Statements

FETCH statement [ESQL] [SP]

Function

To reposition a cursor, and then get data from it.

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 ]
... [ FOR UPDATE ] [ ARRAY fetch-count ]
... INTO variable-list [ FOR UPDATE ]

Parameters

row-count: number or host variable

cursor-name: identifier or host-variable

host-variable-list: may contain indicator variables

sqlda-name: identifier

fetch-count: integer or host variable

Permissions

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

Side effects

None.

Description

The FETCH statement retrieves one row from the named cursor.

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

The cursor must have been previously opened.

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.

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

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

An optional positional parameter 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 (see Using cursors in procedures and triggers).

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.

The OPEN statement initially positions the cursor before the first row.

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

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

This occurs if a temporary table had to be created to open the cursor (see Temporary tables used in query processing for a description).

The UPDATE statement may 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).

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 a write lock on the row. The lock will be held until the end of the current transaction. See How locking works.

Using the FETCH statement in Embedded SQL

The following clauses are for use in Embedded SQL only:

USING DESCRIPTOR sqlda-name
INTO host-variable-list
PURGE
BLOCK n
ARRAY fetch-count
Use of host-variable in cursor-name and row-count.

The 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 thus must be executed before the FETCH statement.

In the multi-user environment, rows may be fetched by the client more than one at a time. Note that in UNIX, the client is linked into the application so this will always happen by default. This is referred to as block fetching or multi-row 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 will be sent to the server and a single row will be 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. Note that this fetch request may return a block of rows.

If the SQLSTATE_NOTFOUND warning is returned on the fetch, the sqlerrd[2] field of the SQLCA (SQLCOUNT) will contain the number of rows by which 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 will be 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) will be 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 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.

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 only server return a single row and set the SQLCOUNT to zero. A SQLCOUNT of zero with no error condition indicates that one valid row has been fetched.

Standards and compatibility

SQL/92 Entry level feature. Use in procedures is a Persistent Stored Module feature.
Sybase Supported in Adaptive Server Enterprise.

Example

The following is an Embedded SQL example.

EXEC SQL DECLARE cur_employee CURSOR FOR
SELECT emp_id, emp_lname FROM employee ;
EXEC SQL OPEN cur_employee;
EXEC SQL FETCH cur_employee
INTO :emp_number, :emp_name:indicator;

For a detailed example of using wide fetches, see the section Fetching more than one row at a time.

The following is a procedure example:

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

FETCH statement [ESQL] [SP]

Function

Syntax

Parameters

Permissions

Side effects

See also

Description

Using the FETCH statement in Embedded SQL

Standards and compatibility

Example