SQL Programming Manual for TAL

Dynamic NonStop SQL Operations
HP NonStop SQL Programming Manual for TAL—527887-001
7-37
Handling Null Values
Allocating Memory for a Possible Null Value
You can allocate memory for indicator variables at the same time you allocate memory
for other values. If NULL^INFO is -1, you allocate a buffer for the indicator value and
assign its address to the IND^PTR field of the appropriate SQLVAR entry. This
example shows code for this allocation. The statements take place within a FOR loop
to handle each input parameter or output column.
--After allocating memory for the data value and assigning
--the address of the memory to VAR^PTR:
IF sqlda^ptr.sqlvar[i].null^info = -1 THEN
BEGIN
sqlda^ptr.sqlvar[i].IND^PTR :=
GETPOOL (pool^head, $DBL(2) ); -- Get 2 bytes
--for the indicator variable
IF sqlda^ptr.sqlvar[i].IND^PTR = -1D THEN
--(print error message and call ABEND)
END;
Handling Null Values in Input Parameters
If your program is to handle null values on input, each parameter in the statement
entered by the user or constructed by your program must have a corresponding
indicator parameter, or a run-time error will occur when a null value is encountered.
After DESCRIBE INPUT executes and for each input parameter described in an
SQLVAR array in the input SQLDA, SQL 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).
If NULL^INFO contains a value of -1 and you are allocating memory dynamically, you
can now allocate 2 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, you assign a -1 to the location
pointed to by IND^PTR. NonStop SQL checks this value and assumes a null value for
the parameter.
If instead the user does not enter a null value for the input parameter, you can assign a
0 to the location pointed to by IND^PTR. NonStop SQL checks IND^PTR, sees that
IND^PTR indicates a nonnull value, and gets the parameter value from the location
pointed to by VAR^PTR.
This example handles a possible null value in an input parameter. In this example, the
user is supposed to type in a question mark to signify a null value. The datatype-field
refers to the equivalenced data type field in the SQL^TYPES structure on which
PARAM^ is based.
--Variable declarations:
INT .EXT ind^; --pointer used to set null indicator