An Insert Example

Deleting with a Cursor contains an example of the DELETE statement whose purpose is to look for and delete duplicate rows of a table. A better way to perform this task is to select the desired rows instead of deleting the undesired ones. The code in the following IBM Informix ESQL/C example shows one way to do this task:

EXEC SQL BEGIN DECLARE SECTION;
   long last_ord = 1;
   struct {
      long int o_num;
      date     o_date;
      long     c_num;
      char     o_shipinst[40];
      char     o_backlog;
      char     o_po[10];
      date     o_shipdate;
      decimal  o_shipwt;
      decimal  o_shipchg;
      date     o_paiddate;
      } ord_row;
EXEC SQL END DECLARE SECTION;

EXEC SQL BEGIN WORK;
EXEC SQL INSERT INTO new_orders
   SELECT * FROM orders main
      WHERE 1 = (SELECT COUNT(*) FROM orders minor
         WHERE main.order_num = minor.order_num);
EXEC SQL COMMIT WORK;

EXEC SQL DECLARE dup_row CURSOR FOR
   SELECT * FROM orders main INTO :ord_row
      WHERE 1 < (SELECT COUNT(*) FROM orders minor
         WHERE main.order_num = minor.order_num)
      ORDER BY order_date;
EXEC SQL DECLARE ins_row CURSOR FOR
   INSERT INTO new_orders VALUES (:ord_row);

EXEC SQL BEGIN WORK;
EXEC SQL OPEN ins_row;
EXEC SQL OPEN dup_row;
while(SQLCODE == 0)
{
   EXEC SQL FETCH dup_row;
   if(SQLCODE == 0)
   {
      if(ord_row.o_num != last_ord)
         EXEC SQL PUT ins_row;
      last_ord = ord_row.o_num
      continue;
   }
   break;
}
if(SQLCODE != 0 && SQLCODE != 100)
   EXEC SQL ROLLBACK WORK;
else
   EXEC SQL COMMIT WORK;
EXEC SQL CLOSE ins_row;
EXEC SQL CLOSE dup_row;

This example begins with an ordinary INSERT statement, which finds all the nonduplicated rows of the table and inserts them into another table, presumably created before the program started. That action leaves only the duplicate rows. (In the demonstration database, the orders table has a unique index and cannot have duplicate rows. Assume that this example deals with some other database.)

The code in the previous example then declares two cursors. The first, called dup_row, returns the duplicate rows in the table. Because dup_row is for input only, it can use the ORDER BY clause to impose some order on the duplicates other than the physical record order used in the example on page Deleting with a Cursor. In this example, the duplicate rows are ordered by their dates (the oldest one remains), but you can use any other order based on the data.

The second cursor, ins_row, is an insert cursor. This cursor takes advantage of the ability to use a C structure, ord_row, to supply values for all columns in the row.

The remainder of the code examines the rows that are returned through dup_row. It inserts the first one from each group of duplicates into the new table and disregards the rest.

For the sake of brevity, the preceding example uses the simplest kind of error handling. If an error occurs before all rows have been processed, the sample code rolls back the active transaction.

How Many Rows Were Affected?

When your program uses a cursor to select rows, it can test SQLCODE for 100 (or SQLSTATE for 02000), the end-of-data return code. This code is set to indicate that no rows, or no more rows, satisfy the query conditions. For databases that are not ANSI compliant, the end-of-data return code is set in SQLCODE or SQLSTATE only following SELECT statements; it is not used following DELETE, INSERT, or UPDATE statements. For ANSI-compliant databases, SQLCODE is also set to 100 for updates, deletes, and inserts that affect zero rows.

A query that finds no data is not a success. However, an UPDATE or DELETE statement that happens to update or delete no rows is still considered a success. It updated or deleted the set of rows that its WHERE clause said it should; however, the set was empty.

In the same way, the INSERT statement does not set the end-of-data return code even when the source of the inserted rows is a SELECT statement, and the SELECT statement selected no rows. The INSERT statement is a success because it inserted as many rows as it was asked to (that is, zero).

To find out how many rows are inserted, updated, or deleted, a program can test the third element of SQLERRD. The count of rows is there, regardless of the value (zero or negative) in SQLCODE.