Inserting Collection Values into Columns (IDS)

This section describes how to insert a collection value into a column with DB–Access. It does not discuss how to insert individual elements into a collection column. To access or modify the individual elements of a collection, use an ESQL/C program or SPL routine. For information about how to create an ESQL/C program to insert into a collection, see the IBM Informix: ESQL/C Programmer's Manual. For information about how to create an SPL routine to insert into a collection, see Creating and Using SPL Routines.

The examples that this section provides are based on the manager table in Figure 352. The manager table contains both simple and nested collection types.

CREATE TABLE manager
(
  mgr_name        VARCHAR(30),
  department      VARCHAR(12),
  direct_reports  SET(VARCHAR(30) NOT NULL),
  projects        LIST(ROW(pro_name VARCHAR(15),
                    pro_members SET(VARCHAR(20) NOT NULL))
                    NOT NULL)
);

Inserting into Simple Collections and Nested Collections

When you insert values into a row that contains a collection column, you insert the values of all the elements that the collection contains as well as values for the other columns. For example, the following statement inserts a single row into the manager table, which includes columns for both simple collections and nested collections:

INSERT INTO manager(mgr_name, department, direct_reports,
projects)
VALUES 
(
'Sayles', 'marketing', 
"SET{'Simonian', 'Waters', 'Adams', 'Davis', 'Jones'}", 
LIST{
   ROW('voyager_project', SET{'Simonian', 'Waters',
   'Adams', 'Davis'}), 
   ROW ('horizon_project', SET{'Freeman', 'Jacobs',
   'Walker', 'Smith', 'Cannan'}), 
   ROW ('saphire_project', SET{'Villers', 'Reeves',
   'Doyle', 'Strongin'})
   }
)

Inserting Null Values into a Collection That Contains a Row Type

To insert values into a collection that is a ROW type, you must specify a value for each field in the ROW type.

In general, NULL values are not allowed in a collection. However, if the element type of the collection is a ROW type, you can insert null values into individual fields of the row type.

You can also specify an empty collection. An empty collection is a collection that contains no elements. To specify an empty collection, use the braces ({}). For example, the following statement inserts data into a row in the manager table but specifies that the direct_reports and projects columns are empty collections:

INSERT INTO manager
   VALUES ('Sayles', 'marketing', "SET{}", 
   "LIST{ROW(NULL, SET{})}"
)

A collection column cannot contain null elements. The following statement returns an error because NULL values are specified as elements of collections:

INSERT INTO manager
   VALUES ('Cole', 'accounting', "SET{NULL}",
   "LIST{ROW(NULL, ""SET{NULL}"")}"

The following syntax rules apply for performing inserts and updates on collection types:

• Use braces ({}) to demarcate the elements that each collection contains.
• If the collection is a nested collection, use braces ({}) to demarcate the elements of both the inner and outer collections.