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-15
Inserting Rows From Rowset Arrays
Move "PROGRAM MANAGER" to hvaprojdesc[2]
Move 300 to hvajobcode[3]
Move "QUALITY SUPERVISOR" to hvaprojdesc[3]
Move 400 to hvajobcode[4]
Move "TECHNICAL OFFICER" to hvaprojdesc[4]
Move 500 to hvajobcode[5]
Move "EXECUTIVE OFFICER" to hvaprojdesc[5]
EXEC SQL INSERT INTO job (jobcode, jobdesc)
VALUES (:hvajobcode, :hvajobdesc) END-EXEC.
...
Using Arrays in Expressions and Functions
Unless the arrays are in an INTO clause or in a rowset-derived table, on rowsets use:
•
Numeric value expressions and functions
•
Character value expressions and functions
The guideline is, wherever you can use a host variable in an expression or function,
you can use a rowset array if it is for input (for example, WHERE clause, VALUES
clause, SET clause).
When array expressions involve binary arithmetic operators, the two possibilities for
the operand types are:
•
Array and a constant. For example, an array multiplied by a constant. The
semantics are that every element in the array is multiplied by the constant.
•
Array and an array. For example, an array multiplied by another array. In this case,
both arrays have to be the same length. The semantics are that the first element of
array1 is multiplied with the first element of array2, second element of array1 by
second element of array2, and so on.
Inserting Null
You can insert multiple rows of data with a null value for one of the columns in some of
the rows by using an indicator host variable array.
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. They use –
1 value as the null indicator. For the remaining 50 rows, they set the SALARY column
to nonnull values. The indicator host variable array must contain the value 0 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;
C