HP 3000 Manuals HP 3000 Manuals
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.