SQL Programming Manual for TAL

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for TAL—527887-001
3-27
INVOKE
The statements to supply values for the columns are:
billings.billing^date ':='"1988-10-20";
billings.time^before^pmt ':=' "120";
...
EXEC SQL
INSERT INTO billings VALUES
("923", DATE "1985-10-15",
:billing^date TYPE AS DATE,
:time^before^pmt TYPE AS INTERVAL DAY(3) );
...
Using Indicator Variables for Null Values
INVOKE generates indicator variables of TAL type INT for each host variable that
corresponds to a column that can be null. The format of the indicator variable is the
name of the corresponding column, plus a prefix and or suffix, if you specify one.
For example, if the column name is RETIRE^DATE, the format of the indicator variable
is RETIRE^DATE^I. You can specify a suffix other than the ^I provided by SQL by
supplying a SUFFIX clause with the INVOKE statement. You can replace the suffix with
a prefix of your choosing by specifying the PREFIX clause. Examples appear on page
3-29.
If you specify NULL STRUCTURE, INVOKE produces a substructure that contains the
indicator variable and the column. For more information about the NULL STRUCTURE
clause, see Using the NULL STRUCTURE Clause on page 3-28.
For a column allows a null value, the INVOKE directive generates an indicator variable
that your program can use to specify whether or not a column value is null. In this
example, INVOKE generates a structure for a table definition that allows null values in
the FIRST^NAME and RETIRE^DATE columns. The generated structure type includes
indicator variables with the default suffix ^I for FIRST^NAME and RETIRE^DATE.
The INVOKE directive as it appears in the TAL source program is:
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL INVOKE emptbl AS emptbl^rec;
EXEC SQL END DECLARE SECTION;
The CREATE TABLE statement that defines the table is:
CREATE TABLE emptbl
( empnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
first^name CHARACTER(15),
Note. If a column name is 30 or 31 characters long and the default indicator suffix ^I is used,
the ^I is truncated, and the indicator variable name is identical to the corresponding host
variable name. To prevent this error, specify the PREFIX or NULL STRUCTURE clause when
column names are 30 or 31 characters long.