Positioned updates/deletes

Using positioned updates/deletes with named database cursors.

The "WHERE CURRENT OF cursor-name" clause in UPDATE and DELETE statements is not supported by all database engines.

Table 1. Database server support of WHERE CURRENT OF
Database Server Type	WHERE CURRENT OF supported?
Genero db	Yes
IBM^® DB2^® UDB	Yes
IBM Informix^®	Yes
Microsoft™ SQL Server	Yes
MySQL	Yes
Oracle Database Server	No, emulated by driver with ROWIDs
PostgreSQL	No, emulated by driver with OIDs
Sybase ASE	Yes
SQLite	No

Some database drivers can emulate WHERE CURRENT OF mechanisms by using rowids, but this requires additional processing. You should review the code to disable this option.

The standard SQL solution is to use primary keys in all tables and write UPDATE / DELETE statements with a WHERE clause based on the primary key:

DEFINE rec RECORD
           id    INTEGER,
           name  CHAR(100)
     END RECORD
BEGIN WORK
  SELECT CUSTID FROM CUSTOMER
         WHERE CUSTID=rec.id FOR UPDATE
  UPDATE CUSTOMER SET CUSTNAME = rec.name
        WHERE CUSTID = rec.id
COMMIT WORK