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

Static Rowsets
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
7-36
Inserting Null
01 sqlstate pic x(5).
01 rs.
02 ROWSET[10] hvajobcode pic 9(4) comp.
02 ROWSET[10] hvajobdesc pic x(18).
EXEC SQL END DECLARE SECTION END-EXEC.
...
***** Populate the first five rows in some way *****
EXEC SQL INSERT INTO persnl.job
SELECT jobcode, jobdesc
FROM ROWSET 5(:hvajobcode, :hvajobdesc)
AS rs(jobcode, jobdesc) END-EXEC.
...
Inserting Null
Use an indicator host variable array in a rowset-derived table to insert multiple rows of
data with a null value for one of the columns in some of the rows.
Example
This example inserts 100 rows into the EMPLOYEE table and sets the SALARY
column to null for the first 50 rows by using an indicator host variable array in a rowset-
derived table. The null indicator is -1. For the remaining 50 rows, the SALARY column
is set to nonnull values. The indicator host variable array must contain the value 0
(zero) for these rows:
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
ROWSET [100] unsigned NUMERIC (4) hva_empnum;
ROWSET [100] char hva_first_name[16];
ROWSET [100] char hva_last_name[21];
ROWSET [100] unsigned NUMERIC (4) hva_deptnum;
ROWSET [100] unsigned NUMERIC (4) hva_jobcode;
ROWSET [100] unsigned NUMERIC (4) hva_salary;
ROWSET [100] short hva_salary_indicator;
...
EXEC SQL END DECLARE SECTION;
...
/* Populate the host variable arrays in some way. */
...
/* Store -1 in the indicator array for the first 50 input
values. */
for (i = 0; i < 50; i++) hva_salary_indicator[i] = -1;
/* Store 0 in the indicator array for the next 50 input values.
It is assumed that there are valid values for salary in the
hva_salary rowset array from element no. 50 up to element no. 99
*/
for (i = 50; i < 100; i++) hva_salary_indicator[i] = 0;
EXEC SQL
INSERT INTO persnl.employee
SELECT (empnum,first_name,last_name,deptnum,jobcode,salary)
FROM
ROWSET(:hva_empnum,:hva_first_name,
:hva_last_name,:hva_deptnum,:hva_jobcode,
C