A final, hypothetical example of how to use an update cursor presents a problem that should never arise with an established database but could arise in the initial design phases of an application.
In the example, a large table named target is created and populated. A character column, dactyl, inadvertently acquires some null values. These rows should be deleted. Furthermore, a new column, serials, is added to the table with the ALTER TABLE statement. This column is to have unique integer values installed. The following example shows the IBM Informix ESQL/C code you use to accomplish these tasks:
EXEC SQL BEGIN DECLARE SECTION; char dcol[80]; short dcolint; int sequence; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE target_row CURSOR FOR SELECT datcol INTO :dcol:dcolint FROM target FOR UPDATE OF serials; EXEC SQL BEGIN WORK; EXEC SQL OPEN target_row; if (sqlca.sqlcode == 0) EXEC SQL FETCH NEXT target_row; for(sequence = 1; sqlca.sqlcode == 0; ++sequence) { if (dcolint < 0) /* null datcol */ EXEC SQL DELETE WHERE CURRENT OF target_row; else EXEC SQL UPDATE target SET serials = :sequence WHERE CURRENT OF target_row; } if (sqlca.sqlcode >= 0) EXEC SQL COMMIT WORK; else EXEC SQL ROLLBACK WORK;Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]