SQL/MP Programming Manual for COBOL85

Data Retrieval and Modification
HP NonStop SQL/MP Programming Manual for COBOL85—429326-004
4-16
OPEN Statement
Example 4-2 declares a cursor named LIST-BY-PARTNUM:
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 execute an OPEN statement for a cursor, a program's PAID must have the access
authority described in Process Access ID (PAID) Requirements on page 4-13.
If the associated SELECT statement contains host variables in the WHERE clause,
you must initialize these host variables before you execute the OPEN statement. When
the OPEN statement executes, 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 execute 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.
Example 4-2. Declaring a Cursor
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 PARTS.
02 PARTNUM PIC 9(4) DISPLAY.
02 PARTDESC PIC X(18).
02 PRICE PIC S9(16)V9(2) COMP.
02 QTY-AVAILABLE PIC S9(9) COMP.
...
EXEC SQL END DECLARE SECTION END-EXEC.
...
EXEC SQL DECLARE LIST-BY-PARTNUM CURSOR FOR
SELECT PARTNUM,
PARTDESC,
PRICE,
QTY-AVAILABLE
FROM =PARTS
WHERE PARTNUM>= :PARTNUM OF PARTS
ORDER BY PARTNUM
BROWSE ACCESS;
PROCEDURE DIVISION.
...