SQL/MX 3.2.1 Guide to Stored Procedures in Java (H06.26+, J06.15+)

Number of Entries in Input and Output Descriptors
In dynamic SQL, the input descriptor has n entries, where n is the number of actual parameters
that map to formal IN and INOUT parameters. n is not necessarily the same as the number of
formal IN and INOUT parameters.
Conversely, the output descriptor has m entries, where m is the number of actual parameters that
map to formal OUT and INOUT parameters. m is the same as the number of formal OUT and
INOUT parameters because each output parameter must be represented by a single dynamic
parameter in the CALL statement.
Example of Using Input and Output Descriptors
To code an embedded SQL program to use a dynamic CALL statement with SQL descriptor areas:
1. Allocate input and output SQL descriptor areas.
Allocate an input SQL descriptor area for dynamic input parameters associated with the IN
or INOUT mode, and allocate an output descriptor area for dynamic output parameters
associated with the OUT or INOUT mode:
EXEC SQL ALLOCATE DESCRIPTOR 'in_sda';
EXEC SQL ALLOCATE DESCRIPTOR 'out_sda';
For the syntax of the ALLOCATE DESCRIPTOR statement, see the SQL/MX Reference Manual.
2. Prepare the CALL statement.
Store the CALL statement in a host variable and then prepare the CALL statement. The PREPARE
statement checks the statement syntax, determines the data types of parameters, compiles the
CALL statement, and associates the CALL statement with a statement name that you can use
in a subsequent EXECUTE statement:
/* Build the CALL statement in a char buffer. */
strcpy(hv_sql_stmt,
"CALL samdbcat.persnl.adjustsalary(?,?,?)");
/* IN, IN, OUT */
/* Prepare the statement. */
EXEC SQL PREPARE sqlstmt FROM :hv_sql_stmt;
For the syntax of the PREPARE statement, see the SQL/MX Reference Manual.
3. Describe the input and output parameters.
The DESCRIBE statement stores information about the dynamic input or output parameters of
a prepared CALL statement in an SQL descriptor area. Use these DESCRIBE statements to
populate the SQL input and output descriptor areas:
• DESCRIBE INPUT initializes the input SQL descriptor area based on the dynamic input
parameters (associated with the IN or INOUT mode) for a prepared CALL statement:
EXEC SQL DESCRIBE INPUT sqlstmt
USING SQL DESCRIPTOR 'in_sda';
• DESCRIBE OUTPUT stores descriptions in the output SQL descriptor area of dynamic
output parameters (associated with the OUT or INOUT mode) from a prepared CALL
statement:
EXEC SQL DESCRIBE INPUT sqlstmt
USING SQL DESCRIPTOR 'in_sda';
For the syntax of the DESCRIBE statement, see the SQL/MX Reference Manual.
For the syntax of the DESCRIBE statement, see the SQL/MX Reference Manual.
4. Set the input parameter values by using SET DESCRIPTOR.
Use the SET DESCRIPTOR statement to set information explicitly in the input SQL descriptor
area for the individual input parameters:
78 Invoking SPJs in NonStop SQL/MX