Specifying an sqlda Structure

Use the DESCRIPTOR option to introduce the name of a pointer to an sqlda structure. The following ESQL/C example shows how to associate values from an sqlda structure:

Inserting into a Collection Cursor (IDS)

A collection cursor allows you to access the individual elements of a collection variable. To declare a collection cursor, use the DECLARE statement and include the Collection-Derived-Table segment in the INSERT statement that you associate with the cursor. Once you open the collection cursor with the OPEN statement, the cursor can put elements in the collection variable.

To put elements, one at a time, into the insert cursor, use the PUT statement and the FROM clause. The PUT statement identifies the collection cursor that is associated with the collection variable. The FROM clause identifies the element value to be inserted into the cursor. The data type of any host variable in the FROM clause must match the element type of the collection.

Important:

The collection variable stores the elements of the collection. However, it has no intrinsic connection with a database column. Once the collection variable contains the correct elements, you must then save the variable into the actual collection column with the INSERT or UPDATE statement.

Suppose you have a table called children with the following schema:

CREATE TABLE children
(
   age      SMALLINT,
   name     VARCHAR(30),
   fav_colors SET(VARCHAR(20)),
)

The following ESQL/C program fragment shows how to use an insert cursor to put elements into a collection variable called child_colors:

EXEC SQL BEGIN DECLARE SECTION;
   client collection child_colors;
   char *favorites[]
   (
      "blue",
      "purple",
      "green",
      "white",
      "gold",
      0
   );
   int a = 0;
   char child_name[21];
EXEC SQL END DECLARE SECTION;

EXEC SQL allocate collection :child_colors;

/* Get structure of fav_colors column for untyped 
 * child_colors collection variable */
EXEC SQL select fav_colors into :child_colors
   from children where name = :child_name;
/* Declare insert cursor for child_colors collection
 * variable and open this cursor */
EXEC SQL declare colors_curs cursor for 
   insert into table(:child_colors) 
   values (?);
EXEC SQL open colors_curs;
/* Use PUT to gather the favorite-color values
 * into a cursor */
while (fav_colors[a])

{
   EXEC SQL put colors_curs from :favorites[:a];
   a++
   ...
}
/* Flush cursor contents to collection variable */
EXEC SQL flush colors_curs;
EXEC SQL update children set fav_colors = :child_colors;

EXEC SQL close colors_curs;
EXEC SQL deallocate collection :child_colors;

After the FLUSH statement executes, the collection variable, child_colors, contains the elements {"blue", "purple", "green", "white", "gold"}. The UPDATE statement at the end of this program fragment saves the new collection into the fav_colors column of the database. Without this UPDATE statement, the new collection would not be added to the collection column.

Writing Buffered Rows

To open an insert cursor, the OPEN statement creates an insert buffer. The PUT statement puts a row into this insert buffer. The buffered rows are inserted into the database table as a block only when necessary; this process is called flushing the buffer. The buffer is flushed after any of the following events:

Buffer is too full to hold the new row at the start of a PUT statement.
A FLUSH statement executes.
A CLOSE statement closes the cursor.
An OPEN statement specifies an already open cursor, closing it before reopening it. (This implicit CLOSE statement flushes the buffer.)
A COMMIT WORK statement executes.
Buffer contains BYTE or TEXT data (flushed after a single PUT statement).

If the program terminates without closing an insert cursor, the buffer remains unflushed. Rows that were inserted into the buffer since the last flush are lost. Do not rely on the end of the program to close the cursor and flush the buffer.

Error Checking

The sqlca structure contains information on the success of each PUT statement as well as information that lets you count the rows that were inserted. The result of each PUT statement is contained in the following fields of the sqlca: sqlca.sqlcode, SQLCODE, and sqlca.sqlerrd[2].

Data buffering with an insert cursor means that errors are not discovered until the buffer is flushed. For example, an input value that is incompatible with the data type of the column for which it is intended is discovered only when the buffer is flushed. When an error is discovered, buffered rows that were not inserted before the error are not inserted; they are lost from memory.

The SQLCODE field is set to 0 if no error occurs; otherwise, it is set to an error code. The third element of the sqlerrd array is set to the number of rows that were successfully inserted into the database:

If any row is put into the insert buffer, but not written to the database, SQLCODE and sqlerrd are set to 0 (SQLCODE because no error occurred, and sqlerrd because no rows were inserted).
If a block of buffered rows is written to the database during the execution of a PUT statement, SQLCODE is set to 0 and sqlerrd is set to the number of rows that was successfully inserted into the database.
If an error occurs while the buffered rows are written to the database, SQLCODE indicates the error, and sqlerrd contains the number of successfully inserted rows. (The uninserted rows are discarded from the buffer.)

Tip:

When you encounter an SQLCODE error, a SQLSTATE error value also exists. See the GET DIAGNOSTICS statement for details of how to obtain the message text.

To count the number of pending and inserted rows in the database

Prepare two integer variables (for example, total and pending).
When the cursor is opened, set both variables to 0.
Each time a PUT statement executes, increment both total and pending.
Whenever a PUT or FLUSH statement executes or the cursor closes, subtract the third field of the SQLERRD array from pending.

At any time, (total - pending) represents the number of rows actually inserted. If no statements fail, pending contains zero after the cursor is closed. If an error occurs during a PUT, FLUSH, or CLOSE statement, the value that remains in pending is the number of uninserted (discarded) rows.