SQL/MX Programming Manual for C and COBOL (G06.24+, H06.03+)

Static SQL Cursors
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
6-15
Cursor Position
•
You can specify IN EXCLUSIVE MODE for the SELECT statement in the cursor
specification so that NonStop SQL/MX does not have to escalate the lock when an
UPDATE or DELETE executes. Otherwise, if you do not specify IN EXCLUSIVE
MODE and your program is reading records accessed by another cursor defined
with IN EXCLUSIVE MODE, your program must wait for access.
Cursor Position
Cursor position is similar to record position in a sequential file. An open cursor is
positioned either before a certain row, on a certain row, or after the last row.
These operations cause the cursor to be positioned:
The SELECT statement that specifies the cursor can determine the order in which
rows are returned. To specify the order, include an ORDER BY clause. Otherwise, the
order is undefined.
Cursor Stability
Cursor stability guarantees that a row at the current cursor position cannot be modified
by another program, but concurrent access to other rows in the database is allowed.
For NonStop SQL/MX to guarantee cursor stability, you can specify STABLE ACCESS
for the SELECT statement that defines the cursor or you can specify SERIALIZABLE
access.
SERIALIZABLE ACCESS locks the row until the end of the transaction.
STABLE ACCESS is used only for those SELECT statements that could potentially be
updated. If the shape of a SELECT statement is such that it cannot be updated with an
updatable cursor (for example it has a join), the STABLE ACCESS option is internally
changed to READ COMMITTED access.
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
query expression in the cursor declaration 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
OPEN Before the first row
FETCH On the retrieved row (the current position)
DELETE Before the row following the deleted row
UPDATE No change (the current position)
CLOSE No position