Example of Inserting a Value into a Multiset Collection

Suppose the host variable a_multiset has the following declaration:

EXEC SQL BEGIN DECLARE SECTION;
   client collection multiset(integer not null) a_multiset;
EXEC SQL END DECLARE SECTION;

The following INSERT statement adds a new MULTISET element of 142,323 to a_multiset:

EXEC SQL allocate collection :a_multiset;
EXEC SQL select multiset_col into :a_multiset from table1
   where id = 107;
EXEC SQL insert into table(:a_multiset) values (142323);
EXEC SQL update table1 set multiset_col = :a_multiset
   where id = 107;

EXEC SQL deallocate collection :a_multiset;

When you insert elements into a client-collection variable, you cannot specify a SELECT statement or an EXECUTE FUNCTION statement in the VALUES clause of the INSERT. When you insert elements into a server-collection variable, however, the SELECT and EXECUTE FUNCTION statements are valid in the VALUES clause. For more information on client- and server-collection variables, see the HCL OneDB™ ESQL/C Programmer's Manual.