Declaring Host Variables

Variables used in SQL commands are known as host variables. All host variables used in either a main program unit or a subprogram unit must be declared in the Type Declaration Section of the program unit where the host variable is used. The host variable declarations must be the last declarations in the Type Declaration Section of the program unit which contains the embedded SQL statements and must appear between the two following SQL commands:

     EXEC SQL BEGIN DECLARE SECTION

      .

      .  Host variables are declared here

      .  in FORTRAN variable type descriptions.

      .

     EXEC SQL END DECLARE SECTION

In Figure 3-1, host variable declarations start at 2.

You cannot put more than one such declaration section in a program unit. In addition, you must include the BEGIN DECLARE SECTION and END DECLARE SECTION commands even if there are no host variables used in the embedded SQL commands. This is because the preprocessor includes the variable declaration file after the END DECLARE SECTION command.

The SELECT command shown at 8 in Figure 3-1 uses three host variables for data, one for each of the columns in the PurchDB.Parts table. When used in an embedded SQL command, host variables are preceded with a colon:

   :PartNumber

   :PartName

   :SalesPrice

Because these host variables are for data from a DBEnvironment, the variable types must be compatible with the ALLBASE/SQL data stored in the respective columns:

   CHARACTER*16           PartNumber

   CHARACTER*30           PartName

   DOUBLE PRECISION       SalesPrice

   SQLIND                 SalesPriceInd

The host variable named SalesPrice is accompanied by a second host variable, SalesPriceInd. Known as an indicator variable, this host variable is used to detect null values. If column SalesPrice contains a null value, ALLBASE/SQL returns a negative number to SalesPriceInd. The subprogram unit named DisplayRow examines the value in this indicator variable to determine whether to display the announcement that a Sales Price is null or display the value in SalesPrice.

Indicator variables are declared at 2 in Figure 3-1 as SQLIND. SQLIND is a special declaration reserved for indicator variables.