HP NonStop SQL/MP

Host Variables
HP NonStop SQL/MP Programming Manual for C—429847-008
2-18
Retrieving Rows With Null Values
else display_result();
...
Retrieving Rows With Null Values
You can use an indicator variable to insert null values into a database or to test for a
null value after you retrieve a row. However, you cannot use an indicator variable set to
–1 in a WHERE clause to retrieve a row that contains a null value. In this case,
NonStop SQL/MP does not find the row and returns an sqlcode of 100, even if a
column actually contains a null value.
To retrieve a row that contains a null value, use the NULL predicate in the WHERE
clause. For example, to retrieve rows that have null values from the EMPLOYEE table
using a cursor, specify the NULL predicate in the WHERE clause in the associated
SELECT statement when you declare the cursor:
/* 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
:employee_record.empnum,
:employee_record.first_name,
:employee_record.last_name,
:employee_record.deptnum,
:employee_record.jobcode,
:employee_record.salary ;
/* Test SQLCODE. */
/* Process the row that contains the null salary. */
/* Branch back to FETCH the next row. */
...
EXEC SQL CLOSE get_null_salary ;
Creating Host Variables Using the INVOKE
Directive
The INVOKE directive creates host variables that correspond to columns in an SQL
table or view. INVOKE converts the column names to C identifiers and generates a
C declaration for each column. When a column allows null values, INVOKE also
creates an indicator variable for the column. For views only, INVOKE includes the
system-defined primary keys in the definition. You can use a class MAP DEFINE name
for a table or view name in an INVOKE directive, but not for a record name.