NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
F-4
Considerations—FETCH
Use this option in dynamic SQL if you know the number and data types of the
returned columns.
USING DESCRIPTOR :sqlda-desc
(dynamic SQL only) is a host variable containing an SQLDA descriptor that
describes a list of memory locations (not always declared host variables) into which
corresponding SELECT columns are copied.
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
•
Authorization requirements
FETCH requires read access to any tables or views associated with the cursor.
Updating fetched rows requires write access to the table or view.
•
Ordering fetched rows
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.
•
Too many values or too many variables
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 and TMF transactions
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.
•
Status information
FETCH returns an integer status code to SQLCODE, as follows:
0 The FETCH was successful
100 The end of the table was encountered
> 0 A warning was issued
< 0 An error occurred