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-25
Using the Index Identifier
EXEC SQL END DECLARE SECTION END-EXEC.
...
Move 5 to outputsize
EXEC SQL ROWSET FOR OUTPUT SIZE :outputsize
SELECT jobcode, jobdesc
INTO :hvajobcode, :hvajobdesc
FROM job
END-EXEC.
...
Using the Index Identifier
Use the index (or row) identifier to indicate which row of the input rowset array in the
WHERE clause caused a row to be part of the output rowset array. When you use a
zero-based index, the values of the index identifier range from 0 to n-1, where n is
the number of elements in the WHERE clause rowset array. This strategy might not
work as well for a COBOL application where host language array indexing starts from
1. In the next COBOL example, you can add 1 to all the index identifier values by using
an arithmetic expression in the select list.
To use the index identifier, you can declare a host variable array, whose size is at least
as large as the other output host variable arrays in the SQL statement in the
DECLARE section. You can then use a SELECT (or FETCH) operation into this host
variable array after including the index identifier (row_id in the next example) in the
list of columns to be retrieved.
You can also use a cursor declaration to use the index identifier by using the general
DECLARE CURSOR syntax shown on page 7-23.
Example
This example selects the EMPNUM column of all rows in the EMPLOYEE table where
the JOBCODE value is equal to one of the values in the hva_jobcode host variable
array. The SELECT statement is executed for each matching job code. The row
identifier indicates which element of the host variable array selects the corresponding
row from the EMPLOYEE table:
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
ROWSET[5] unsigned NUMERIC (4) hva_jobcode;
ROWSET [100] unsigned NUMERIC (4) hva_empnum;
ROWSET [100] short hva_row_id;
...
long numrows;
EXEC SQL END DECLARE SECTION;
long i;
...
/* Populate the jobcode rowset in some way. */
hva_jobcode[0] = 100;
hva_jobcode[1] = 200;
hva_jobcode[2] = 350; /* Does not exist. */
hva_jobcode[3] = 400;
hva_jobcode[4] = 500;
C