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-4
Matching Compile-Time Specified Length With
Execution-Time Length
Using the FOR INPUT SIZE and KEY BY Syntax Example
This statement uses the FOR INPUT SIZE and KEY BY syntax with an array input
parameter in the WHERE clause. The input size is always a scalar parameter, and its
value must be less than the length of the input parameter array.
ROWSET FOR INPUT SIZE ? KEY BY rowid SELECT rowid, COUNT(*),
a FROM tab WHERE b = ?[50] GROUP BY rowid, a
Matching Compile-Time Specified Length With Execution-Time
Length
When you specify rowset type host variables during execution to take the place of
parameter arrays specified in the PREPARE string, their length must match the integer
constant provided for that parameter array in the PREPARE string, if an INPUT SIZE
clause is not present in the SQL statement.
Use rowset host variables of sufficient length. NonStop SQL/MX might raise an error
during execution of the statement if the size of an input rowset host variable provided is
less than N, the common size of all input rowset parameters. The error is raised during
execution for reading from or writing into memory that was not intended for that
purpose. In other words, NonStop SQL/MX does not perform a bounds check on
rowset host variables used during execution. Memory access violations are reported at
execution time if these guidelines are not followed. Also, memory might be overwritten
or falsely read from, based on the specific circumstance. Memory access violations
occur when an input rowset host variable is less than size N. You cannot assume that
these errors will be raised by NonStop SQL/MX during execution time.
You can compile with rowset parameters and use scalar values at execution time if you
can use at least one rowset host variable during execution. The scalar value is
duplicated as many times as specified in the PREPARE string.
An error will be raised during execution if:
•
You specify one or more parameter arrays in the PREPARE string, and all host
variables used at run time for input are scalars.
•
You specify a scalar parameter (not of array type) in the PREPARE string and use
a rowset host variable to transfer data for that parameter.
When you specify more than one rowset parameter in the PREPARE string, at most all
but one of the rowset parameters can be provided scalar values at run time. The
rowset parameters that have scalar values provided for them at execution time behave
as if the query were compiled with scalar parameters in this location. The scalar value
is duplicated by NonStop SQL/MX as many times as the size of the input array value.
Note that when you use rowset host variables of size larger than N for input, values
beyond the Nth entry are ignored.