Access a typed table

You can use a row variable to access the columns of a typed table. A typed table is a table that was created with the OF TYPE clause of the CREATE TABLE statement. This table obtains the information for its columns from a named row type.

Suppose you create a typed table called names from the full_name named row type that Sample tables with row-type columns defines:

EXEC SQL create table names of type full_name;

You can access a row of the names typed table with a row variable. The following code fragment declares a_name as a typed row variable and selects a row of the names table into this row variable:

EXEC SQL BEGIN DECLARE SECTION;
   row (
      fname char(15), 
      mi char(2)
      lname char(15)
      ) a_name;
   char last_name[16];
EXEC SQL END DECLARE SECTION;
;

EXEC SQL allocate row :a_name;
EXEC SQL select name_row into :a_name
   from names name_row
   where lname = 'Haven'
    and fname = 'C. K.'
    and mi = 'D';
EXEC SQL select lname into :last_name from table(:a_name);

The last SELECT statement accesses the lname field value of the :a_name row variable. For more information about typed tables, see the CREATE TABLE statement in the HCL OneDB™ Guide to SQL: Syntax and the HCL OneDB Guide to SQL: Tutorial.

The following example illustrates how you can also use an untyped row variable to access a row of an untyped table:

EXEC SQL BEGIN DECLARE SECTION;
row untyped_row;
int i;
char s[21];
EXEC SQL END DECLARE SECTION;

EXEC SQL create table tab_untyped(a integer, b char(20));
EXEC SQL insert into tab_untyped(1, "junk");
EXEC SQL select tab_untyped into :untyped_row 
   from tab_untyped;
EXEC SQL select a, b into :i, :s from table(:untyped);