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 INSERT

You can embed the INSERT statement in programs. Its form and use in a program are the same as described in Chapter 4, "Modifying Data," with the additional feature that you can use host variables in expressions, both in the VALUES and WHERE clauses. Moreover, a program has the additional ability to insert rows using a cursor.

Using an Insert Cursor

The DECLARE CURSOR statement has many variations. Most are used to create cursors for different kinds of scans over data, but one variation creates a special kind of cursor called an insert cursor. You use an insert cursor with the PUT and FLUSH statements to insert rows into a table in bulk efficiently.

Declaring an Insert Cursor

To create an insert cursor, declare a cursor to be for an INSERT statement instead of a SELECT statement. You cannot use such a cursor to fetch rows of data; you can use it only to insert them. The following is an example of the declaration of an insert cursor :

DEFINE the_company LIKE customer.company,

	the_fname LIKE customer.fname,

	the_lname LIKE customer.lname

DECLARE new_custs CURSOR FOR

	INSERT INTO customer (company, fname, lname)

		VALUES (the_company, the_fname, the_lname)

When you open an insert cursor, a buffer is created in memory to hold a block of rows. The buffer receives rows of data as the program produces them; then they are passed to the database server in a block when the buffer is full. This reduces the amount of communication between the program and the database server, and it lets the database server insert the rows with less difficulty. As a result, the insertions go faster.

The minimum size of the insert buffer is set for any implementation of embedded SQL; you have no control over it (it is typically 1 or 2 kilobytes). The buffer is always made large enough to hold at least two rows of inserted values. It is large enough to hold more than two rows when the rows are shorter than the minimum buffer size.

Inserting with a Cursor

The code in the previous example prepares an insert cursor for use. The continuation, as the following example shows, demonstrates how the cursor can be used. For simplicity, this example assumes that a function named next_cust returns either information about a new customer or null data to signal the end of input.

EXEC SQL BEGIN WORK;

EXEC SQL OPEN new_custs;

while(SQLCODE == 0)

{

	next_cust();

	if(the_company == NULL)

		break;

	EXEC SQL PUT new_custs;

}

if(SQLCODE == 0) 				/* if no problem with PUT */

{

	EXEC SQL FLUSH new_custs;				/* write any rows left */

	if(SQLCODE == 0)			/* if no problem with FLUSH */

		EXEC SQL COMMIT WORK;			/* commit changes */

}

else

	EXEC SQL ROLLBACK WORK;				/* else undo changes */

The code in this example calls next_cust repeatedly. When it returns non-null data, the PUT statement sends the returned data to the row buffer. When the buffer fills, the rows it contains are automatically sent to the database server. The loop normally ends when next_cust has no more data to return. Then the FLUSH statement writes any rows that remain in the buffer, after which the transaction terminates.

Examine the INSERT statement on page 6-9 once more. The statement by itself, not part of a cursor definition, inserts a single row into the customer table. In fact, the whole apparatus of the insert cursor can be dropped from the example code, and the INSERT statement can be written into the code where the PUT statement now stands. The difference is that an insert cursor causes a program to run somewhat faster.

Status Codes After PUT and FLUSH

When a program executes a PUT statement, the program should test whether the row is placed in the buffer successfully. If the new row fits in the buffer, the only action of PUT is to copy the row to the buffer. No errors can occur in this case. However, if the row does not fit, the entire buffer load is passed to the database server for insertion, and an error can occur.

The values returned into the SQL Communications Area (SQLCA) give the program the information it needs to sort out each case. SQLCODE and SQLSTATE are set after every PUT statement, to zero if no error occurs and to a negative error code if an error occurs.

The third element of SQLERRD is set to the number of rows actually inserted into the table. It is set to zero if the new row is merely moved to the buffer; to the count of rows that are in the buffer if the buffer load is inserted without error; or to the count of rows inserted before an error occurs, if one does occur.

Read the code once again to see how SQLCODE is used (see the previous example). First, if the OPEN statement yields an error, the loop is not executed because the WHILE condition fails, the FLUSH operation is not performed, and the transaction rolls back.

Second, if the PUT statement returns an error, the loop ends because of the WHILE condition, the FLUSH operation is not performed, and the transaction rolls back. This condition can occur only if the loop generates enough rows to fill the buffer at least once; otherwise, the PUT statement cannot generate an error.

The program might end the loop with rows still in the buffer, possibly without inserting any rows. At this point, the SQL status is zero, and the FLUSH operation occurs. If the FLUSH operation produces an error code, the transaction rolls back. Only when all inserts are successfully performed is the transaction committed.

Rows of Constants

The insert cursor mechanism supports one special case where high performance is easy to obtain. In this case, all the values listed in the INSERT statement are constants: no expressions and no host variables, just literal numbers and strings of characters. No matter how many times such an INSERT operation occurs, the rows it produces are identical. In that case, there is no point in copying, buffering, and transmitting each identical row.

Instead, for this kind of INSERT operation, the PUT statement does nothing except to increment a counter. When a FLUSH operation is finally performed, a single copy of the row, and the count of inserts, is passed to the database server. The database server creates and inserts that many rows in one operation.

It is not common to insert a quantity of identical rows. You can do it when you first establish a database, to populate a large table with null data.

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 do the same thing is to select the desired rows instead of deleting the undesired ones. The code in the following INFORMIX-ESQL/C example shows one way to do this.

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 6-7. 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, this 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 do (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.

Informix Guide to SQL: TutorialChapter 6: Modifying Data Through SQL Programs Home Contents Index Master Index New Book