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-8
Selecting Rows Into Rowset Arrays
....
}
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 sqlstate pic x(5).
01 numrows pic 9(9) comp.
01 rs.
02 ROWSET[100] hvadeptnum pic 9(4) comp.
02 ROWSET[100] hvafirstname pic x(15).
02 ROWSET[100] hvalastname pic x(20).
EXEC SQL END DECLARE SECTION END-EXEC.
01 i pic s9(4) comp.
...
EXEC SQL
SELECT first_name, last_name, deptnum
INTO :hvafirstname, :hvalastname, :hvadeptnum
FROM employee
ORDER BY deptnum, last_name, first_name END-EXEC.
EXEC SQL
GET DIAGNOSTICS :numrows = ROW_COUNT end-exec.
PERFORM VARYING i FROM 1 BY 1 UNTIL i > numrows
display "Dept: " hvadeptnum(i) "Name: " hvalastname(i)
hvafirstname(i)
end-perform.
...
The previous example is correct only if the SELECT INTO statement is certain to return
fewer than 100 rows. If more than 100 rows are returned, an SQLSTATE value is
returned.
If the SELECT statement can return more rows than are allocated in the rowset array,
you have these choices:
•
Limit the SQL query so that it returns only a specified number of rows, as shown:
...
EXEC SQL
SELECT [first 100]first_name, last_name, deptnum
INTO :hva_firstname, :hva_lastname, :hva_deptnum
FROM persnl.employee
ORDER BY deptnum, last_name, first_name;
...
•
Use a rowset cursor to get all the results from the SELECT statement. See
Selecting Rowsets With a Cursor on page 7-12.
Getting the Number of Retrieved Rows
In the preceding example, the actual number of rows retrieved is stored in the
diagnostics area. NonStop SQL/MX stores completion and exception information for an
embedded SQL statement in this area. NonStop SQL/MX automatically allocates the
diagnostics area in a program. You are not required to explicitly allocate it yourself.
COBOL