SQL/MP Programming Manual for C

Data Retrieval and Modification
HP NonStop SQL/MP Programming Manual for C—429847-008
4-19
OPEN Statement
OPEN Statement
The OPEN statement opens an SQL cursor. The OPEN operation orders and defines
the set of rows in the result table and then positions the cursor before the first row.
The OPEN statement does not acquire any locks unless a sort is necessary to order
the selected rows. (The FETCH statement acquires any locks associated with a
cursor.)
To run an OPEN statement for a cursor, a process started by the program must have
the access authority described in Process Access ID (PAID) Requirements on
page 4-16. For details, see Required Access Authority on page 7-1.
If the associated SELECT statement contains host variables in the WHERE clause,
you must initialize these host variables before you run the OPEN statement. When the
OPEN statement runs, NonStop SQL/MP defines the set of rows in the result table and
places the input host variables in its buffers. If you do not initialize the host variables
before you run the OPEN statement, these problems can occur:

If a host variable contains values with unexpected data types, overflow or
truncation errors can occur.

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 run before any FETCH statements for the cursor.

For audited tables and views, the OPEN statement must run within a TMF
transaction.

If data is materialized by the OPEN operation, NonStop 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;
...