SQL/MX Programming Manual for C and COBOL (G06.24+, H06.03+)

HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
7-1
7 Static Rowsets
The traditional cursor model in SQL is inefficient for applications retrieving large
numbers of rows because too much time is used retrieving one row at time. However,
the SQL/MX extension rowsets enable the SQL cursor to return more than one row at
a time, greatly reducing the number of calls made to both the database system and the
network.
Rowsets improve the performance of applications requiring simultaneous access to
several rows at a time, whether that access is to perform comparisons or other types of
processing. Rowsets simplify the task of storing and manipulating a large number of
rows in the application address space.
This section describes:
•
What Are Rowsets? on page 7-1
•
Using Rowsets on page 7-2
•
Declaring Host Variable Arrays as Rowsets on page 7-2
•
Specifying Rowset Arrays on page 7-4
•
Using Rowset Arrays in DML Statements on page 7-6
•
Specifying Size and Row ID for Rowset Arrays on page 7-20
•
Specifying Rowset-Derived Tables on page 7-28
•
Using Rowset-Derived Tables in DML Statements on page 7-29
What Are Rowsets?
The rows returned in a single fetch are called the rowset, and the columns of the rows
are the arrays composing the rowset. An application can present a set of column
values (rows) in an SQL statement (for example, in the WHERE clauses). The host
variable arrays composing a rowset therefore can be used as output variables (for
example, in the INTO clause) to receive large amounts of data from SELECT and
FETCH statements. Each host variable array receives data from one selected column.
Similarly, you can use host variable arrays as input variables for these statements:
•
SELECT (WHERE and HAVING clauses)
•
INSERT (VALUES clause)
•
UPDATE (SET and WHERE clauses)
•
DELETE (WHERE clause)
Rowsets are supported for both static and dynamic embedded SQL programs in
NonStop SQL/MX. Before using static rowsets, you must declare them as host variable
arrays in the SQL DECLARE section by using the keyword ROWSET. For information
on dynamic rowsets, see Section 12, Dynamic SQL Rowsets.
Note. Rowsets are not supported from MXCI.
Note. Examples in this section show how to use rowsets from an SQL/MX program that
accesses SQL/MX and SQL/MP tables. Rowsets as a feature do not exist in NonStop
SQL/MP.