Cursor Updates
The cursor version of UPDATE is like the interactive update, except for the WHERE clause. The WHERE clause, required in the cursor update, specifies that the update occur to the row the cursor currently points to. If the cursor is not pointing to a row, as is the case immediately after an OPEN or DELETE statement, a runtime error message is generated indicating that a fetch must first be performed. If the row the cursor is pointing to has been deleted from the underlying database table (as the result, for example, of a non-cursor delete), no row is updated and the sqlcode is set to 100. Following a cursor update, the cursor continues to point to the same row.
Two cursor updates not separated by a fetch causes the same row to be updated twice if the cursor was opened in the direct update mode. If the cursor was opened in deferred update mode, more than one update cannot be issued against a row, and the update cannot be followed by a DELETE statement on the same row. Attempting to do either results in an error indicating an ambiguous update operation.
If the table was created with no duplicate rows allowed, the DBMS Server returns an error if attempt is made to insert a duplicate row.
In performing a cursor update, make sure that certain conditions are met:
• A cursor must be declared in the same file in which any UPDATE statement referencing that cursor appears. This applies also to any cursor referenced in a dynamic UPDATE statement string.
• A cursor name in a dynamic UPDATE statement must be unique among all open cursors in the current transaction.
• The cursor stipulated in the update must be open before the statement is executed.
• The UPDATE statement and the FROM clause in the cursor's declaration must see the same database table.
• The columns in the SET clause must have been declared for update at the time the cursor was declared.
• Host language variables can be used only for the cursor names or for expressions in the SET clause.
When executing a cursor update dynamically, using the PREPARE statement, the cursor must be open before the cursor UPDATE statement can be prepared. The prepared statement remains valid while the cursor is open. If the named cursor is closed and reopened, re-prepare the corresponding UPDATE statement. If an attempt is made to execute the UPDATE statement associated with the previously open cursor, the DBMS Server issues an error.
Both the COMMIT and ROLLBACK statements implicitly close all open cursors. A common programming error is to update the current row of a cursor, commit the change, and continue in a loop to repeat the process. This process fails because the first commit closes the cursor.
If the statement does not update any rows, the sqlcode of the SQLCA is set to 100. The sqlerrd(3) of the SQLCA indicates the number of rows updated by the statement.
Last modified date: 08/28/2024