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) ]}
  [USING {[SQL]DESCRIPTOR{[INPUT]{SQLDA
                                  AreaName1}
                          [AND OUTPUT{SQLDA
                                      AreaName2}]
                          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 [(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