SQL/MP Programming Manual for C

Host Variables

HP NonStop SQL/MP Programming Manual for C—429847-008

2-17

Using Indicator Variables for Null Values

A null value in an SQL column indicates that the value is either unknown for the row or

is not applicable to the row. A program inserts a null value or tests for a null value

using an indicator variable. An indicator variable is a 2-byte integer variable 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.

A program can use an indicator variable associated with a host variable:



To insert 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 values into a database with an INSERT or UPDATE statement, a program

sets the indicator variable to less than zero (0) for a null value or zero (0) for a nonnull

value before executing the statement. This statement inserts values into the ODETAIL

table. The columns UNIT_PRICE and QTY_ORDERED allow null values.

EXEC SQL INSERT INTO =odetail

(ordernum, partnum, unit_price, qty_ordered)

VALUES ( :odetail.ordernum,

:odetail.partnum,

:odetail.unit_price :odetail.unit_price_i,

:odetail.qty_ordered :odetail.qty_ordered_i );

Testing For a Null Value

To test for a null value, a program tests the indicator variable associated with a host

variable. This example selects values from the ODETAIL table and returns the values

to host variables. After the SELECT statement runs, the example tests the indicator

variable for a null value. If the value of the indicator variable is less than 0, the

associated column contains a null value.

EXEC SQL SELECT ordernum, partnum, unit_price, qty_ordered

INTO :odetail.ordernum,

:odetail.partnum,

:odetail.unit_price INDICATOR :odetail.unit_price_i,

:odetail.qty_ordered INDICATOR

:odetail.qty_ordered_i,

FROM sales.odetail

WHERE ordernum = 300380 AND partnum = 2402 ;

...

if ((odetail.unit_price_i < 0) ||

(odetail.qty_ordered_i < 0))

handle_null_value();