Host variables serve as a data exchange medium between Adabas SQL Gateway 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:

• to identify a variable in the host language program which is to receive a value(s) from Adabas SQL Gateway Embedded SQL.

• to identify a variable in the host language program which is to pass a value(s) to Adabas SQL Gateway 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

• INDICATOR Variables

• Host Variable Markers

• Host Structures

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:

 

 

 

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.

 

Example:

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 serve as one of two purposes:

• Signifies the presence of a NULL value 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.

 

Example:

Select the cancellation date of Contract 2025 into a host variable. (The column 'date_cancellation' could contain NULL values)

SELECT date_cancellation

    INTO :host_variable1 INDICATOR :host_variable2

        FROM contract

            WHERE contract_id=2025 ;

• Signifies that truncation has occurred in a host variable assignment. 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.

SUMMARY:
 

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.

 

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 also 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 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, then 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;