SQL/MX 3.2.1 Programming Manual for C and COBOL (H06.26+, J06.15+)

Host Variables in C/C++ Programs
HP NonStop SQL/MX Release 3.2.1 Programming Manual for C and COBOL—663854-005
3-42
Testing for Null or a Truncated Value
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();
...
Retrieving Rows With Nulls
To retrieve a row that contains null, use the NULL predicate in the WHERE clause. You
cannot use an indicator variable set to –1 in a WHERE clause to retrieve a row that
contains null. If you do, NonStop SQL/MX does not find the row and returns a
NOTFOUND exception even if a column actually contains null.
Example
This example retrieves rows that have nulls from the EMPLOYEE table using a cursor.
The cursor specifies the NULL predicate in the WHERE clause in the associated
SELECT statement:
/* Declare a cursor to find rows with null salaries. */
EXEC SQL DECLARE get_null_salary CURSOR FOR
SELECT empnum, first_name, last_name,
deptnum, jobcode, salary
FROM employee
WHERE salary IS NULL;
C
C