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

Dynamic SQL Rowsets
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
12-2
Preparing an SQL Statement With Dynamic Rowsets
Data can be exchanged between a dynamic embedded SQL application and the
database by using rowset-type host variables (as in static SQL) or by using the
address of memory locations in application space, which have been suitably prepared
to send data to or receive data from the database. Consequently, dynamic rowsets can
be used without declaring rowset-type host variables in the DECLARE section. This
requires the use of descriptor areas to provide the address of input/output memory
locations to NonStop SQL/MX. It is recommended that you use rowset-type host
variables to exchange data with the database. The remainder of this section assumes
that all input data will be provided in host variables, and output data will be fetched into
host variables.
These dynamic SQL statements have modified behavior when rowsets are used.
•
PREPARE
•
GET DESCRIPTOR
•
SET DESCRIPTOR
•
DESCRIBE INPUT
For all other types of dynamic SQL statements, the descriptions in Section 9, Dynamic
SQL, Section 10, Dynamic SQL With Descriptor Areas, and Section 11, Dynamic SQL
Cursors apply without any change to rowsets. The rowset-specific information of these
four statements are described in this section.
Preparing an SQL Statement With Dynamic
Rowsets
In dynamic embedded SQL, SQL statements are placed in character strings and are
SQL compiled at execution time by calling the PREPARE statement. You might not
know the host variables used to transfer data between an application program and a
database when the SQL statement is prepared. Input parameters are instead denoted
by '?', with an optional CAST specification describing the SQL type of the parameter in
the SQL statement that is prepared.
To use dynamic rowsets for input, you must know when calling PREPARE if each input
parameter will be executed using a scalar host variable or a rowset host variable. If you
use a rowset host variable, you must specify its length (that is, the number of entries in
the rowset) in the PREPARE string. In other words, you must indicate that an input
parameter is of array type when you specify the SQL statement using the syntax
shown in Specification of an Rowset Parameter in the PREPARE String on page 12-3.
You can mix scalar input parameters and array input parameters in the same SQL
statement.
To use dynamic rowsets for output, the PREPARE statement is not modified at all from
the description in Section 11, Dynamic SQL Cursors. When preparing a cursor
specification, you are not required to specify whether you will use rowsets to fetch from
a dynamic SQL cursor. You can decide during the execution phase. No prior notice is
required during the compile phase. In fact, you can use the same dynamic SQL cursor