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-27
Using the Index Identifier
•
Three rows with jobcode equal to 500 and row identifier value 4 for C and 5 for
COBOL
The jobcode equal to 350 does not exist in the sample database. As a result, the row
identifier equal to 2 for C and 3 for COBOL does not occur in the output of the
program.
The row identifier values in the COBOL example are greater, by a value of 1, than their
corresponding values in the C example. This difference occurs because the SQL query
is different in the two examples. For SQL, the row identifier column is a zero-based
index. For the convenience of COBOL applications, the COBOL example modifies an
SQL query to output values of the row identifier column as a one-based index.
Use the index identifier to obtain a count of how many rows are selected due to each
condition in the WHERE clause input rowset array. See the next example, which uses
the same table and input host variables as in the previous example:
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
ROWSET[5] unsigned NUMERIC (4) hva_jobcode;
ROWSET [100] short hva_row_count;
ROWSET [100] short hva_row_id;
...
long numrows;
EXEC SQL END DECLARE SECTION;
long i;
...
/* Populate the jobcode rowset in some way. */
hva_jobcode[0] = 100;
hva_jobcode[1] = 200;
hva_jobcode[2] = 350; /* Does not exist. */
hva_jobcode[3] = 400;
hva_jobcode[4] = 500;
...
EXEC SQL ROWSET FOR KEY BY row_id
SELECT row_id, COUNT(*)
INTO :hva_row_id, :hva_row_count
FROM persnl.employee
WHERE jobcode = :hva_jobcode
GROUP BY row_id ;
EXEC SQL GET DIAGNOSTICS :numrows = ROW_COUNT;
...
for (i = 0; i < numrows; i++) {
printf("\nRow Id: %hu", hva_row_id[i]);
printf("\nRow Count: %hu", hva_row_count[i]);
Note. Many of the examples in this manual use the NonStop SQL/MX Release 2.x sample
database, which uses SQL/MX format tables. To install the sample database, you must have a
license for the use of SQL/MX DDL statements. To acquire the license, you must purchase
product T0394. If you did not purchase T0394 and you try to install the sample database, an
error message informs you that the system is not licensed.
C