Using Host Variables

• To pass data values with the following data manipulation commands:

  SELECT INSERT DELETE UPDATE DECLARE FETCH REFETCH UPDATE WHERE CURRENT

• To hold null value indicators in these data manipulation commands:

  SELECT INSERT FETCH REFETCH UPDATE UPDATE WHERE CURRENT

• In queries to indicate string truncation and the string length before truncation.

• To identify the starting row and the number of rows to process in the INTO clause of the following commands:

  BULK SELECT BULK INSERT

• To pass dynamic commands at run time with the following commands:

  PREPARE EXECUTE IMMEDIATE

• To hold savepoint numbers, which are used in the following commands:

  SAVEPOINT ROLLBACK WORK TO :savepoint

• To hold messages from the ALLBASE/SQL message catalog, obtained by using the SQLEXPLAIN command.

• To hold a DBEnvironment name in the CONNECT command.

Host Variable Names

ALLBASE/SQL host variable names in Pascal programs must:

In all SQL commands containing host variable syntax, 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:

Be sure to initialize an input host variable before using it. When using cursor operations with the SELECT command, initialize the input host variables contained in the select list and WHERE clause before you execute the OPEN command.

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

              INTO  :PartNumber,

                    :PartName

              FROM   PurchDB.Parts

              WHERE  PartNumber = :PartNumber;

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

Indicator Variables

A special type of host variable called an indicator variable, is used in SELECT, FETCH, UPDATE, UPDATE WHERE CURRENT, and INSERT commands to identify null values and in SELECT and FETCH commands to identify truncated output strings.

An indicator variable must appear in an SQL command immediately after the host variable whose data it describes. The host variable and its associated indicator variable are not separated by a comma. In SELECT and FETCH commands, an indicator variable is an output host variable containing one of the following indicators, which describe data ALLBASE/SQL returns:

    0    value is not null

   -1    value is null

   >0    string value is truncated; number indicates data length

             before truncation.

In the INSERT, UPDATE, and UPDATE WHERE CURRENT commands, an indicator variable is an input host variable. The value you put in the indicator variable tells ALLBASE/SQL when to insert a null value in a column:

   >=0    value is not null

    <0    value is null

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 -1 into PartNameInd:

   EXEC SQL SELECT  PartNumber, PartName

              INTO :PartNumber,

                   :PartName :PartNameInd

               FROM PurchDB.Parts

              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 are allowed:

   CREATE PUBLIC TABLE PurchDB.Parts

     (PartNumber      CHAR(16)     NOT NULL,

      PartName        CHAR(30),

      SalesPrice      DECIMAL(10,2) );

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.

An indicator variable will also detect truncated strings in the SELECT and FETCH commands. In the SELECT command illustrated above, PartNameInd contains a value >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.

Bulk Processing Variables

Bulk processing variables can be used with the BULK option of the SELECT or the INSERT command.

When used with the BULK SELECT command, two input host variables may be named following the array name in the INTO clause to specify how ALLBASE/SQL should store the query result in the array:

   INTO :ArrayName [,:StartIndex [,:NumberOfRows]]

The StartIndex value denotes at which array element the query result should start. The NumberOfRows value is the maximum, total number of rows ALLBASE/SQL should put into the array:

   EXEC SQL BULK SELECT  PurchasePrice * :Discount,

                         OrderQty,

                         OrderNumber

                   INTO :OrdersArray,

                        :FirstRow,

                        :TotalRows

                   FROM  PurchDB.OrderItems

                  WHERE  OrderNumber

                         BETWEEN :LowValue AND :HighValue

               GROUP BY  OrderQty, OrderNumber;

ALLBASE/SQL puts the entire query result, or the number of rows specified in TotalRows, whichever is less, into the array named OrdersArray, starting at the array subscript stored in FirstRow. If neither of these input host variables is specified, ALLBASE/SQL stores as many rows as the array can hold, starting at OrdersArray[1]. If FirstRow plus TotalRows is greater than the size of the array, a runtime error occurs and the program aborts.

Bulk processing variables may be used with the BULK INSERT command to direct ALLBASE/SQL to insert only certain rows from the input array:

   EXEC SQL BULK INSERT INTO   PurchDB.Orders

                      VALUES (:OrdersArray,

                              :FirstRow,

                              :TotalRows);

If a starting index or total number of rows is not specified, ALLBASE/SQL inserts, starting at the beginning of the array, as many rows as there are elements in the array.