SQL/MP Programming Manual for COBOL85

Data Retrieval and Modification
HP NonStop SQL/MP Programming Manual for COBOL85—429326-004
4-13
Process Access ID (PAID) Requirements
4. Open the cursor by using an OPEN statement. The OPEN statement determines
the result table and sorts the table if the SELECT statement includes the ORDER
BY clause. For audited tables or views, the OPEN statement also associates the
cursor with a TMF transaction.
5. Retrieve the column values from a row using the FETCH statement. The FETCH
statement positions the cursor at the next row of the result table and transfers the
column values defined in the associated SELECT statement to the corresponding
host variables. The FETCH statement also locks each row according to the access
specified by the SELECT statement.
For audited tables or views, the FETCH statement must execute within the same
TMF transaction as the OPEN statement.
6. Process the column values returned from the current row to the host variables. For
example, you might test a value and then delete or update the row.
7. After you process the current row, branch back to the FETCH statement and
retrieve the next row. Continue executing this loop until you have processed all
rows specified by the associated SELECT statement (and SQLCODE equals 100).
8. Close the cursor using the CLOSE statement. The CLOSE statement releases the
result table established by the OPEN statement. (The FREE RESOURCES
statement also releases the result table.)
Process Access ID (PAID) Requirements
To use an SQL cursor, a program’s PAID must have the access authority as follows.
SQL/MP checks this authority when the program opens the cursor.
A program can use a cursor whose declaration does not specify FOR UPDATE to
locate rows in a table to delete. SQL/MP tests the table only for read access when the
OPEN statement executes. However, because a DELETE operation requires write
access, SQL/MP checks for write access when you execute the DELETE statement.
A program contending for data access with other users can specify the IN EXCLUSIVE
MODE clause in the associated SELECT statement. SQL/MP then does not have to
convert the lock for a subsequent UPDATE or DELETE operation. However, if a
program is reading records accessed concurrently by a cursor defined with an IN
EXCLUSIVE MODE clause in another program, the first program must wait to access
the data.
Access SQL Objects
Read Tables or protection views referred to in the SELECT statement associated with
the cursor (that is, specified in the DECLARE CURSOR statement)
Read Tables or protection views underlying the shorthand view, if the cursor refers to
a shorthand view
Write Tables referenced, if the cursor declaration includes the FOR UPDATE clause