SQL/MX 3.2.1 Programming Manual for C and COBOL (H06.26+, J06.15+)

Static Rowsets
HP NonStop SQL/MX Release 3.2.1 Programming Manual for C and COBOL—663854-005
7-5
Specifying Rowset Arrays
INDICATOR
is an optional keyword that precedes indicator-array-name.
indicator-array-name
is an indicator variable array of exact numeric data type. This data type is short in
C or PIC 9(4)comp in COBOL. You must precede indicator-array-name
with a colon (:) in an SQL statement.
If data returned in the host variable array for a particular row and column is null, the
corresponding indicator variable is set to –1. If character data returned is
truncated, the indicator variable is set to the length of the string in the database.
Otherwise, the value of the indicator variable is zero. To insert null into the
database, set the indicator variable to a value less than zero for a particular row
and column in the corresponding host variable array. For inserting nonnull values,
the corresponding indicator variable must be set to zero. This last rule is also true
for all input arrays (for example, those used in WHERE and SET clauses). You
generate a run-time error if you specify a positive value in an indicator for input.
Example
This example retrieves three columns of, at most, 200 rows of a table. The salary
column can be null, and the salary array is followed by an indicator array:
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
ROWSET [200] char hva_first_name[16];
ROWSET [200] char hva_last_name[21];
ROWSET [200] unsigned NUMERIC (8,2) hva_salary;
ROWSET [200] short hva_salary_indicator;
...
EXEC SQL END DECLARE SECTION;
...
EXEC SQL
SELECT first_name, last_name, salary
INTO :hva_first_name, :hva_last_name,
:hva_salary INDICATOR :hva_salary_indicator
FROM persnl.employee;
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 sqlstate pic x(5).
01 rs.
02 ROWSET[200] hvafirstname pic x(15).
02 ROWSET[200] hvalastname pic x(20).
02 ROWSET[200] hvasalary pic 9(8)v9(2) comp.
02 ROWSET[200] hvasalaryindicator pic 9(5).
EXEC SQL END DECLARE SECTION END-EXEC.
...
EXEC SQL
SELECT first_name, last_name, salary
INTO :hvafirstname, :hvalastname,
:hvasalary INDICATOR :hvasalaryindicator
C
COBOL