 |
» |
|
|
|
The EXECUTE statement causes ALLBASE/SQL to execute a statement
that has been dynamically preprocessed by means of the PREPARE
statement. Scope | |
ISQL or Application Programs
SQL Syntax | |
EXECUTE { StatementName [Owner.] ModuleName [ (SectionNumber) ] } [USING {[SQL] DESCRIPTOR {[INPUT ] { SQLDA AreaName1 } [AND OUTPUT { SQLDA AreaName2 }] OUTPUT { SQLDA AreaName2 } } [INPUT] HostVariableSpecification1 [ AND OUTPUT HostVariableSpecification2 ]
OUTPUT HostVariableSpecification
:Buffer [,:StartIndex [, :NumberOfRows]] } ] Parameters | |
- StatementName
identifies a dynamically preprocessed statement
to be executed in an application program.
The StatementName corresponds to
one specified in a previous PREPARE
statement. This form of the EXECUTE statement
cannot be used interactively.
- [Owner.]ModuleName [(SectionNumber)]
identifies a dynamically
preprocessed statement to be executed
interactively. The preprocessed statement cannot be a SELECT
statement. This form of the EXECUTE statement
cannot be used in an application program.
If the section number is omitted, section number
one is assumed.
You can omit the verb EXECUTE interactively.
- USING
allows dynamic parameter substitution in a prepared
statement in an application program.
- [SQL]DESCRIPTOR
indicates that a data structure of sqlda_type
is used to pass dynamic parameter information between the application
and ALLBASE/SQL.
- SQLDA
specifies that a data structure of sqlda_type named sqlda
is used to pass dynamic parameter information between the application
and ALLBASE/SQL.
- AreaName
specifies the user defined name of a data structure of
type sqlda_type that is used to pass dynamic parameter information
between the application and ALLBASE/SQL.
- HostVariableSpecification
specifies host variable(s) that
hold dynamic parameter values at run time.
The syntax of HostVariableSpecification is presented separately below.
- INPUT
is the default for any EXECUTE statement and can be specified, as
required, for any type of prepared statement containing input dynamic
parameters.
- OUTPUT
is only allowed when the
prepared statement is an EXECUTE PROCEDURE statement. It can be used
when the statement contains output dynamic parameters.
- INPUT AND OUTPUT
is only allowed when the
prepared statement is an EXECUTE PROCEDURE statement. It can be used
when the statement contains both input and output dynamic parameters.
- Buffer
is a host variable array structure containing rows that are the
input for a BULK INSERT statement.
This structure contains fields for each column to be inserted
and indicator variables for columns that can
contain null values. Whenever a column can contain nulls,
an indicator variable must be included in the array definition
immediately after the definition of that column.
This indicator variable is an integer that can have the following
values:
- > = 0
the value is not NULL
- < 0
the value is NULL
| | | |  | NOTE: To be consistent with standard SQL and to support portability of
code, it is strongly recommended that you use a -1 to indicate a null
value. However, ALLBASE/SQL interprets all negative indicator variable
values to mean a null value.
| | | | |
- StartIndex
is a host variable whose value specifies the array
subscript denoting where the first row to be inserted is stored;
default is the first element of the array.
- NumberOfRows
is a host variable whose value specifies the number
of rows to insert; default is to insert from the starting index to the
end of the array.
SQL Syntax--HostVariableSpecification | |
:HostVariableName [ [INDICATOR] :IndicatorVariable ] [,...] Parameters--HostVariableSpecification | |
- HostVariableName
specifies a host variable name that at run time
contains the data value that is assigned to a dynamic parameter defined
in a prepared statement.
Host variables must be specified in the same order as the dynamic
parameters in the prepared statement they represent.
There must be a one to one correspondence between host variable names and
the dynamic parameters in the prepared statement.
A maximum of 1024 host variable names can be specified.
- IndicatorVariable
names an indicator variable, whose value
determines whether the associated host variable contains a NULL value:
- > = 0
the value is not NULL
- < 0
the value is NULL
Description | |
There must be a one to one mapping of the input and/or output parameters
in a prepared statement and its associated EXECUTE statement.
INPUT is the default for any EXECUTE statement and can be specified, as
required, for any type of prepared statement.
The OUTPUT clause is only allowed when the
prepared statement is an EXECUTE PROCEDURE statement containing output
dynamic parameters.
An INPUT AND OUTPUT clause is only allowed when the
prepared statement is an EXECUTE PROCEDURE statement containing both
input and output dynamic parameters.
If StatementName is an EXECUTE PROCEDURE statement without any
input and output dynamic parameters, you can execute the procedure by
issuing EXECUTE StatementName.
If StatementName is an EXECUTE PROCEDURE statement with either
input or output dynamic parameters, you can use the EXECUTE USING statement
with INPUT (default) or OUTPUT option to execute the dynamically
preprocessed statement.
If StatementName is an EXECUTE PROCEDURE statement with both
input and output dynamic parameters, you can use the EXECUTE USING statement
with the INPUT AND OUTPUT option to execute the dynamically preprocessed
statement.
Use the USING clause for either an SQLDA DESCRIPTOR or a
HostVariableSpecification
for input and/or output dynamic parameter substitution in a prepared statement.
The :Buffer [,:StartIndex [, :NumberOfRows]] option
is only used in association with a BULK INSERT statement.
If StatementName is an EXECUTE PROCEDURE statement, and
there are multiple row result sets from
the procedure,
you must use the procedure cursor method to retrieve result sets.
A warning is returned if a procedure cursor is not used in
this case; the return status and output parameters are returned as usual.
Authorization | |
In an application program, the EXECUTE statement does not
require any special authorization. The user running the
program must have whatever authorization is required by the
dynamically preprocessed statement being executed.
To use the EXECUTE statement in the interactive environment,
you must have RUN or OWNER authority for the dynamically
preprocessed statement or have DBA authority. In
addition, the owner of the dynamically preprocessed statement
must have whatever authorization the dynamically preprocessed
statement itself requires. Examples | |
Interactive execution
isql=> PREPARE Statistics(1)
> FROM 'UPDATE STATISTICS FOR TABLE PurchDB.Orders'
isql=> PREPARE Statistics(2)
> FROM 'UPDATE STATISTICS FOR TABLE PurchDB.OrderItems'
Two sections for module Statistics are stored in the system catalog.
isql=> EXECUTE Statistics(1)
The statistics for table PurchDB.Orders are updated.
isql=> EXECUTE Statistics(2)
The statistics for table PurchDB.OrderItems are updated.
isql=> DROP MODULE Statistics
Both sections of the module are deleted.
|
Programmatic execution
If you know that the statement to be dynamically preprocessed is
not a SELECT statement and does not contain dynamic parameters, you can
prepare it and execute it in one step, as follows:
EXECUTE IMMEDIATE :Dynam1
|
You can prepare and execute the
statement in separate operations. For example, if you don't know the format
of a statement, you could do the following:
PREPARE Dynamic1 FROM :Dynam1
The statement stored in :Dynam1 is dynamically preprocessed.
DESCRIBE Dynamic1 INTO SqldaOut
|
If Dynamic1 is not a SELECT statement, the Sqld field of the Sqlda data
structure is 0. If you know there are no dynamic parameters in
the prepared statement, use the EXECUTE statement to execute the dynamically
preprocessed statement.
If it is possible that dynamic parameters are in the prepared
statement, you must describe the statement for input:
DESCRIBE INPUT Dynamic1 USING SQL DESCRIPTOR SqldaIn
|
If the prepared statement could be an EXECUTE PROCEDURE statement
(sqld = zero on DESCRIBE OUTPUT) with dynamic output parameters, you must
describe it for output:
DESCRIBE OUTPUT Dynamic1 USING SQL DESCRIPTOR SqldaOut
|
If only dynamic input parameters are present, the appropriate data buffer
or host variables must be loaded with the values of any dynamic parameters.
Then if the statement is not a query, it can be executed, as in this example
using a data buffer:
{{EXECUTE Dynamic1 USING SQL DESCRIPTOR SqldaIn}}
|
However, if the prepared statement is an EXECUTE PROCEDURE statement with
multiple row result sets (sqlmproc = non-zero) and dynamic input and output
parameters execute it as follows:
{{EXECUTE Dynamic1 USING SQL INPUT DESCRIPTOR SqldaIn}}
{{and OUTPUT DESCRIPTOR SqldaOut}}
|
|
