SQL Programming Manual for Pascal

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for Pascal—528614-001
3-26
INVOKE and Indicator Variables
The invoked type in the Pascal program is:
TYPE
EMPLOYEE_TYPE = RECORD
NAME : FSTRING(20);
AGE : FSTRING(6);
END ;
The host variable reference is:
:AGE TYPE AS INTERVAL YEAR(2) TO MONTH
INVOKE and Indicator Variables
INVOKE generates indicator variables 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 or suffix (or both) if you specify them.
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
INVOKE and Null Values
For columns that can be null according to their table definition, the INVOKE directive
produces indicator variables that your program can use to specify whether or not a
column value is null.
Example
In the next example, INVOKE generates a record description 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 table is created with this CREATE TABLE statement:
EXEC SQL CREATE TABLE EMPTBL
( EMPNUM NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
FIRST_NAME CHARACTER(15),
Note. if the column name is 30 or 31 characters long and the default indicator suffix _I is used,
the indicator variable name becomes the same as the corresponding host variable because the
suffix _I is truncated. This similarity causes an SQL error at compile time. To avoid this error,
specify the PREFIX or NULL STRUCTURE clause when column names are 30 or 31
characters long.