EXECUTE statement

Invocation

This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared.

Authorization

For statements where authorization checking is performed at statement execution time (DDL, GRANT, and REVOKE statements), the privileges held by the authorization ID of the statement must include those required to execute the SQL statement specified by the PREPARE statement. The authorization ID of the statement might be affected by the DYNAMICRULES bind option.

For statements where authorization checking is performed at statement preparation time (DML), no further authorization checking is performed on the SQL statement specified by the PREPARE statement.

Syntax

assignment-target

Notes:

• ¹ An expression other than host-variable can only be used when the EXECUTE statement is used within a compound SQL (compiled) statement.

Description

statement-name

Identifies the prepared statement to be executed. The statement-name must identify a statement that was previously prepared, and the prepared statement cannot be a SELECT statement.

INTO

Introduces a list of targets which are used to receive values from output parameter markers in the prepared statement. Each assignment to a target is made in sequence through the list. If an error occurs on any assignment, the value is not assigned to the target, and no more values are assigned to targets. Any values that have already been assigned to targets remain assigned.

For a dynamic CALL statement, parameter markers appearing in OUT and INOUT arguments to the procedure are output parameter markers. If any output parameter markers appear in the statement, the INTO clause must be specified (SQLSTATE 07007).

assignment-target

Identifies one or more targets for the assignment of output values. The first value in the result row is assigned to the first target in the list, the second value to the second target, and so on.

If the data type of an assignment-target is a row type, then there must be exactly one assignment-target specified (SQLSTATE 428HR), the number of columns must match the number of fields in the row type, and the data types of the columns of the fetched row must be assignable to the corresponding fields of the row type (SQLSTATE 42821).

If the data type of an assignment-target is an array element, then there must be exactly one assignment-target specified.

global-variable-name

Identifies the global variable that is the assignment target.

host-variable-name

Identifies the host variable that is the assignment target. For LOB output values, the target can be a regular host variable (if it is large enough), a LOB locator variable, or a LOB file reference variable.

SQL-parameter-name

Identifies the routine parameter that is the assignment target.

SQL-variable-name

Identifies the SQL variable that is the assignment target. SQL variables must be declared before they are used.

transition-variable-name

Identifies the column to be updated in the transition row. A transition-variable-name must identify a column in the subject table of a trigger, optionally qualified by a correlation name that identifies the new value.

array-variable-name

Identifies an SQL variable, SQL parameter, or global variable of an array type.

array-index

An expression that specifies which element in the array will be the target of the assignment. For an ordinary array, the array-index expression must be assignable to INTEGER (SQLSTATE 428H1) and cannot be the null value. Its value must be between 1 and the maximum cardinality defined for the array (SQLSTATE 2202E). For an associative array, the array-index expression must be assignable to the index data type of the associative array (SQLSTATE 428H1) and cannot be the null value.

field-reference

Identifies the field within a row type value that is the assignment target. The field-reference must be specified as a qualified field-name where the qualifier identifies the row value in which the field is defined.

DESCRIPTOR result-descriptor-name

Identifies an output SQLDA that must contain a valid description of host variables.

Before the EXECUTE statement is processed, the user must set the following fields in the input SQLDA:

• SQLN to indicate the number of SQLVAR occurrences provided in the SQLDA
• SQLDABC to indicate the number of bytes of storage allocated for the SQLDA
• SQLD to indicate the number of variables used in the SQLDA when processing the statement
• SQLVAR occurrences to indicate the attributes of the variables.

The SQLDA must have enough storage to contain all SQLVAR occurrences. Therefore, the value in SQLDABC must be greater than or equal to 16 + SQLN*(N), where N is the length of an SQLVAR occurrence.

If LOB or structured data type output data must be accommodated, there must be two SQLVAR entries for every output parameter marker.

SQLD must be set to a value greater than or equal to zero and less than or equal to SQLN.

USING

Introduces a list of variables or expressions for which values are substituted for the input parameter markers in the prepared statement.

For a dynamic CALL statement, parameter markers appearing in IN and INOUT arguments to the procedure are input parameter markers. For all other dynamic statements, all the parameter markers are input parameter markers. If any input parameter markers appear in the statement, the USING clause must be specified (SQLSTATE 07004).

input-host-variable, ...

Identifies a host variable that is declared in the program in accordance with the rules for declaring host variables. The number of variables must be the same as the number of input parameter markers in the prepared statement. The nth variable corresponds to the nth parameter marker in the prepared statement. Locator variables and file reference variables, where appropriate, can be provided as the source of values for parameter markers.

expression

Identifies an expression to be used as the input for the corresponding input parameter marker in the prepared statement. An expression other than a host-variable can only be specified when the EXECUTE statement is issued within a compound SQL (compiled) statement.

DESCRIPTOR input-descriptor-name

Identifies an input SQLDA that must contain a valid description of host variables.

