Client collections

The application declares the collection variable name, allocates the memory for it with the ALLOCATE COLLECTION statement, and performs operations on the collection data.

To access the elements of a collection variable, specify the variable in the Collection Derived Table clause of a SELECT, INSERT, UPDATE, or DELETE statement. performs the select, insert, update, or delete operation. does not send these statements to the database server when they include a client collection variable in the collection-derived table clause.

For example, performs the following INSERT operation on the a_multiset collection variable: 
EXEC SQL BEGIN DECLARE SECTION;
   client collection multiset(integer not null) a_multiset;
EXEC SQL END DECLARE SECTION;
EXEC SQL insert into table(:a_multiset) values (6);
When an SQL statement includes a collection variable, it has the following syntax restrictions:
  • You can only access elements of a client-side collection with the collection-derived table clause and a SELECT, INSERT, UPDATE, or DELETE statement.
  • An INSERT statement cannot have a SELECT, an EXECUTE FUNCTION, or an EXECUTE PROCEDURE statement in the VALUES clause.
  • You cannot include a WHERE clause
  • You cannot include an expression
  • You cannot use scroll cursors