SQL Programming Manual for Pascal

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for Pascal—528614-001
3-37
UPDATE and Locks
•
A set of rows
A set of rows, one row at a time using a cursor (a mechanism for dealing with a set of
rows returned in sequence to a program) In general, specifying an update transaction
in a program is the same as specifying an update transaction in SQLCI.
UPDATE and Locks
A lock on an updated row is held until the TMF transaction is committed or rolled back
(audited table) or until the program releases the lock (nonaudited table).
UPDATE and Access
To execute an UPDATE statement, a program's process accessor ID (PAID) must have
read and write access to the table or view being updated. A program's PAID must also
have read access to any table or view specified in subqueries of the search condition.
UPDATE WHERE CURRENT With Static SQL Cursors
A static SQL cursor can only be referred to in procedures in the compilation unit in
which the cursor was declared. This means that for static SQL programs, the UPDATE
WHERE CURRENT, DECLARE CURSOR, OPEN, FETCH, and CLOSE statements
that refer to the cursor must be in the same compilation unit.
If the program exits a procedure with an open cursor, procedures that execute later can
still refer to the cursor, as long as the procedures are in the same compilation unit.
UPDATE WHERE CURRENT With Dynamic SQL Cursors
For dynamic SQL cursors, all statements referencing the cursor must be in the
procedure where the cursor is defined; however, if you open the cursor and use it in
one call to the procedure where it is defined, you can still use the cursor in subsequent
calls, without opening the cursor again.
UPDATE and Error Processing
Rows are updated in sequence. If an error occurs, NonStop SQL returns an error code
to the SQLCODE variable and terminates the operation. The SQLCODE values that
NonStop SQL returns after an UPDATE statement are:
Value Meaning
0 The UPDATE statement was successful.
100 No rows were found on a search condition.
< 0 An error occurred; SQLCODE contains the error number.
> 0 (not 100) A warning occurred; SQLCODE contains the first warning number.