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-2
Using Rowsets
Using Rowsets
The two ways of using rowsets in SQL queries are:
•
Direct use. You can place host variable rowset arrays anywhere a scalar host
variable is placed in an SQL query.
•
Rowset-derived tables. Given a rowset, a construct is provided that creates an
in-memory table. A rowset-derived table resulting from this construct is a table of
several columns (one column for each array of the rowset) and rowset size tuples
or rows.
A rowset is analogous to an in-memory table. A rowset with one host variable array of
n elements is similar to a temporary in-memory table with n tuples, where the j
element value of the array corresponds to the j tuple of the table. A rowset with M host
variable arrays of n elements is similar to a temporary in-memory table with M columns
and n tuples. The j element value of array A corresponds to the A column of the j
tuple of the table. Within the scope of an SQL statement and by using rowset-derived
table syntax, you can create and use in-memory tables in a way similar to ordinary
tables.
An SQL statement containing a rowset of size n for input is handled like a join of the
tables composing the SQL statement with the rowset or the execution of the same
statement n times using successive elements of the rowset. The semantics and side
effects of rowsets are explained assuming that the rowset is just another table in the
SQL statement.
For output, a cursor is typically needed in a SELECT statement unless the SELECT
statement returns a single row. When you use rowset arrays as host variables to
retrieve results, this rule is expanded as follows:
•
A cursor is not needed if the SELECT statement returns no more than the size of
the rowset.
•
A cursor must be used when the maximum number of rows cannot be estimated or
when memory requirements are too high to store the result table. In this case, the
result table is retrieved in rowset size batches using the FETCH statement.
Declaring Host Variable Arrays as Rowsets
A host variable array, along with its dimension, is declared within the SQL Declare
Section of an embedded SQL program. A rowset array is a host variable array that is
declared for each column in a query. A rowset consists of a collection of rowset arrays.
Each rowset array contains as many elements as there are in the rowset.
The dimensions of the arrays that make up a rowset correspond to the desired number
of elements. All arrays must have the same number of dimensions as the other arrays
in the rowset or be at least as large as the desired rowset. NonStop SQL/MX uses the
smallest dimension as the rowset size while performing operations into the rowset.