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

Introduction

HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004

1-8

Declaring a Rowset

•

The number of function calls between the application and NonStop SQL/MX is

reduced by manipulating rows in sets. Network traffic is reduced because the data

for several rows is sent in a single packet.

•

When data is stored in an array, the application can bind all rows in a particular

column with a single bind call and update or delete all rows by executing a single

statement.

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;

...