Informix Guide to SQL: Syntax Segments

Use the Literal Collection segment to specify values for a collection column. For syntax that allows you to use expressions that evaluate to element values, see Collection Constructors.

You can specify literal collection values for each of the collection data types: SET, MULTISET, or LIST.

To specify a single literal-collection value, specify the collection type and the literal values. The following SQL statement inserts four integer values into the set_col column that is declared as SET(INT NOT NULL):

INSERT INTO table1 (set_col) VALUES ("SET{6, 9, 9, 4}")

You specify an empty collection with a set of empty braces ({}). The following INSERT statement inserts an empty list into a collection column list_col that is declared as LIST(INT NOT NULL):

INSERT INTO table2 (list_col) VALUES ("LIST{}")

If you are passing a literal collection as an argument to an SPL routine, make sure that there is a space between the parentheses that surround the arguments and the quotation marks that indicate the beginning and end of the literal collection.

If you specify a collection as a literal value in a literal row string you need not include the quotation marks around the collection itself. Only the outermost quotation marks that delineate the row string literal are necessary. No quotation marks need surround the nested collection type. For an example, see Literals for Nested Rows.

The diagram for Literal Collection refers to this section.

Elements of a collection can be literal values for the data types in the following table.

Important: You cannot specify the simple-large-object data types (BYTE and TEXT) as the element type for a collection.

Quoted strings must be specified with a different type of quotation mark than the quotation marks that encompass the collection so that the database server can parse the quoted strings. Therefore, if you use double quotation marks to specify the collection, use single quotation marks to specify individual, quoted-string elements.

The diagram for Literal Collection refers to this section.

A nested collection is a collection that is the element type for another collection.

Whenever you nest collection literals, you use nested quotation marks. In these cases, you must follow the rule for nesting quotation marks. Otherwise, the server cannot correctly parse the strings.

The general rule is that you must double the number of quotation marks for each new level of nesting. For example, if you use double quotation marks for the first level, you must use two double quotation marks for the second level, four double quotation marks for the third level, eight for the fourth level, sixteen for the fifth level, and so on. Likewise, if you use single quotes for the first level, you must use two single quotation marks for the second level and four single quotation marks for the third level.

There is no limit to the number of levels you can nest, as long as you follow this rule.

The following example illustrates the case for two levels of nested collection literals, using double quotation marks. Table tab5 is a one-column table whose column, set_col, is a nested collection type.

The following statement creates the tab5 table:

CREATE TABLE tab5 (set_col SET(SET(INT NOT NULL) NOT NULL));

The following statement inserts values into the table tab5:

INSERT INTO tab5 VALUES (
"SET{""SET{34, 56, 23, 33}""}"

)

For any individual literal value, the opening quotation marks and the closing quotation marks must match. In other words, if you open a literal with two double quotes, you must close that literal with two double quotes
(""a literal value"").

To specify nested quotation marks within an SQL statement in an ESQL/C program, you use the C escape character for every double quote inside a single-quote string. Otherwise, the ESQL/C preprocessor cannot correctly interpret the literal collection value. For example, the preceding INSERT statement on the tab5 table would appear in an ESQL/C program as follows:

EXEC SQL insert into tab5

	values ('set{\"set{34, 56, 23, 33}\"}');

For more information, see the chapter on complex data types in the Informix ESQL/C Programmer's Manual.

If the collection is a nested collection, you must include the collection-constructor syntax for each level of collection type. Suppose you define the following column:

nest_col SET(MULTISET (INT NOT NULL) NOT NULL)

The following statement inserts three elements into the nest_col column:

INSERT INTO tabx (nest_col) 

	VALUES ("SET{'MULTISET{1, 2, 3}'}")

To learn how to use quotation marks in INSERT statements, see Nested Quotation Marks.