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-3
Specification of an Rowset Parameter in the
PREPARE String
to fetch one row at a time and multiple rows at a time by using dynamic rowsets in
separate FETCH calls.
Specification of an Rowset Parameter in the PREPARE String
To use a rowset host variable for an input parameter, indicate its length by
'?[positive-integer-constant]' in the PREPARE string. For example:
?[10]
The length specifier must be a positive integer (> 0) and a constant. Otherwise, a
parse error is raised. The term rowset parameter denotes the parameter represented
by '?[positive-integer-constant]'. In contrast with static SQL, all rowset
parameters used for input in a single SQL statement must be of the same size (for
example, N). This approach is logically equivalent to the SQL statement being
executed separately N times, with scalar values for input.
To use scalar host variable for an input parameter, use the PREPARE syntax ‘?,’ as
described in Section 9, Dynamic SQL.
As with static SQL, you can mix scalar and array type input parameters in the same
SQL statement. Note that with the syntax presented, ?[1] denotes a rowset parameter
of length one, which is distinct from ?, which denotes a scalar parameter. As explained
in Section 7, Static Rowsets, when a scalar input parameter is mixed in with rowset
parameters, the same value is used for each of the N logical invocations of the
statement. Therefore, ? implies the same values for each logical invocation, while ?[1]
implies an array with one element. No replication of values exists when ?[1] is used.
Basic Dynamic Rowset Example
This statement inserts rows into table TAB, 100 rows at a time. All three input host
variables are of rowset type with a minimum length of 100. The type of each rowset
host variable must be compatible with the type of the column into which its values are
inserted.
INSERT INTO tab VALUES (?[100], ?[100], ?[100])
Mixing Scalar and Rowset Host Variables Example
This statement inserts rows into table TAB, 50 rows at a time. The first column must be
a rowset host variable with a minimum length of 50 and type compatible with the type
of the first column. The second column must be a rowset host variable with a minimum
length of 50 and type compatible with NUMERIC(4) UNSIGNED. The third column
must be a scalar host variable and its type must be compatible with the type of the third
column. You can use scalars and rowsets in the same insert list. The same value is
used for the scalar at each logical invocation of the statement.
INSERT INTO tab VALUES (?[50] , CAST(?[50] AS NUMERIC(4)
UNSIGNED), ?)