SQL/MP Programming Manual for COBOL

Data Retrieval and Modification
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
4-17
FETCH Statement

If a host variable contains old values from the previous execution of the program, a
subsequent FETCH statement uses these old values as the starting point to
retrieve data. Therefore, the FETCH does not begin at the expected location in the
result table.
The host variables must also be declared within the scope of the OPEN statement.
Some additional considerations for the OPEN statement are:

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 process started by the program must have read
access to tables or views associated with the cursor. For information about process
access, see Required Access Authority
on page 7-1.
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
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.