Informix Guide to SQL: Tutorial

Informix Guide to SQL: Tutorial
Chapter 6: Modifying Data Through SQL Programs

Home Contents Index Master Index New Book

Using UPDATE

You can embed the UPDATE statement in a program in any of the forms described in Chapter 4, "Modifying Data," with the additional feature that you can name host variables in expressions, both in the SET and WHERE clauses. Moreover, a program can update the row that is addressed by a cursor.

Using an Update Cursor

An update cursor permits you to delete or update the current row; that is, the most recently fetched row. The following example (in INFORMIX-ESQL/COBOL) shows the declaration of an update cursor:

EXEC SQL

	DECLARE names CURSOR FOR

		SELECT fname, lname, company

		FROM customer

	FOR UPDATE

END-EXEC.

The program that uses this cursor can fetch rows in the usual way.

EXEC SQL

	FETCH names INTO :FNAME, :LNAME, :COMPANY

END-EXEC.

If the program then decides that the row needs to be changed, it can do so.

IF COMPANY IS EQUAL TO 'SONY'

	EXEC SQL

		UPDATE customer

			SET fname = 'Midori', lname = 'Tokugawa'

			WHERE CURRENT OF names

	END-EXEC.

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

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. (Levels of locks and concurrent use are discussed in Chapter 7, "Programming for a Multiuser Environment.")

Updating Specific Columns

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 using WHERE CURRENT OF is also rejected because deletion affects all columns.

UPDATE Keyword Not Always Needed

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 using any cursor.

Cleaning Up a Table

A final, hypothetical example of using 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, datcol, 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 INFORMIX-ESQL/C code needed to accomplish these things:

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;