Nested Quotation Marks

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, use nested quotation marks. In these cases, you must follow the rule for nesting quotation marks. Otherwise, the database 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 ( ' ) quotation marks 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 examples illustrate the case for two levels of nested collection literals, using double ( " ) quotation marks. Here table tab5 is a single-column table whose only 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 each literal value, the opening quotation mark and the closing quotation mark must match. Thus, if you open a literal with two double quotation marks, you must close that literal with two double quotation marks (""a literal value"").

To specify nested quotation marks within an SQL statement in programs, use the C escape character for every double quotation mark inside a string that is delimited by single quotation marks. Otherwise, the preprocessor cannot correctly interpret the literal collection value. For example, the preceding INSERT statement on the tab5 table would appear in the 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 HCL OneDB™ 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}'}");