Before the EXECUTE statement is processed, the user must set the following fields in the input SQLDA:

• SQLN to indicate the number of SQLVAR occurrences provided in the SQLDA
• SQLDABC to indicate the number of bytes of storage allocated for the SQLDA
• SQLD to indicate the number of variables used in the SQLDA when processing the statement
• SQLVAR occurrences to indicate the attributes of the variables.

The SQLDA must have enough storage to contain all SQLVAR occurrences. Therefore, the value in SQLDABC must be greater than or equal to 16 + SQLN*(N), where N is the length of an SQLVAR occurrence.

If LOB or structured data type input data must be accommodated, there must be two SQLVAR entries for every parameter marker.

SQLD must be set to a value greater than or equal to zero and less than or equal to SQLN.

FOR host-variable or integer-constant ROWS

Specifies the number of rows of source data. The values for the insert or merge operation are specified in the USING clause.

host-variable or integer-constant is assigned to an integral value k. If host-variable is specified, it must be of type integer or short and must not include an indicator variable. k must be in the range 2 to 32767. If FOR host-variable or integer-constant ROWS is not provided, the SQL will be executed with array of size 1.

Notes

• Before the prepared statement is executed, each input parameter marker is effectively replaced by the value of its corresponding variable or expression. For a typed parameter marker, the attributes of the target variable or expression are those specified by the CAST specification. For an untyped parameter marker, the attributes of the target variable or expression are determined according to the context of the parameter marker.
  Let V denote an input variable or expression that corresponds to parameter marker P. The value of V is assigned to the target variable for P in accordance with the rules for assigning a value to a column. Thus:

  • V must be compatible with the target.
  • If V is a string, its length must not be greater than the length attribute of the target.
  • If V is a number, the absolute value of its integral part must not be greater than the maximum absolute value of the integral part of the target.
  • If the attributes of V are not identical to the attributes of the target, the value is converted to conform to the attributes of the target.

  When the prepared statement is executed, the value used in place of P is the value of the target variable for P or the result of the target expression for P. For example, if V is CHAR(6) and the target is CHAR(8), the value used in place of P is the value of V padded with two blanks.

• Both input and output parameters should be specified in both the USING and INTO clause for an anonymous block (dynamic compound sql).

• For a dynamic CALL statement, after the prepared statement is executed, the returned value of each OUT and INOUT argument is assigned to the assignment target corresponding to the output parameter marker used for the argument. For a typed parameter marker, the attributes of the target variable are those specified by the CAST specification. For an untyped parameter marker, the attributes of the target variable are those specified by the definition of the parameter of the procedure.
  Let V denote an output assignment target that corresponds to parameter marker P, which is used for argument A of a procedure. The value of A is assigned to V in accordance with the rules for retrieving a value from a column. Thus:

  • V must be compatible with A.
  • If V is a string, its length must not be less than the length of A, or the value of A will be truncated.
  • If V is a number, the maximum absolute value of its integral part must not be less than the absolute value of the integral part of A.
  • If the attributes of V are not identical to the attributes of A, the value of A is converted to conform to the attributes of V.
