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

Host Variables in C/C++ Programs
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
3-31
Retrieving Rows With Nulls
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;
...
EXEC SQL OPEN get_null_salary ;
...
EXEC SQL FETCH get_null_salary
INTO :hv_empnum,
:hv_first_name,
:hv_last_name,
:hv_deptnum,
:hv_jobcode,
:hv_salary ;
/* Process the row that contains the null salary. */
/* Branch back to FETCH the next row. */
...
EXEC SQL CLOSE get_null_salary ;
...
Creating C Host Variables Using INVOKE
The INVOKE preprocessor directive creates a structure with the names of the host
variables corresponding to columns in a table or view. INVOKE converts the column
names to C identifiers and generates a C declaration for each column. If a column
allows null, INVOKE also creates an indicator variable for the column.
You can declare host variables that correspond to the columns in an SQL table or view
without using an INVOKE statement. However, using an INVOKE statement to
generate host variables has these advantages:
•
Program independence: If you modify a table or view, the INVOKE statement re-
creates the host variables to correspond to the new table or view when you run the
SQL/MX C preprocessor. However, you must modify a program that refers to a
deleted column or accesses a new column.
•
Performance: The INVOKE statement maps SQL data types to the corresponding
host language data types, and usually no data conversion is required at run time.
C