Insert into and update row-type columns

The INSERT and UPDATE statements support row-type columns as follows:

• To insert a new row into a row-type column, specify the new values in the VALUES clause of the INSERT statement.
• To update the entire row-type column, specify the new field values in the SET clause of the UPDATE statement.

In the VALUES clause of an INSERT statement or the SET clause of an UPDATE statement, the field values can be in any of the following formats:

• The row host variable

  For more information, see Access a typed table.

• A constructed row

  Constructed rows are described with respect to row variables in Constructed rows. For information about the syntax of a constructed row, see the Constructed Row segment in the HCL OneDB™ Guide to SQL: Syntax.

• A literal-row value

  For more information about the syntax of a literal-row value, see the Literal Row segment in the HCL OneDB Guide to SQL: Syntax.

To represent literal values for a row-type column, you specify a literal-row value. You create a literal-row value or a named or unnamed row type, introduce the value with the ROW keyword and provide the field values in a comma-separated list that is enclosed in parentheses. You surround the entire literal-row value with quotes (double or single). The following INSERT statement inserts the literal row of ROW(0, 0, 4, 5) into the rectangle column in the tab_unmrow table (that Sample tables with row-type columns defines):

EXEC SQL insert into tab_unmrow values 
(
   20, "row(0, 0, 4, 5)"
);

The UPDATE statement in the following figure overwrites the SET values that the previous INSERT added to the tab_unmrow table.

EXEC SQL update tab_unmrow
   set rectangle = ("row(1, 3, 4, 5)")   
   where area = 20;

If any character value appears in this literal-row value, it too must be enclosed in quotes; this condition creates nested quotes. For example, a literal value for column row1 of row type ROW(id INTEGER, name CHAR(5), would be:

'ROW(6, "dexter")'

To specify nested quotes in an SQL statement in the program, you must escape every double quotation mark when it appears in a quotation mark string. The following two INSERT statements show how to use escape characters for inner quotes:

EXEC SQL insert into (row1) tab1 
   values ('ROW(6, \"dexter\")');

EXEC SQL insert into (row2) tab1
   values ('ROW(1, \"SET{80, 81, 82, 83}\")');

When you embed a double-quoted string inside another double-quoted string, you do not need to escape the inner-most quotation marks:

EXEC SQL insert into tabx
   values (1, "row(""row(12345)"")");

If the row type contains a row type or a collection as a member, the inner row does not need quotes. For example, for column col2 whose data type is ROW(a INTEGER, b SET (INTEGER)), a literal value would be:

'ROW(1, SET{80, 81, 82, 83})'