With host structure support, the C/C++ precompiler allows host variables to be grouped into a single host structure. This provides a shorthand for referencing that same set of host variables in an SQL statement. For example, the following host structure can be used to access some of the columns in the STAFF table of the SAMPLE database:
struct tag
{
short id;
struct
{
short length;
char data[10];
} name;
struct
{
short years;
double salary;
} info;
} staff_record;
The fields of a host structure can be any of the valid host variable types. These include all numeric, character, and large object types. Nested host structures are also supported up to 25 levels. In the example above, the field info is a sub-structure, whereas the field name is not, as it represents a VARCHAR field. The same principle applies to LONG VARCHAR, VARGRAPHIC and LONG VARGRAPHIC. Pointer to host structure is also supported.
There are two ways to reference the host variables grouped in a host structure in an SQL statement:
EXEC SQL SELECT id, name, years, salary
INTO :staff_record
FROM staff
WHERE id = 10;
The precompiler converts the reference to staff_record into a list, separated by commas, of all the fields declared within the host structure. Each field is qualified with the host structure names of all levels to prevent naming conflicts with other host variables or fields. This is equivalent to the following method.
EXEC SQL SELECT id, name, years, salary
INTO :staff_record.id, :staff_record.name,
:staff_record.info.years, :staff_record.info.salary
FROM staff
WHERE id = 10;
References to field names must be fully qualified even if there are no other host variables with the same name. Qualified sub-structures can also be referenced. In the example above, :staff_record.info can be used to replace :staff_record.info.years, :staff_record.info.salary.
Since a reference to a host structure (first example) is equivalent to a comma-separated list of its fields, there are instances where this type of reference may lead to an error. For example:
EXEC SQL CONNECT TO :staff_record;
Here, the CONNECT statement expects a single character-based host variable. By giving a host structure instead, the statement results in a precompile-time error:
SQL0087N Host variable "staff_record" is a structure used where structure references are not permitted.
Other uses of host structures, which may cause an SQL0087N error to occur, include PREPARE, EXECUTE IMMEDIATE, CALL, indicator variables and SQLDA references. Host structures with exactly one field are permitted in such situations, as are references to individual fields (second example).
An indicator table is a collection of indicator variables to be used with a host structure. It must be declared as an array of short integers. For example:
short ind_tab[10];
The example above declares an indicator table with 10 elements. The following shows the way it can be used in an SQL statement:
EXEC SQL SELECT id, name, years, salary
INTO :staff_record INDICATOR :ind_tab
FROM staff
WHERE id = 10;
The following lists each host structure field with its corresponding indicator variable in the table:
| Note: | An indicator table element, for example ind_tab[1], cannot be referenced individually in an SQL statement. The keyword INDICATOR is optional. The number of structure fields and indicators do not have to match; any extra indicators are unused, and any extra fields do not have indicators assigned to them. |
A scalar indicator variable can also be used in the place of an indicator table to provide an indicator for the first field of the host structure. This is equivalent to having an indicator table with only 1 element. For example:
short scalar_ind;
EXEC SQL SELECT id, name, years, salary
INTO :staff_record INDICATOR :scalar_ind
FROM staff
WHERE id = 10;
If an indicator table is specified along with a host variable instead of a host structure, only the first element of the indicator table, for example ind_tab[0], will be used:
EXEC SQL SELECT id
INTO :staff_record.id INDICATOR :ind_tab
FROM staff
WHERE id = 10;
If an array of short integers is declared within a host structure:
struct tag
{
short i[2];
} test_record;
The array will be expanded into its elements when test_record is referenced in an SQL statement making :test_record equivalent to :test_record.i[0], :test_record.i[1].