SQL/MP Programming Manual for COBOL

Data Retrieval and Modification
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
4-14
Cursor Position
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.
For information about process access, see Required Access Authority on page 7-1.
Cursor Position
The cursor position is similar to the record position in a sequential file. The SQL
statements that affect the cursor position in a program are:
Cursor Stability
Cursor stability guarantees that a row at the current cursor position cannot be modified
by another program. For SQL/MP to guarantee cursor stability, declare the cursor with
the FOR UPDATE clause or specify the STABLE ACCESS option. In some cases, a
program might be accessing a copy of a row instead of the actual row. For example, a
program might be accessing a copy of the row if the associated SELECT statement
defining the cursor requires that the system perform any of these operations:

Ordering the rows by a column

Removing duplicate rows

Performing other operations that require the selected table to be copied into a
result table before it is used by a program
If your program is accessing a copy of a row instead of the actual row, the cursor
points to a copy of the data, and the data is concurrently available to other programs.
Accessing a copy of the data, however, never occurs if the cursor is declared with the
SQL Statement Cursor Position or Action
OPEN Positions the cursor before the first row.
FETCH Positions the cursor at the retrieved row (or the current position).
DELETE Positions the cursor between rows. For example, if the current row is
deleted, the cursor is positioned either between rows or before the next
row and after the preceding row.
SELECT Determines the order in which the rows are returned. To specify an
order, include an ORDER BY clause. Otherwise, the order is
undefined.
CLOSE Causes no position; release the result table established by the cursor.