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-7
Selecting Rows Into Rowset Arrays
Use this general syntax:
For complete syntax, see the SELECT statement in the SQL/MX Reference Manual.
Example
This example uses a SELECT statement returning an employee’s first name, last
name, and department from the EMPLOYEE table. The elements in the target host
variable arrays are in the order based on the columns in the ORDER BY clause.
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
ROWSET [100] unsigned NUMERIC (4) hva_deptnum;
ROWSET [100] char hva_firstname[16];
ROWSET [100] char hva_lastname[21];
...
long numrows;
EXEC SQL END DECLARE SECTION;
long i;
...
EXEC SQL
SELECT first_name, last_name, deptnum
INTO :hva_firstname, :hva_lastname, :hva_deptnum
FROM persnl.employee
ORDER BY deptnum, last_name, first_name;
...
EXEC SQL GET DIAGNOSTICS :numrows = ROW_COUNT;
...
for (i = 0; i < numrows; i++) {
/* NOTE: The null termination can also be done */
/* before the SELECT statement. */
hva_firstname[i][15] = '\0';
hva_lastname[i][20] = '\0';
printf("\nDept: %hu, Name: %s, %s",
hva_deptnum[i], hva_lastname[i], hva_firstname[i]);
...
/* Process the row in some way. */
SELECT column [,column]...
INTO :hostvar-array [,:hostvar-array]...
FROM table-name [,table-name]...
[WHERE search-condition]
[GROUP BY column [,column]...]
[HAVING search-condition]
[ORDER BY column [,column]...]
Note. Data mining operations—SAMPLE, SEQUENCE BY, and TRANSPOSE—are not
supported for operations with rowsets. Some Publish/Subscribe operations are not supported
with rowsets either. Specifically, you cannot use rowsets as input (in WHERE and SET
clauses) with embedded UPDATEs and DELETEs. Additionally, you cannot join a rowset-
derived table with an embedded UPDATE or DELETE.
C