Prepare statements that have collection variables

You use the Collection Derived Table clause with an INSERT or SELECT statement to access the Informix® ESQL/C collection variable. (For more information about how to use the Collection Derived Table clause and collection variables, see Complex data types.)

When you prepare a statement that manipulates the Informix ESQL/C collection variable, the following restrictions apply:
  • You must specify the statement text as a quoted string in the PREPARE statement.

    For collection variables, Informix ESQL/C does not support statement text that is stored in a program variable.

  • The quoted string for the statement text cannot contain any collection host variables.

    To manipulate a collection variable, you must use the question mark (?) symbol to indicate an input parameter and then provide the collection variable when you execute the statement.

  • You cannot perform multi-statement prepares if a statement contains a collection variable.
For example, the following Informix ESQL/C code fragment prepares an INSERT on the a_set client collection variable: 
EXEC SQL BEGIN DECLARE SECTION;
   client collection set(integer not null) a_set;
EXEC SQL END DECLARE SECTION;

EXEC SQL prepare coll_stmt from 
   'insert into table values (1, 2, 3)';
EXEC SQL execute coll_stmt using :a_set;
Important: You must declare the Informix ESQL/C collection variable as a client collection variable (a collection variable that is stored on the client computer).