SQL/MP Programming Manual for COBOL85

Data Retrieval and Modification
HP NonStop SQL/MP Programming Manual for COBOL85—429326-004
4-17
FETCH Statement
Some additional considerations for the OPEN statement are as follows:
•
You must code an OPEN statement within the scope of all other SQL statements
(including the DECLARE CURSOR, FETCH, INSERT, DELETE, UPDATE, and
CLOSE statements) that use the cursor.
•
The OPEN statement must execute before any FETCH statements for the cursor.
•
For audited tables and views, the OPEN statement must execute within a TMF
transaction.
•
If data is materialized by the OPEN operation, SQL/MP returns statistics to the
SQLSA structure. For information about returning statistics to a program, see
Section 9, Error and Status Reporting.
•
If the DECLARE CURSOR statement for the cursor specifies a sort operation (for
example, with an ORDER BY clause), do not issue an AWAITIO or AWAITIOX
statement with the filenum parameter set to -1 after you open the cursor;
otherwise, the sort operation fails with SQL error -8301.
This OPEN statement opens the LIST-BY-PARTNUM cursor:
EXEC SQL OPEN LIST-BY-PARTNUM END-EXEC.
FETCH Statement
The FETCH statement positions the cursor at the next row of the result table and
transfers a value from each column in the row specified by the associated SELECT
statement to the corresponding host variable.
To execute a FETCH statement, a program's PAID must have read access to tables or
views associated with the cursor.
SQL/MP returns these values to SQLCODE after a FETCH statement.
The cursor must be open when the FETCH statement executes. The FETCH
statement must also execute within the scope of all other SQL statements, including
the DECLARE CURSOR, OPEN, INSERT, DELETE, UPDATE, and CLOSE statements
that refer to the cursor.
SQL/MP resets values in an SQLSA structure immediately before a FETCH statement
executes. If you use an SQLSA value elsewhere in your program, save the value in a
variable immediately after the FETCH statement executes. To monitor statistics for a
cursor, declare accumulator variables for the required values and add the SQLSA
values to the accumulator variables after each FETCH statement executes.
SQLCODE Value Description
0
The FETCH statement was successful.
100
The end of a table was encountered.
< 0
An error occurred; SQLCODE contains the error number.
> 0 (not 100)
A warning occurred; SQLCODE contains the first warning number.