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

Host Variables in COBOL Programs
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
4-21
Retrieving Rows With Nulls
See DATEFORMAT Function in the SQL/MX Reference Manual.
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 4-1. Null Test Example
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 PRODUCT-REC.
02 PRODNUM PIC 9(5) COMP.
02 TIMESTAMP-SHIPPED PIC X(26).
02 SHIP-IND PIC S9(4) COMP.
...
* Variable for selecting the product number:
01 MIN-PRODNUM PIC 9(5) COMP.
EXEC SQL END DECLARE SECTION END-EXEC.
* Variable for displaying the timestamp or NULL:
01 VALUE-DISPLAY PIC X(26) VALUE SPACES.
...
* Declare a cursor to perform the SELECT:
EXEC SQL DECLARE get_prodnum CURSOR FOR
SELECT prodnum, timestamp_shipped
FROM sales.products
WHERE prodnum >= :MIN-PRODNUM
END-EXEC.
PROCEDURE DIVISION.
0100-MAIN.
...
EXEC SQL OPEN get_prodnum END-EXEC.
PERFORM 0150-FETCH UNTIL SQLSTATE = "02000".
EXEC SQL CLOSE get_prodnum END-EXEC.
...
0150-FETCH.
EXEC SQL FETCH get_prodnum INTO
:PRODNUM,
DATEFORMAT (:TIMESTAMP-SHIPPED INDICATOR :SHIP-IND, USA)
END-EXEC.
* SQL/MX sets SHIP-IND to less than zero if the column
* contained a null value in the selected row.
IF SHIP-IND < 0 THEN MOVE "NULL" TO VALUE-DISPLAY
ELSE MOVE TIMESTAMP-SHIPPED TO VALUE-DISPLAY.
IF SQLSTATE = "00000" DISPLAY PRODNUM " " VALUE-DISPLAY.
...
COBOL