SQL/MP Programming Manual for COBOL

Host Variables
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
2-11
Using Indicator Variables for Null Values
Declare date-time values as character data types and then use the TYPE AS clause to
direct SQL/MP to interpret the value in the host variable as a date-time or INTERVAL
value. Sample TYPE AS clauses are:

TYPE AS DATETIME YEAR TO HOUR

TYPE AS DATE

TYPE AS TIME

TYPE AS TIMESTAMP

TYPE AS INTERVAL YEAR
You can insert or retrieve date-time values in any of three formats, independently of the
SQL column definition. For example, you can specify formats such as 06/15/1996,
1996-06-15, or 15.06.1996. You must declare the host variable size to be consistent
with the format you will use. You then control the display format by retrieving the value
by using the DATEFORMAT function.
Using Indicator Variables for Null Values
A null value in an SQL column indicates that a value is either unknown for the row or is
not applicable to the row. If a column allows null values, a program can use an
indicator variable to set or receive the column value. An indicator variable is a two-byte
integer variable, defined in the Declare Section, associated with the host variable that
sets or receives the actual column value.
The INVOKE directive automatically declares indicator variables for columns defined to
allow null values. For information, see Using Indicator Variables With the INVOKE
Directive on page 2-22.
To send a value to SQL/MP for insertion, update, or comparison, a program sets the
indicator variable to less than zero (0) for a null value or zero (0) for a nonnull value.
When it returns a value that allows a null value to a program, SQL/MP sets the
indicator variable to less than zero (0) for a null value or zero (0) for a nonnull value.
A program can use indicator variables to perform these operations:

To insert null values into a database with an INSERT or UPDATE statement

To test for a null value after retrieving a value from a database with a SELECT
statement
Inserting a Null Value
To insert a null value, the program sets the indicator variable to a value less than 0 for
the column receiving the null value before executing the INSERT statement. This
INSERT statement uses an indicator variable to insert a null value into the RETIREES
table:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 RETIREE-REC.
02 EMPNUM PIC 9(5) COMP.
02 RETIRE-DATE PIC X(10).