SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
F-4
Considerations—FETCH
Use this option in dynamic SQL if you have no previous knowledge of the returned
columns and use DESCRIBE to retrieve their descriptions.
Considerations—FETCH

FETCH requires read access to any tables or views associated with the cursor.
Updating fetched rows requires write access to the table or view.

Successive executions of FETCH retrieve successive rows in the result table. To
control the order in which the rows appear, include an ORDER BY clause in the
SELECT portion of the DECLARE CURSOR statement that defines the cursor.

If the number of host variables is different from the number of columns in the result
table, FETCH issues a warning and returns the number of values in the shorter list
(column list or host variable list).
If the column list is shorter than the host variable list, the values in the extra host
variables are indeterminate.

Locking occurs when the FETCH executes (or, if the SELECT requires a sort,
when you open the cursor), but the SELECT statement associated with the cursor
specifies whether the access option that controls locking is BROWSE, STABLE, or
REPEATABLE.
A FETCH for a cursor on an audited table that uses STABLE or REPEATABLE
access must execute in the same TMF transaction that opened the cursor.

FETCH returns an integer status code to SQLCODE:
Avoid using SQLCODE 100 as an end-of-file indicator. SQL resets SQLCODE to 0
when you close the associated cursor. Instead, define your own end-of-file flag.
Examples—FETCH

In this example, suppose that you have a cursor that returns information from the
PARTS table. The host variables are declared in a Declare Section, and the cursor
declaration lists the columns to be retrieved. The FETCH statement lists a
corresponding host variable to receive the values returned for each column. (The
example uses the SQL statement terminator for COBOL programs.)
0 The FETCH was successful
100 The end of the table was encountered
> 0 A warning was issued
< 0 An error occurred