• Dynamic SQL statement caching: The information required to execute dynamic and static SQL statements is placed in the database package cache when static SQL statements are first referenced or when dynamic SQL statements are first prepared. This information stays in the package cache until it becomes invalid, the cache space is required for another statement, or the database is shut down.

  When an SQL statement is executed or prepared, the package information relevant to the application issuing the request is loaded from the system catalog into the package cache. The actual executable section for the individual SQL statement is also placed into the cache: static SQL sections are read in from the system catalog and placed in the package cache when the statement is first referenced; dynamic SQL sections are placed directly in the cache after they have been created. Dynamic SQL sections can be created by an explicit statement, such as PREPARE or EXECUTE IMMEDIATE. Once created, sections for dynamic SQL statements may be recreated by an implicit prepare of the statement by the system if the original section has been deleted for space management reasons, or has become invalid due to changes in the environment.

  Each SQL statement is cached at the database level and can be shared among applications. Static SQL statements are shared among applications using the same package; dynamic SQL statements are shared among applications using the same compilation environment, and the exact same statement text. The text of each SQL statement issued by an application is cached locally within the application for use if an implicit prepare is required. Each PREPARE statement in the application program can cache one statement. All EXECUTE IMMEDIATE statements in an application program share the same space, and only one cached statement exists for all these EXECUTE IMMEDIATE statements at a time. If the same PREPARE or any EXECUTE IMMEDIATE statement is issued multiple times with a different SQL statement each time, only the last statement will be cached for reuse. The optimal use of the cache is to issue a number of different PREPARE statements once at the start of the application, and then to issue an EXECUTE or OPEN statement as required.

  When dynamic SQL statements are cached, a statement can be reused over multiple units of work without needing to prepare the statement again, unless the SQL statements prepared in a package are bound with the KEEPDYNAMIC NO option. The system recompiles the statement if necessary when environment changes occur.

  The following events are examples of environment or data object changes that can cause cached dynamic statements to be implicitly prepared on the next PREPARE, EXECUTE, EXECUTE IMMEDIATE, or OPEN request:

  • ALTER FUNCTION
  • ALTER METHOD
  • ALTER NICKNAME
  • ALTER PROCEDURE
  • ALTER SERVER
  • ALTER TABLE
  • ALTER TABLESPACE
  • ALTER TYPE
  • CREATE FUNCTION
  • CREATE FUNCTION MAPPING
  • CREATE INDEX
  • CREATE METHOD
  • CREATE PROCEDURE
  • CREATE TABLE
  • CREATE TEMPORARY TABLESPACE
  • CREATE TRIGGER
  • CREATE TYPE
  • DROP (all objects)
  • RUNSTATS on any table or index
  • Any action that causes a view to become inoperative
  • UPDATE of statistics in any system catalog table
  • SET CURRENT DEGREE
  • SET PATH
  • SET QUERY OPTIMIZATION
  • SET SCHEMA
  • SET SERVER OPTION
  The following list outlines the behavior that can be expected from cached dynamic SQL statements:

  • PREPARE Requests: Subsequent preparations of the same statement do not incur the cost of compiling the statement if the section is still valid. The cost and cardinality estimates for the current cached section are returned. These values might differ from the values returned from any previous PREPARE for the same SQL statement. You do not need to issue a PREPARE statement subsequent to a COMMIT or ROLLBACK statement, unless the statement is associated with a package that was bound with KEEPDYNAMIC NO.
  • EXECUTE Requests: EXECUTE statements may occasionally incur the cost of implicitly preparing the statement if it has become invalid since the original PREPARE. If a section is implicitly prepared, it will use the current environment and not the environment of the original PREPARE statement.
  • EXECUTE IMMEDIATE Requests: Subsequent EXECUTE IMMEDIATE statements for the same statement will not incur the cost of compiling the statement if the section is still valid.
  • OPEN Requests: OPEN requests for dynamically defined cursors may occasionally incur the cost of implicitly preparing the statement if it has become invalid since the original PREPARE statement. If a section is implicitly prepared, it will use the current environment and not the environment of the original PREPARE statement.
  • FETCH Requests: No behavior changes should be expected.
  • ROLLBACK: Only those dynamic SQL statements prepared or implicitly prepared during the unit of work affected by the rollback operation are invalidated. Inactive dynamic SQL statements associated with a package bound with KEEPDYNAMIC NO are removed from the application SQL context after a ROLLBACK opeation and must be explicitly prepared again before the application can execute them. Dynamic SQL statements are still cached at the database level, so a subsequent PREPARE request does not incur the cost of compiling the statement if the section is still valid.
  • COMMIT: Dynamic SQL statements are not be invalidated, but any acquired locks are be freed. Cursors not defined with the WITH HOLD option are closed and their locks freed. Open cusors defined with the WITH HOLD option hold onto their package and section locks to protect the active section both during and after commit processing. Dynamic SQL statements bound with the KEEPDYNAMIC NO option are not in a prepared state after a transaction boundary and must be explicitly prepared again before the application can execute them. SELECT statements prepared for an open cursor defined with the WITH HOLD option remain in a prepared state until a transaction boundary is hit where the cursor is closed. Inactive dynamic SQL statements associated with a package bound with KEEPDYNAMIC NO are removed from the application SQL context after a commit operation and must be explicitly prepared again before the application can execute them.

  If an error occurs during an implicit prepare, an error will be returned for the request causing the implicit prepare (SQLSTATE 56098).

Examples

   strcpy (s,"INSERT INTO TDEPT VALUES(?,?,?,?)");
   EXEC SQL PREPARE DEPT_INSERT FROM :s;
     .
     .
   (Check for successful execution and put values into :h1, :h2, :h3, :h4)
     .
     .
   EXEC SQL EXECUTE DEPT_INSERT USING :h1, :h2,
   :h3, :h4;

   EXECUTE S3 USING DESCRIPTOR :sqlda3

   CREATE PROCEDURE GIVE_BONUS (IN EMPNO INTEGER,
                             IN DEPTNO INTEGER,
                             OUT CHEQUE INTEGER,
                             INOUT BONUS DEC(6,0))
   ...

   strcpy (s, "CALL GIVE_BONUS(?, ?, ?, ?)");
   EXEC SQL PREPARE DO_BONUS FROM :s;
     .
     .
   /* Check for successful execution and put values into
      :employee, :dept, and :bonus */
     .
     .
   EXEC SQL EXECUTE DO_BONUS INTO :cheque_no, :bonus
                          USING :employee, :dept, :bonus;
     .
     .
   /* Check for successful execution and process the
      values returned in :cheque_no and :bonus */