You use EXECUTE to execute a statement prepared with PREPARE. Placeholders for input values in the dynamic statement are replaced by specific values.
If the statement is a SELECT statement, the column values of the derived rows are stored in host variables or in an SQL descriptor area.
If the statement is a CALL statement, values of output parameters are stored in host variables or in an SQL descriptor area.
You can use EXECUTE to execute a previously prepared statement any number of times.
A statement can only be executed with EXECUTE in the compilation unit in which it was previously prepared with PREPARE.
EXECUTE statement_id
[INTO declaration ]
[USING declaration ]
declaration ::= { variable | SQL DESCRIPTOR GLOBAL descriptor }
variable ::= :host_variable [[INDICATOR] :indicator_variable] [,:host_variable [[INDICATOR] :indicator_variable]] ...
statement_id
Identifier of the dynamic statement that has been prepared with PREPARE.
If the statement text contains a cursor name, the cursor description for this cursor must be prepared and the cursor opened before the EXECUTE statement is executed.
INTO clause
Indicates where the output values of the dynamic statement specified with statement_id are to be stored. The INTO clause must be specified in the following cases:
The prepared statement is a SELECT statement
The prepared statement is a CALL statement and the procedure called in it has parameters of the type OUT or INOUT
host_variable
Name of a host variable assigned an output value.
The data type of a host variable must be compatible with the data type of the relevant output value (see section "Reading values into host variables or a descriptor area").
If an output value is an aggregate with several elements (SELECT statement), the corresponding host variable must be a vector with the same number of elements. The number of specified host variables must be the same as the number of output values in the SELECT statement specified with statement_id.
In a procedure call using the CALL statement, the number of host variables specified must match the number of procedure parameters of the type OUT or INOUT in the procedure called.
indicator_variable
Name of the indicator variable for the preceding host variable.
If the host variable is a vector (SELECT statement), the indicator variable must also be a vector with the same number of elements.
The indicator value indicates whether the NULL value was transferred or whether data was lost:
0 -1 > 0 | The host variable contains the value read. The assignment was error free. The value to be assigned is the NULL value. For alphanumeric and national values: |
descriptor
Name of an SQL descriptor area containing the data type description of the output values and into which the output values (for procedures the procedure parameters of the type OUT or INOUT) are written when the statement specified by statement_id is executed.
The SQL descriptor area must be created beforehand and supplied with appropriate values:
The value of the COUNT field must be the same as the number of output values of the statement specified with statement_id (for aggregates one output value for each element, for procedures one output value for each procedure parameter of the type OUT or INOUT) where
0 <= COUNT <= defined maximum number of item descriptors
The output values are assigned to the DATA fields of the item descriptors in the order of the items in the descriptor area. The data type description for an item must be compatible with the data type of the corresponding output value (see section "Reading values into host variables or a descriptor area").
If the value to be transferred is the NULL value, the appropriate INDICATOR field is set to the value -1. If a string to be assigned is truncated, the corresponding INDICATOR field indicates the original length.
USING clause
Specifies where the input values for the dynamic statement statement_id are to be read from. The INTO clause must be specified in the following cases:
When the SELECT statement contains question marks as placeholders for values
When the procedure called in the CALL statement has parameters of the type IN or INOUT and the corresponding arguments contain question marks as placeholders for values
host_variable
Name of a host variable containing the value to be assigned to a placeholder in the dynamic statement statement_id.
The data type of a host variable must be compatible with the data type of the corresponding placeholder (see section "Values for placeholders").
If the placeholder represents an aggregate with several elements (SELECT statement), the corresponding host variable must be a vector with the same number of elements.
The number of host variables specified must be the same as the number of placeholders in the SELECT statement.
In a procedure call using the CALL statement, the number of host variables specified must match the number of placeholders for parameters of the data type IN or INOUT.
The user variables are assigned values in the order in which the placeholders are specified in the dynamic statement.
indicator_variable
Name of the indicator variable for the preceding host variable.
If the host variable is a vector (SELECT statement), the indicator variable must also be a vector with the same number of elements.
The value of the indicator variable indicates whether the NULL value is to be transferred:
< 0 >= 0 | The NULL value is to be assigned. The value of the host variable is to be assigned. |
descriptor
Name of an SQL descriptor area containing the data types and values for the placeholders in the dynamic statement statement_id.
The SQL descriptor area must be created beforehand and supplied with appropriate values:
The value of the descriptor area field COUNT must be the same as the number of input values required (for aggregates one input value for each element, for procedures one output value for each procedure parameter of the type OUT or INOUT) where
0 <= COUNT <= defined maximum number of item descriptors
The values of the DATA fields of the item descriptors (or NULL values if the INDICATOR is negative) are assigned to the placeholders in the dynamic statement in the order of the items in the descriptor area. The data type
description of an item must be compatible with the data type of the corresponding placeholder (see section "Values for placeholders").
Example
EXECUTE dyn_statement
INTO SQL DESCRIPTOR GLOBAL 'DESCR_AREA'
See also
EXECUTE IMMEDIATE, PREPARE, SELECT