Variable Structures
To simplify the transfer of data between database tables and embedded programs, variable structures can be used in the select, fetch, and insert statements. Variable structures are specified, like single variables, according to the rules of the host language and must be declared in an embedded OpenSQL declare section. The number, data type, and ordering of the structure's elements must correspond to the number, data type, and ordering of the result columns associated with a SELECT, FETCH, or INSERT statement.
For example, for a database table, employee, with the columns ename (data type character(20)) and eno (integer), declare the variable structure:
emprec
ename character_string(20),
eno integer;
and issue the SELECT statement
exec sql select *
into :emprec.ename, :emprec.eno
from employee
where eno = 23;
Rather than specifying individual variables, you can specify the structure name in the SELECT statement. To specify the preceding example using a structure name, use the following SELECT statement:
exec sql select *
into :emprec
from employee
where eno = 23;
The embedded OpenSQL preprocessor expands the structure name into the names of the individual members. Therefore, placing a structure name in the INTO clause is equivalent to enumerating all members of the structure in the order in which they were declared.
You can also use a structure to insert values in the database table. For example:
exec sql insert into employee (ename, eno)
values (:emprec);
For details on the declaration and use of variable structures, see the Embedded SQL Companion Guide.