Using Host Variables

You use host variables in SQL commands as follows:

To pass data values, when using the following data manipulation commands:

     SELECT

     INSERT

     DELETE

     UPDATE

     FETCH

     DELETE WHERE CURRENT

     UPDATE WHERE CURRENT

To hold null value indicators in four data manipulation commands:
SELECT INSERT FETCH UPDATE

To pass dynamic commands at runtime, achieved by using the following commands:
PREPARE EXECUTE IMMEDIATE
To hold savepoint numbers, which are used in the following commands:
SAVEPOINT ROLLBACK WORK TO
To hold messages from the ALLBASE/SQL message catalog, obtained by using the SQLEXPLAIN command.

This chapter provides examples illustrating where, in the commands itemized above, the SQL syntax supports host variables. This chapter also takes a brief look at two special cases:

Using host variables in subprogram units.
Using host variables in conjunction with data in batch files.

Some of the examples are numbered so later in this chapter, under "Declaring Host Variables", you can quickly find declaration examples for the same host variables.

Host Variable Names

Host variable names in FORTRAN programs must:

Contain from 1 to 30 ASCII characters.
Conform to the rules for ALLBASE/SQL basic names.
Contain only characters chosen from the following set: the 26 letters of the ASCII alphabet, the 10 decimal digits, or an underscore (_).
Begin with an alpha character but not the prefix EXEC SQL.
Not begin or end with a hyphen.
Not be the same as any ALLBASE/SQL or FORTRAN reserved word.

In all SQL commands containing host variables, the host variable name must be preceded by a colon:

   :HostVariableName

Input and Output Host Variables

Host variables can be used for input or for output:

Input host variables provide data for ALLBASE/SQL.
Output host variables contain data from ALLBASE/SQL.

When using an input host variable, you must initialize it before using it.

Data Values and Null Indicators

Host variables containing data values can be input or output host variables. In the following SELECT command, the INTO clause contains two output host variables: PartNumber and PartName; ALLBASE/SQL puts data from the PurchDB.Parts table into these host variables. The WHERE clause contains one input host variable: PartNumber; ALLBASE/SQL reads data from this host variable to determine which row to retrieve.

    EXEC SQL SELECT   PartNumber, PartName

   1           INTO  :PartNumber,

   2                 :PartName

   3           FROM   PurchDB.Parts

   4           WHERE  PartNumber = :PartNumber

In this example, one host variable, PartNumber, is used for both input and output.

Indicator Variables

Host variables that contain null value indicators are called indicator variables. Indicator variables are used in SELECT, FETCH, and INSERT commands to identify null values, and in SELECT and FETCH commands to identify truncated output strings.

In SELECT and FETCH commands, an indicator variable is an output host variable containing one of the following indicators, which describe the data ALLBASE/SQL returns:

    0    value is not null

   -1    value is null

   >0    string value is truncated; number indicates string

         length before truncation.

In the INSERT command, an indicator variable is an input host variable. You put one of the following indicators into the indicator variable to tell ALLBASE/SQL when to insert a null value in a column:

   >=0    is not null

   <0     value is null

An indicator variable must appear in an SQL command immediately after the host variable whose data it describes. The following SELECT command uses an indicator variable, PartNameInd, for data from the PartName column. When this column contains a null value, ALLBASE/SQL puts a negative number into PartNameInd:

    EXEC SQL SELECT  PartNumber, PartName

   1           INTO :PartNumber,

   2                :PartName :PartNameInd

   3           FROM  PurchDB.Parts

   4           WHERE PartNumber = :PartNumber

Any column not defined with the NOT NULL attribute may contain null values. In the PurchDB.Parts table, ALLBASE/SQL prevents the PartNumber column from containing null values, because it was defined as NOT NULL. In the other two columns, however, null values may occur:

    CREATE PUBLIC TABLE PurchDB.Parts

   1  (PartNumber      CHAR(16)     NOT NULL,

   2   PartName        CHAR(30),

   3   SalesPrice      DECIMAL(10,2))

   4   IN WarehFS

Null values have certain properties that you need to remember when manipulating data that may be null. For example, ALLBASE/SQL ignores columns or rows containing null values when evaluating an aggregate function (except that COUNT(*) includes all null values). Refer to the ALLBASE/SQL Reference Manual for a complete account of the properties of null values.

Be sure to use an indicator variable in the SELECT and FETCH commands whenever columns accessed may contain null values. A runtime error results if ALLBASE/SQL retrieves a null value and the program contains no indicator variable.

You can use an indicator variable to detect truncated strings in the SELECT and FETCH commands. In the SELECT command illustrated above, PartNameInd contains a value greater than zero (>0) when a part name is too long for the host variable declared to hold it. The value in PartNameInd indicates the actual length of the string before truncation.

Using Host Variables

Technical documentation

» Table of Contents

» Index

Host Variable Names

Input and Output Host Variables

Data Values and Null Indicators

Indicator Variables