Host Variable Specification
Host variables serve as a data exchange medium between CONNX Embedded SQL and the application program written in a host language. When used in an SQL statement, a host variable specification has one of the following purposes:
Identify a variable in the host language program which is to receive a value(s) from CONNX Embedded SQL.
Identify a variable in the host language program which is to pass a value(s) to CONNX Embedded SQL.
A host variable is a single variable or structure declared in the host program.
A host variable identifier is used to identify a single host variable or structure from within an SQL statement.
A host variable specification consists of a host variable identifier and an associated optional INDICATOR variable and defines either a single variable, a structure, or an element in a structure.
This section contains the following topics:
Single Variables
The identified single host variable may actually be a single element within a host variable structure. Such a reference is not permitted in ANSI compatibility mode.
A single host variable is identified by a host variable identifier which has the following syntax:
where
host_variable_identifier 1 | Identifies a single variable which is assigned any value but the NULL value. |
host_variable_identifier 2 | Identifies an INDICATOR variable, see INDICATOR Variables below. |
Here is an example showing how to select the price of the cruise with a cruise ID of 5064 into a host variable.
SELECT cruise_price
INTO :host_variable1
FROM cruise
WHERE cruise_id=5064;
INDICATOR Variables
An INDICATOR variable can signify the presence of a NULL value in a host variable assignment or signify that truncation has occurred in a host variable assignment.
If the NULL value is to be assigned to a target host variable specification, then an accompanying INDICATOR variable must be present and is assigned a negative value to signify the NULL value. If the NULL value is to be assigned and the INDICATOR variable is missing, then a runtime error will occur.
The INDICATOR variable must be of a numeric data type with the exception of double precision, real and floating point data types. It must be of the appropriate data type for the host language.
Here is an example showing how to select the cancellation date of Contract 2025 into a host variable. (The column 'date_cancellation' can contain NULL values.)
SELECT date_cancellation
INTO :host_variable1 INDICATOR :host_variable2
FROM contract
WHERE contract_id=2025 ;
If truncation occurred during the assignment of a character string to a host variable, then the INDICATOR variable will show the total number of characters in the originating source prior to truncation:
Indicator Value | Meaning | Host Variable Name |
<0 | indicates NULL value | undefined |
=0 | indicates non-NULL value | actual value |
>0 | number of characters | actual value in originating source |
Host Variable Markers
A dynamic SQL statement can not contain host variables directly. It is, however, possible to provide a dynamic SQL statement after it has been prepared with value parameters at execution time. The dynamic statement must then contain a host variable marker for every host variable specification. A host variable marker is represented by a question mark (?) in the statement's source text. For details, see the section on
Dynamic SQL.
Host Structures
A host structure is a C structure, or a COBOL group that is referenced in an SQL statement. The exact rules to which a host structure must conform are described in the host language sections of the Programming Guide.
where
host_variable_identifier_1 | Identifies a host structure. It can only be specified in the INTO clause of a single row SELECT or FETCH statement. A reference to a host structure is equivalent to a reference to each element in that structure. Each element of the host structure identified by host_variable_identifier_1 is a host variable which is assigned a value, if that value is not the NULL value. |
host_variable_identifier_2 | An INDICATOR structure. An INDICATOR structure is a host structure consisting of elements each identifying an INDICATOR variable. Each element of the INDICATOR structure identified by host_variable_identifier_2 identifies an INDICATOR variable. See INDICATOR Variables in this section. |
The i-th element in the host structure indicated by host_variable_identifier_2 is the INDICATOR variable for the i-th element in the host structure indicated by host_variable_identifier_1.
Note:
Pointer expressions will be supported in the next release version.
Assume that the number of elements in the host structure identified by host_variable_identifier_1 is m and the number of elements in the host structure identified by host_variable_identifier_2 is n:
If m > n, the last m-n elements in the host structure identified by host_variable_identifier_1 do not have an INDICATOR variable.
If m < n, then the last n-m elements in the host structure identified by host_variable_identifier_2 are ignored.
Examples:
If two host structures have been declared, one for actual returned values and one for indicator values, and the variables 'struct1' and 'indicator1' identify these structures respectively, then the following syntax shows how values from a derived column list are entered into host variables (assuming that the host structures match the derived columns).
SELECT cruise_identifier,start_date,cruise_price
INTO :struct1 INDICATOR :indicator1
FROM cruise;
The following example inserts a resulting value from a query into a particular 'Element' of a defined structure. 'struct1' is a structure identifier that contains an element identified by 'price_element' and 'indicator1' is a structure identifier that contains the element identified by 'price_ind'.
SELECT cruise_price
INTO :struct1.price_element INDICATOR :indicator1.price_ind
FROM cruise;
