An update cursor permits you to delete or update the current row; that is, the most recently fetched row. The following example in IBM Informix ESQL/C shows the declaration of an update cursor:
EXEC SQL DECLARE names CURSOR FOR SELECT fname, lname, company FROM customer FOR UPDATE;
The program that uses this cursor can fetch rows in the usual way.
EXEC SQL FETCH names INTO :FNAME, :LNAME, :COMPANY;
If the program then decides that the row needs to be changed, it can do so.
if (strcmp(COMPANY, "SONY") ==0) { EXEC SQL UPDATE customer SET fname = 'Midori', lname = 'Tokugawa' WHERE CURRENT OF names; }
The words CURRENT OF names take the place of the usual test expressions in the WHERE clause. In other respects, the UPDATE statement is the same as usual, even including the specification of the table name, which is implicit in the cursor name but still required.
The purpose of the keyword UPDATE in a cursor is to let the database server know that the program can update (or delete) any row that it fetches. The database server places a more demanding lock on rows that are fetched through an update cursor and a less demanding lock when it fetches a row for a cursor that is not declared with that keyword. This action results in better performance for ordinary cursors and a higher level of concurrent use in a multiprocessing system. (Programming for a Multiuser Environment discusses levels of locks and concurrent use.)
The following example has updated specific columns of the preceding example of an update cursor:
EXEC SQL DECLARE names CURSOR FOR SELECT fname, lname, company, phone INTO :FNAME,:LNAME,:COMPANY,:PHONE FROM customer FOR UPDATE OF fname, lname END-EXEC.
Only the fname and lname columns can be updated through this cursor. A statement such as the following one is rejected as an error:
EXEC SQL UPDATE customer SET company = 'Siemens' WHERE CURRENT OF names END-EXEC.
If the program attempts such an update, an error code is returned and no update occurs. An attempt to delete with WHERE CURRENT OF is also rejected, because deletion affects all columns.
The ANSI standard for SQL does not provide for the FOR UPDATE clause in a cursor definition. When a program uses an ANSI-compliant database, it can update or delete with any cursor.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]