SQL/MX 3.2.1 Programming Manual for C and COBOL (H06.26+, J06.15+)

Static Rowsets
HP NonStop SQL/MX Release 3.2.1 Programming Manual for C and COBOL—663854-005
7-9
Selecting Rows Into Rowset Arrays
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-16.
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.
At the beginning of the execution of an SQL statement, the diagnostics area is
emptied. When the statement executes, NonStop SQL/MX places information on
completion or exception conditions into this area. The diagnostics area consists of:

Statement information: Header area with information on the SQL statement as a
whole

Condition information: Detail area with information on each error, warning, or
completion code that occurs during the execution of the SQL statement
The number of retrieved rows is stored in the ROW_COUNT field of the statement
information in the diagnostics area. You can retrieve the value in the ROW_COUNT
field by using the GET DIAGNOSTICS statement. In the preceding example, the
statement that retrieves the value of ROW_COUNT is specified as:
EXEC SQL GET DIAGNOSTICS :numrows = ROW_COUNT;
For further information, see the GET DIAGNOSTICS statement in the SQL/MX
Reference Manual.