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-32
Selecting From Rowset-Derived Tables With a
Cursor
...
***** Populate the rowset in some way *****
Move 244 TO hvapartnum(1)
Move 2001 TO hvapartnum(2)
Move 2403 TO hvapartnum(3)
Move 5103 TO hvapartnum(4)
Move 6301 TO hvapartnum(5)
EXEC SQL
SELECT partnum INTO :rowidpartnum
FROM ROWSET(:hvapartnum)
KEY BY row_id AS rs(partnum, row_id)
WHERE row_id+1 = 5 END-EXEC.
***** Process the selected element of the table in some way ****
Display "Part Nbr " rowidpartnum.
...
Selecting From Rowset-Derived Tables With a Cursor
Declare cursors with rowset-derived tables and use them to fetch rows from the
database. This strategy has an advantage over direct use of rowsets arrays with
cursors because you can specify rowset-size and row-id for cursors that use
rowset-derived tables, but you cannot specify them for cursors that use rowset arrays
directly.
Example
In this example, the ODETAIL table is joined with a rowset using a rowset-derived
table. The program uses a cursor to fetch all rows whose order number values are
specified in the input rowset, which contains an array of order numbers. The number of
valid elements in the input array is specified using a host variable. You can use the
index identifier rowid to determine which input condition cause a specific row to be
output. The rowid array is empty before execution of the query. After execution,
because rowid is a zero-based index, it contains values that range from 0 to the
number of valid input conditions minus 1. This strategy might not be optimal for a
COBOL application where the array indexing starts from 1. Therefore, for COBOL
programs, use the solution in: Using the Index Identifier
on page 7-25. A value j in the
rowid array for a particular row indicates that the row was fetched from the table
during execution of input condition number j (calculated with a zero-based array
indexing).
EXEC SQL BEGIN DECLARE SECTION;
long SQLCODE;
ROWSET [5] unsigned NUMERIC (4) hva_ordernum;
ROWSET [5] unsigned NUMERIC (4) hva_od_partnum;
ROWSET [5] short rowid;
short num_inputvalues;
...
EXEC SQL END DECLARE SECTION;
...
/* Populate the rowset in some way. */
hva_ordernum[0] = 244;
hva_ordernum[1] = 2001;
C