SQL/MP Programming Manual for C

Dynamic SQL Operations
HP NonStop SQL/MP Programming Manual for C—429847-008
10-17
Null Values
If you want all your parameters and output variables to handle null values, your
program should access ind_ptr every time it accesses var_ptr.
Handling Null Values in Input Parameters
A program uses an indicator parameter to indicate that a null value was entered for a
parameter. The indicator parameter follows the parameter in the SQL statement; for
example:
INSERT INTO =employee VALUES (1000, ?p INDICATOR ?i );
If a user enters a null value for ?p, the program should set ?i to a value less than zero.
If a user enters a non-null value for ?p, the program should set ?i to 0. Both ?p and ?i
are in the names buffer, so the program can prompt the user for a null value.
Each parameter in the statement entered by the user or constructed by your program
must have a corresponding indicator parameter to handle possible null values, or a
run-time error occurs when a null value is encountered.
After DESCRIBE INPUT runs and for each input parameter described in an sqlvar
array in the input SQLDA structure, NonStop SQL/MP sets null_info to -1 if the
input parameter in the prepared statement allows a null value (that is, if the prepared
statement included a null indicator).
Your program then checks null_info. If null_info contains a -1 and you are
allocating memory dynamically, you can now allocate two bytes of memory for a null
indicator value, and then set ind_ptr to point to the memory. Allocate this memory at
the same time you allocate memory for a possible nonnull parameter value.
If the user specifies a null value for the parameter, assign a -1 to the location pointed to
by ind_ptr. NonStop SQL/MP checks this value and then transmits a null value for
the parameter.
However, if the user does not enter a null value for the input parameter, you can assign
a 0 to the location indicated by ind_ptr. NonStop SQL/MP checks ind_ptr, sees
that ind_ptr indicates a nonnull value, and gets the parameter value from the
location indicated by var_ptr.
Handling Null Values in Output Variables
DESCRIBE sets null_info to -1 if the output variable can be null (that is, if the
prepared statement includes a null indicator). If the value returned is null, NonStop
SQL/MP checks null_info and moves a -1 into the location pointed to by ind_ptr.
(Errors are returned if the value is null but null_info is zero (0) or if ind_ptr is an
invalid address.)
Your program must check null_info to determine whether the value returned can be
null. If null_info contains a -1, then your program checks the location pointed to by
ind_ptr. If that location contains a -1, then a null value was returned. If the location
contains 0, then a nonnull value was returned and your program should get the value
from the location pointed to by var_ptr.