Advanced PowerBuilder

Updating Through a Cursor

An application can update or delete a row to which the cursor currently points in the result set, using CURRENT. For example:

UPDATE "product_master" SET "product_balance" = product_balance + 100 WHERE CURRENT OF lProductMasterCursor1 ;

In this example, instead of using a WHERE clause, we give the name of the cursor. This updates the row at the current cursor position - suppose you fetch four items and issue this command, the fourth row is the one that gets updated. Updating a cursor doesn't change the row position in the result set. There are few points to remember when updating using a cursor:

• A fetch, update or delete returns an error, only if any of the columns were changed since the last read, even if the column is not included in the SELECT list. If any of the rows in the result set has been deleted, it creates a hole, and if you try to fetch it, it results in an error.
• You can get around this by using a Dynamic Scroll cursor. The SQL Anywhere syntax for declaring a cursor is:

DECLARE <CursorName> [ SCROLL | NO SCROLL | DYNAMIC SCROLL]
CURSOR FOR <Statement> [ FOR UPDATE | FOR READ ONLY ]

• You simply specify whether you want scrolling, no scrolling or dynamic scrolling. A dynamic cursor won't return an error, but will skip the changed row and fetch the next row. The final clause of the declaration statement specifies whether you want to allow updates or make it a read-only.
• When using aggregate functions, DISTINCT options, GROUP BY clauses, ORDER BY clauses or UNION operators, the cursor is not updateable, also, when you specify FOR UPDATE, the table should have at least one unique index, otherwise it results in an error.
• You can't paint this UPDATE statement with a WHERE CURRENT OF clause from the Edit > Paste SQL menu option, if you have declared a cursor in the script. Only when you declare a cursor as a shared, instance or global cursor can you paste the SQL, by selecting Declare from the menu option.