DB2 10.5 for Linux, UNIX, and Windows

EXECUTE statement

The EXECUTE statement executes a prepared SQL statement.

Invocation

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

Authorization

For each global variable used as an expression in the USING clause or in the expression for an array-index, the privileges held by the authorization ID of the statement must include one of the following authorities:
  • READ privilege on the global variable that is not defined in a module
  • EXECUTE privilege on the module of the global variable that is defined in a module
For each global variable used as an assignment-target, the privileges held by the authorization ID of the statement must include one of the following authorities:
  • WRITE privilege on the global variable that is not defined in a module
  • EXECUTE privilege on the module of the global variable that is defined in a module

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

Read syntax diagramSkip visual syntax diagram
>>-EXECUTE--statement-name-------------------------------------->

>--+----------------------------------------------+------------->
   |         .-,-----------------.                |   
   |         V                   |                |   
   '-INTO--+---assignment-target-+--------------+-'   
           '-DESCRIPTOR--result-descriptor-name-'     

>--+----------------------------------------------+------------><
   |          .-,-----------------------.         |   
   |          V                         |         |   
   '-USING--+---+-input-host-variable-+-+-------+-'   
            |   |            (1)      |         |     
            |   '-expression----------'         |     
            '-DESCRIPTOR--input-descriptor-name-'     

assignment-target

|--+-global-variable-name-------------------+-------------------|
   +-host-variable-name---------------------+   
   +-SQL-parameter-name---------------------+   
   +-SQL-variable-name----------------------+   
   +-transition-variable-name---------------+   
   +-array-variable-name--[--array-index--]-+   
   '-field-reference------------------------'   

Notes:
  1. 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.

Notes

Examples

Example 1: In this C example, an INSERT statement with parameter markers is prepared and executed. Host variables h1 - h4 correspond to the format of TDEPT.
   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;
Example 2: This EXECUTE statement uses an SQLDA.
   EXECUTE S3 USING DESCRIPTOR :sqlda3
Example 3: Given a procedure to award an employee a bonus:
   CREATE PROCEDURE GIVE_BONUS (IN EMPNO INTEGER,
                             IN DEPTNO INTEGER,
                             OUT CHEQUE INTEGER,
                             INOUT BONUS DEC(6,0))
   ...
Dynamically call the procedure from a C application. The procedure takes the following host variables as input:
  • employee, the ID number of the employee
  • dept, the department number
  • bonus, the bonus to be awarded to the employee
The procedure returns the following values to the host variables:
  • cheque_no, the ID number from the cheque
  • bonus, the actual bonus amount (after any adjustments)
   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 */