SQL/MX Programming Manual for C and COBOL (G06.24+, H06.03+)

Host Variables in C/C++ Programs

HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004

3-30

Inserting Null

To insert values into columns that allow null with an INSERT or UPDATE statement,

you must set the indicator variable to a value less than zero for null or zero for a

nonnull value before executing the statement.

Example

This example uses a statement that inserts values into the ODETAIL table. The

columns UNIT_PRICE and QTY_ORDERED allow null. To insert null, declare and use

an indicator variable:

EXEC SQL BEGIN DECLARE SECTION;

...

short ind_1 = -1;

EXEC SQL END DECLARE SECTION;

...

EXEC SQL INSERT INTO sales.odetail

(ordernum, partnum, unit_price, qty_ordered)

VALUES ( :hv_ordernum, :hv_partnum,

:hv_unit_price :ind_1,

:hv_qty_ordered :ind_1 );

Testing for Null or a Truncated Value

To test for null or a truncated character value, check the indicator variable associated

with a host variable. If the value of the indicator variable is less than zero, the

associated column contains null. If the value of the indicator variable is greater than

zero, character data in the column was truncated when it was assigned to the host

variable.

Example

This example selects values from the PARTS table and returns these values to host

variables. The columns PARTDESC and QTY_AVAILABLE allow null. After the

SELECT statement executes, the example tests the indicator variable for null or a

truncated value:

...

EXEC SQL SELECT partnum, partdesc, price, qty_available

INTO :hv_partnum,

:hv_partdesc

INDICATOR :hv_partdesc_i,

:hv_price,

:hv_qty_available

INDICATOR :hv_qty_available_i,

FROM sales.parts

WHERE partnum = :in_partnum;

...

if (hv_qty_available_i < 0) handle_null_value();

if (hv_partdesc_i > 0 ) handle_truncated_value();

...