Operate on a collection variable

HCL Informix® supports access to a collection column as a whole through the SELECT, UPDATE, INSERT, and DELETE statements. For example, the SELECT statement can retrieve all elements of a collection, and the UPDATE statement can update all elements in a collection to a single value.
Tip: Informix® can only access the contents of collection columns directly with the IN predicate in the WHERE clause of a SELECT statement and this IN predicate works only with simple collections (collections whose element types are not complex types).

The SELECT, INSERT, UPDATE, and DELETE statements cannot access elements of a collection column in a table. To access elements in a collection column, the Informix® ESQL/C application constructs a subtable, called a collection-derived table, in the collection host variable. From collection-derived table, the Informix® ESQL/C application to access the elements of the collection variable as rows of a table.

This section discusses the following topics on how to use a collection-derived table in the Informix® ESQL/C application to access a collection column:
  • Using the collection-derived table clause in SQL statements to access a collection host variable
  • Initializing a collection host variable with a collection column
  • Inserting elements into a collection host variable
  • Selecting elements from a collection host variable
  • Updating elements in a collection host variable
  • Specifying element values for a collection host variable
  • Deleting elements from a collection host variable
  • Accessing a nested collection with collection host variables