HP 3000 Manuals HP 3000 Manuals
Using Host Variables
Host variables are used in SQL commands as follows:
* 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.
Later in this section are examples illustrating where, in the commands
itemized above, the SQL syntax supports host variables.
Host Variable Names
ALLBASE/SQL host variable names in COBOL programs must conform to the
following rules:
* Contain from 1 to 30 bytes.
* Conform to the rules for ALLBASE/SQL basic names.
* Contain characters chosen from the following set: the 26 letters
of the ASCII alphabet, the 10 decimal digits, a hyphen (-), or
valid characters for any native language you are using.
* Begin with an alphabetic character, although the prefix SQL is not
recommended.
* Not begin or end with a hyphen.
* Not be the same as any ALLBASE/SQL or COBOL reserved word.
In all SQL commands containing host variables, the host variable name
must be preceded by a colon:
:HostVariableName
The COBOL preprocessor converts hyphens in host variable names to
underscores (-) because ALLBASE/SQL names cannot contain hyphens. Thus,
when you use a host variable name in conjunction with a minus sign, be
sure to leave one intervening space between them:
:NEWSALESPRICE - :OLDSALESPRICE
^ ^
|___|_ Leave at least one blank here!
NOTE Even though hyphens are allowed in host variable names, they are
not allowed in column names or names of other ALLBASE/SQL objects.
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.
Be sure to initialize an input host variable before using it. When using
cursor operations with the SELECT command, initialize the input host
variables 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 END-EXEC.
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 END-EXEC.
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 END-EXEC.
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) END-EXEC.
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.