In solidDB procedures, it is possible to use positioned updates and deletes. This means that an update or delete is performed on the row where a given cursor is currently positioned. The positioned updates and deletes can also be used within stored procedures by using the cursor names used within the procedure.
The following syntax is used for positioned updates:
UPDATE table_name SET column = value WHERE CURRENT OF cursor_name
The following syntax is used for positioned deletes:
DELETE FROM table_name WHERE CURRENT OF cursor_name
In both cases, cursor_name refers to a SELECT statement on the table where the rows are to be updated or deleted.
A positioned cursor update can cause unintended behavior and should be used with care.
For example, the following pseudo code causes an endless loop with a solidDB server (error handling, binding variables, and other important tasks are omitted for clarity):
"CREATE PROCEDURE ENDLESS_LOOP BEGIN EXEC SQL PREPARE MYCURSOR SELECT * FROM TABLE1; EXEC SQL PREPARE MYCURSOR_UPDATE UPDATE TABLE1 SET COLUMN2 = 'new data'; WHERE CURRENT OF MYCURSOR; EXEC SQL EXECUTE MYCURSOR; EXEC SQL FETCH MYCURSOR; WHILE SQLSUCCESS LOOP EXEC SQL EXECUTE MYCURSOR_UPDATE; EXEC SQL COMMIT WORK; EXEC SQL FETCH MYCURSOR; END LOOP END";
The endless loop is caused by the fact that, when the update is committed, a new version of the row becomes visible in the cursor and that row is accessed in the next FETCH statement. This happens because the incremented row version number is included in the key value and the cursor finds the changed row as the next key value after the current position. The row is updated again, the key value is changed and, again, the cursor finds the changed row as the next key value.
In the example, the updated COLUMN2 is assumed not to be part of the primary key for the table, and the row version number was the only part of the index entry that changed. However, if a value is changed in a column that is part of the index that was used to select the data, the changed row might jump further forward or backward in the search set.
For these reasons, using positioned updates is not recommended in general and searched updates should be used instead whenever possible.
Note that in an open cursor, user changes do not become visible unless they are committed within the same database session.