The untyped row variable

The definition of an untyped row variable specifies only the row keyword and a name. The following lines declare three untyped row variables:

EXEC SQL BEGIN DECLARE SECTION;
   row row1, row2;
   row rectangle1;
EXEC SQL END DECLARE SECTION;

The advantage of an untyped row host variable is that it provides more flexibility in row definition. For an untyped row variable, you do not have to know the definition of the row-type column at compile time. Instead, you obtain, at run time, a description of the row from a row-type column.

To obtain this description at run time, execute a SELECT statement that retrieves the row-type column into the untyped row variable. When the database server executes the SELECT statement, it returns the data type information for the row-type column (the types of the fields in the row) to the client application.

For example, suppose the a_row host variable is declared as an untyped row variable, as follows:

EXEC SQL BEGIN DECLARE SECTION;
   row a_row;
EXEC SQL END DECLARE SECTION;

The following code fragment uses a SELECT statement to initialize the a_row variable with data type information before it uses the row variable in an UPDATE statement:

EXEC SQL allocate row :a_row;

/* obtain the data-type information */
EXEC SQL select row_col into :a_row from tab_row;

/* update row values in the untyped row variable */
EXEC SQL update table(:a_row) set fld1 = 3;

The field name fld1, which refers to a field of :a_row, comes from the definition of the row column in the tab_row table.

For more information about the ALLOCATE ROW statement, see Manage memory for rows.

You can use the same untyped row variable to successively store different row types, but you must select the associated row-type column into the row variable for each new row type.