Put elements into the insert cursor

To put elements, one at a time, into the insert cursor, use the PUT statement and the FROM clause.

The PUT statement identifies the insert 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 be compatible with the element type of the collection.

To indicate that the collection element is to be provided later by the FROM clause of the PUT statement, use an input parameter in the VALUES clause of the INSERT statement. You can use the PUT statement with an insert cursor following either a static DECLARE statement or the PREPARE statement. The following example uses a PUT following a static DECLARE statement.

EXEC SQL DECLARE list_curs cursor FOR INSERT INTO table
   (:alist);
EXEC SQL open list_curs;
EXEC SQL PUT list_curs from :asmint;

No input parameters can appear in the DECLARE statement.

The following figure contains a code fragment that demonstrates how to insert elements into the collection variable a_list and then to update the list_col column of the tab_list table (which Sample tables with collection columns defines) with this new collection.

Figure 1: Insertion of many elements into a collection host variable

EXEC SQL BEGIN DECLARE SECTION;
   client collection list(smallint not null) a_list;
   int a_smint;
EXEC SQL END DECLARE SECTION;
⋮

EXEC SQL allocate collection :a_list;

/* Step 1: declare the insert cursor on the collection variable */
EXEC SQL prepare ins_stmt from
   'insert into table values';
EXEC SQL declare list_curs cursor for ins_stmt;
EXEC SQL open list_curs using :a_list;

/* Step 2: put the LIST elements into the insert cursor */
for (a_smint=0; a_smint<10; a_smint++)
{
   EXEC SQL put list_curs from :a_smint;
};
/* Step 3: save the insert cursor into the collection variable
EXEC SQL close list_curs;

/* Step 4: save the collection variable into the LIST column */
EXEC SQL insert into tab_list values (:a_list);

/* Step 5: clean up */
EXEC SQL deallocate collection :a_list;
EXEC SQL free ins_stmt;
EXEC SQL free list_curs;

In Insertion of many elements into a collection host variable, the first statement that accesses the a_list variable is the OPEN statement. Therefore, in the code, must be able to determine the data type of the a_list variable. Because the a_list host variable is a typed collection variable, can determine the data type from the variable declaration. However, if a_list was declared an untyped collection variable, you would need a SELECT statement before the DECLARE statement executes to return the definition of the associated collection column.

automatically saves the contents of the insert cursor into the collection variable when you put them into the insert cursor with the PUT statement.