Client collections

The Informix® ESQL/C 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. Informix ESQL/C performs the select, insert, update, or delete operation. Informix ESQL/C does not send these statements to the database server when they include a client collection variable in the collection-derived table clause.

For example, Informix ESQL/C 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