HP 3000 Manuals HP 3000 Manuals
EXECUTE
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)]}
[ { {[INPUT] {SQLDA } }}]
[ { { {AreaName1} }}]
[ { { }}]
[ {[SQL] DESCRIPTOR{[AND OUTPUT {SQLDA }]}}]
[ { {[ {AreaName2}]}}]
[ { { }}]
[USING { {OUTPUT {SQLDA } }}]
[ { { {AreaName} }}]
[ { }]
[ {[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 identifies a dynamically preprocessed statement
[(SectionNumber)] 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.
HostVariableSpecificationpecifies 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
1. 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.
2. 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