SQL/MX Programming Manual for C and COBOL (H06.10+, J06.03+)

Introduction

HP NonStop SQL/MX Programming Manual for C and COBOL—544617-003

1-9

Declaring a Rowset

You declare a host variable array, along with its dimension, with the SQL Declare

Section. A rowset array is declared for each column in a query. Each rowset array

contains as many elements as are contained in the rowset.

Example

In this example, hvarray_jobcode and hvarray_jobdesc are host variable

arrays to be used in a rowset:

EXEC SQL BEGIN DECLARE SECTION;

ROWSET [20] unsigned NUMERIC (4) hvarray_jobcode;

ROWSET [20] char hvarray_jobdesc[19];

...

EXEC SQL END DECLARE SECTION;

Using a Rowset in a Query

You do not need to use a cursor when you are retrieving the results of a query in an

output rowset and the number of rows returned does not exceed the size of the rowset.

Example

In this example, using the SQL Declare Section from the previous example, a

maximum of 20 rows are retrieved from the JOB table:

EXEC SQL SELECT jobcode, jobdesc

INTO :hvarray_jobcode, :hvarray_jobdesc

FROM persnl.job;

The previous example is correct only if the SELECT INTO statement is certain to return

fewer than 20 rows. If the SELECT statement can return more rows than are allocated

in the rowset array, you have these choices:

•

You can limit the SQL query so that it returns only a specified number of rows as

shown in this example:

...

EXEC SQL

SELECT [first 20]jobcode, jobdesc

INTO :hvarray_jobcode, :hvarray_jobdesc

FROM persnl.job;

...

•

If you want to get all the results from the SELECT statement, use a rowset cursor.

See Selecting Rowsets With a Cursor on page 7-12.

You must use a cursor when the maximum number of result rows cannot be estimated

or when the memory requirements are too large to store the result table of the query.