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-12
Selecting Rowsets With a Cursor
Move 9000 TO hvadeptnum(2)
Move 300 TO hvajobcode(3)
Move 1000 TO hvadeptnum(3)
Move 400 TO hvajobcode(4)
Move 1000 TO hvadeptnum(4)
Move 500 TO hvajobcode(5)
Move 3000 TO hvadeptnum(5)
EXEC SQL
SELECT empnum, salary
INTO :hvaempnum, :hvasalary INDICATOR :hvasalaryindicator
FROM employee
WHERE jobcode = :hvajobcode AND
deptnum = :hvadeptnum
END-EXEC.
...
Selecting Rowsets With a Cursor
If the number of rows returned by a SELECT statement exceeds the size of the rowset
array, use a FETCH statement with a rowset cursor to cycle over a specific number of
rows. The next example uses a rowset cursor to fetch and print the project code, the
project description, and the start date of all projects started before a specific date.
For complete syntax, see the FETCH statement in the SQL/MX Reference Manual.
Examples
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
ROWSET[200] unsigned NUMERIC (4) hva_projcode;
ROWSET[200] char hva_projdesc[19];
ROWSET[200] DATE hva_start_date;
long SQLCODE;
long numrows;
EXEC SQL END DECLARE SECTION;
long i;
/* null terminate char arrays */
for (i = 0; i < 200; i++) {
hva_projdesc[i][18] =
'\0';
hva_start_date[i][10] =
'\0';
}
/* declare cursor for select operation */
EXEC SQL
DECLARE rowset_cursor CURSOR FOR
SELECT projcode, projdesc, start_date
FROM persnl.project
WHERE start_date <= DATE
'1998-12-01';
/* open the cursor */
EXEC SQL
OPEN rowset_cursor;
/* Fetch all rows of the result table */
WHILE (SQLCODE == 0) {
EXEC SQL
FETCH rowset_cursor
INTO :hva_projcode, :hva_projdesc, :hva_start_date;
IF ((SQLCODE == 0)||(SQLCODE == 100)) {
EXEC SQL GET DIAGNOSTICS :numrows = ROW_COUNT;
C