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

Home

Contents

Index

Master Index

New Book

Using DELETE To delete rows from a table, a program executes a DELETE statement. The DELETE statement can specify rows in the usual way with a WHERE clause, or it can refer to a single row, the last one fetched through a specified cursor. Whenever you delete rows, you must consider whether rows in other tables depend on the deleted rows. This problem of coordinated deletions is covered in Chapter 4, "Modifying Data." The problem is the same when deletions are made from within a program. Direct Deletions You can embed a DELETE statement in a program. The following example uses INFORMIX-ESQL/C: EXEC SQL delete from items where order_num = :onum; You can also prepare and execute a statement of the same form dynamically. In either case, the statement works directly on the database to affect one or more rows. The WHERE clause in the example uses the value of a host variable named onum. Following the operation, results are posted in SQLSTATE and in the sqlca structure, as usual. The third element of the SQLERRD array contains the count of rows deleted even if an error occurs. The value in SQLCODE shows the overall success of the operation. If the value is not negative, no errors occurred and the third element of SQLERRD is the count of all rows that satisfied the WHERE clause and were deleted. Errors During Direct Deletions When an error occurs, the statement ends prematurely. The values in SQLSTATE and in SQLCODE and the second element of SQLERRD explain its cause, and the count of rows reveals how many rows were deleted. For many errors, that count is zero because the errors prevented the database server from beginning the operation. For example, if the named table does not exist, or if a column tested in the WHERE clause is renamed, no deletions are attempted. However, certain errors can be discovered after the operation begins and some rows are processed. The most common of these errors is a lock conflict. The database server must obtain an exclusive lock on a row before it can delete that row. Other programs might be using the rows from the table, preventing the database server from locking a row. Because the issue of locking affects all types of modifications, it is discussed in Chapter 7, "Programming for a Multiuser Environment." Other, rarer types of errors can strike after deletions begin, for example, hardware errors that occur while the database is being updated. Using Transaction Logging The best way to prepare for any kind of error during a modification is to use transaction logging. In the event of an error, you can tell the database server to put the database back the way it was. The following example is based on the example in "Direct Deletions," which is extended to use transactions: EXEC SQL begin work; /* start the transaction*/ EXEC SQL delete from items where order_num = :onum; del_result = sqlca.sqlcode; /* save two error */ del_isamno = sqlca.sqlerrd[1]; /* ...code numbers */ del_rowcnt = sqlca.sqlerrd[2]; /* ...and count of rows */ if (del_result < 0) /* some problem, */ EXEC SQL rollback work; /* ...put everything back */ else /* everything worked OK, */ EXEC SQL commit work; /* ...finish transaction */ An important point in this example is that the program saves the important return values in the sqlca structure before it ends the transaction. Both the ROLLBACK WORK and COMMIT WORK statements, like other SQL statements, set return codes in the sqlca structure. Executing a ROLLBACK WORK statement after an error wipes out the error code; unless it was saved, it cannot be reported to the user. The advantage of using transactions is that the database is left in a known, predictable state no matter what goes wrong. No question remains about how much of the modification is completed; either all of it or none of it is completed. Coordinated Deletions The usefulness of transaction logging is particularly clear when you must modify more than one table. For example, consider the problem of deleting an order from the demonstration database. In the simplest form of the problem, you must delete rows from two tables, orders and items, as the following example of INFORMIX-ESQL/C shows: EXEC SQL BEGIN WORK; EXEC SQL DELETE FROM items WHERE order_num = :o_num; if (SQLCODE >= 0) { EXEC SQL DELETE FROM orders WHERE order_num = :o_num; if (SQLCODE >= 0) EXEC SQL COMMIT WORK; else { printf("Error %d on DELETE", SQLCODE); EXEC SQL ROLLBACK WORK; } } The logic of this program is much the same whether or not transactions are used. If they are not used, the person who sees the error message has a much more difficult set of decisions to make. Depending on when the error occurred, one of the following situations applies: No deletions were performed; all rows with this order number remain in the database. Some, but not all, item rows were deleted; an order record with only some items remains. All item rows were deleted, but the order row remains. All rows were deleted. In the second and third cases, the database is corrupted to some extent; it contains partial information that can cause some queries to produce wrong answers. You must take careful action to restore consistency to the information. When transactions are used, all these uncertainties are prevented. Deleting with a Cursor You can also write a DELETE statement through a cursor to delete the row that was last fetched. Deleting rows in this manner lets you program deletions based on conditions that cannot be tested in a WHERE clause, as the following example shows: int delDupOrder() { int ord_num; int dup_cnt, ret_code; EXEC SQL declare scan_ord cursor for select order_num, order_date into :ord_num, :ord_date from orders for update; EXEC SQL open scan_ord; if (sqlca.sqlcode != 0) return (sqlca.sqlcode); EXEC SQL begin work; for(;;) { EXEC SQL fetch next scan_ord; if (sqlca.sqlcode != 0) break; dup_cnt = 0; /* default in case of error */ EXEC SQL select count(*) into dup_cnt from orders where order_num = :ord_num; if (dup_cnt > 1) { EXEC SQL delete where current of scan_ord; if (sqlca.sqlcode != 0) break; } } ret_code = sqlca.sqlcode; if (ret_code == 100) /* merely end of data */ EXEC SQL commit work; else /* error on fetch or on delete */ EXEC SQL rollback work; return (ret_code); } Warning: The design of the ESQL/C function in the previous example is unsafe. It depends on the current isolation level for correct operation. Isolation levels are covered later in this chapter. For more information on isolation levels, see Chapter 7, "Programming for a Multiuser Environment." Even when the design works as intended, its effects depend on the physical order of rows in the table, which is not generally a good idea. The purpose of the function is to delete rows that contain duplicate order numbers. In fact, in the demonstration database, the orders.order_num column has a unique index, so duplicate rows cannot occur in it. However, a similar function can be written for another database; this one uses familiar column names. The function declares scan_ord, a cursor to scan all rows in the orders table. It is declared with the FOR UPDATE clause, which states that the cursor can modify data. If the cursor opens properly, the function begins a transaction and then loops over rows of the table. For each row, it uses an embedded SELECT statement to determine how many rows of the table have the order number of the current row. (This step fails without the correct isolation level, as described in Chapter 7, "Programming for a Multiuser Environment.") In the demonstration database, with its unique index on this table, the count returned to dup_cnt is always one. However, if it is greater, the function deletes the current row of the table, reducing the count of duplicates by one. Clean-up functions of this sort are sometimes needed, but they generally need more sophisticated design. This one deletes all duplicate rows except the last one delivered by the database server. That ordering has nothing to do with the contents of the rows or their meanings. You can improve the function in the previous example by adding, perhaps, an ORDER BY clause to the cursor declaration. However, you cannot use ORDER BY and FOR UPDATE together. A better approach is presented in "An Insert Example".