EXECUTE statement Format 1 (Execute SQL)

Function

The EXECUTE statement executes an SQL preprocessed by the PREPARE statement.

Privileges

None.

Format 1: Executing preprocessed SQL statements

EXECUTE { SQL-statement-identifier | extended-statement-name}
[{INTO:embedded-variable [:indicator-variable]
        [, :embedded-variable [:indicator-variable]]...
  |INTO DESCRIPTOR [:]SQL-descriptor-area-name
         [CHARACTER_SET[:]character-set-descriptor-area-name]}]
[{USING:embedded-variable [:indicator-variable]
        [, :embedded-variable [:indicator-variable]]...
  |USING DESCRIPTOR [:]SQL-descriptor-area-name
   [CHARACTER_SET[:]character-set-descriptor-area-name]}]

Operands

SQL-statement-identifier
Specifies the SQL statement identifier assigned to the SQL preprocessed by the PREPARE statement.
extended-statement-name
Specifies the extended statement name that identifies the SQL statement preprocessed by the PREPARE statement.
For extended statement names, see 2.26 Extended statement name.
embedded-variable
If retrieval results from the single-row SELECT statement preprocessed by the PREPARE statement are to be received, or the CALL statement preprocessed by the PREPARE statement includes an output ? parameter, and if the resulting value is to be received into an embedded variable, specifies an embedded variable that receives the value of the column of the retrieval results or the output ? parameter.
indicator-variable
Specifies the indicator variable to which a value is returned, indicating whether the value of the column of the retrieval results returned to the embedded variable or the value of the output parameter is the null value.
The indicator variable should be declared in the embedded SQL declaration section as an embedded variable having the SMALLINT data type.
If you omit the indicator variable, a null value cannot be returned to the embedded variable.
SQL-descriptor-area-name
When retrieving the results from a single-line SELECT statement preprocessed by a PREPARE statement, or the value of the output ? parameter in a CALL statement that uses the SQL descriptor area, specify the name of the SQL descriptor area that will contain the name of the variable that is to receive the name of the column containing the retrieval results, or the value of the output ? parameter.
character-set-descriptor-area-name
When retrieving the results from a single-line SELECT statement preprocessed by a PREPARE statement, or the value of the output ? parameter in a CALL statement that uses the SQL descriptor area, specify the name of the character set descriptor area into which the character set name of the variable that is to receive the name of the column of the retrieval results, or the value of the output ? parameter, is to be stored.
For details about character set descriptor areas, see the HiRDB Version 9 UAP Development Guide.
embedded-variable
If an SQL statement preprocessed by the PREPARE statement contains an input ? parameter and a value is to be assigned to the parameter from an embedded variable, specifies the embedded variable containing the value.
The number of embedded variables specified in the USING clause must be the same as the number of input ? parameters contained in the SQL that is executed by the EXECUTE statement. The embedded variables and the input ? parameters are associated with each other in the order in which they are listed.
The data type of an embedded variables specified in the USING clause must be compatible with the data types that are permitted for input ? parameters.
indicator-variable
Specifies an indicator variable that indicates whether or not the value of the paired embedded variable is the null value.
The indicator variable should be declared in the embedded SQL declaration section as an embedded variable with the SMALLINT data type.
If no indicator variable is specified, the value of that embedded variable is assumed to be a non-null value.
SQL-descriptor-area-name
If the SQL preprocessed by the PREPARE statement contains an input ? parameter, specifies the name of the SQL descriptor area containing the information that you will assign for that input ? parameter.
character-set-descriptor-area-name
When storing the value in the SQL descriptor area for an input ? parameter specified in the SQL preprocessed by the PREPARE statement, specifies the name of the character set descriptor area into which the character set name of the variable used to assign the value of the input ? parameter is to be stored.
For details about character set descriptor areas, see the HiRDB Version 9 UAP Development Guide.

Common rules

  1. An SQL executed by the EXECUTE statement must be preprocessed by the PREPARE statement.
  2. The PREPARE statement and the EXECUTE statement that executes the SQL preprocessed by the PREPARE statement must be executed in the same transaction.
  3. As an embedded variable name, an SQL statement identifier is effective in a compile-unit file. Multiple SQL statements referencing the same SQL statement identifier cannot be used in multiple modules.
  4. The number of embedded variables specified in the INTO clause, the number of columns of retrieval results from the single-row SELECT statement executed by the EXECUTE statement, and the number of output ? parameters included in the CALL statement must all be equal. In the SQL statement to be executed in a single-row SELECT statement, if the number of embedded variables is not equal to the number of columns, a warning flag W is assigned to the SQLWARN3 area of the SQL communications area. Notice that the embedded variables, the columns of retrieval results, and the output ? parameters are put in correspondence with one another from the beginning in the order in which they are listed.
  5. The data type of the embedded variables specified in the INTO clause should be the data type allowed in the corresponding retrieval result columns or output ? parameters.
  6. If the data fetched into the embedded variable of the fixed-length character string (including national character strings and mixed character strings) specified in the INTO clause is shorter than the defined length of the embedded variable, the data is inserted left-justified, and the remaining characters are blank-filled.
  7. If the value of retrieval result columns or the value of the output ? parameter in the CALL statement is the null value, the value of the corresponding embedded variable may be unpredictable.
  8. If the embedded variable specified in the INTO clause is character data type and uses the default character set, and the character set of the retrieval results column or output ? parameter of the CALL statement uses a different character set for character data type, it is automatically converted to the character set of the embedded variable.
  9. If the embedded variable specified in the USING clause is character data type and uses the default character set, and it is different from the character set of the input ? parameter, it is automatically converted to the character set of the input ? parameter.

Notes

  1. The EXECUTE statement can be executed any number of times once the SQL is preprocessed by the PREPARE statement.
  2. For details about SQL descriptor area settings, see the HiRDB Version 9 UAP Development Guide.

Examples

  1. Execute an SQL (SQL statement identifier is PRESQL) preprocessed by the PREPARE statement; specify an embedded variable (QUESTION) that assigns a value to a ? parameter in PRESQL:

    EXECUTE PRESQL USING :QUESTION

  2. Execute the SQL (SQL-statement-identifier: PRESQL) preprocessed by the PREPARE statement. The user must specify the SQL descriptor area (SQLDA) that is to store the information by which a value is assigned to the ? parameter of PRESQL.

    EXECUTE PRESQL USING DESCRIPTOR SQLDA

  3. Execute the SQL (SQL-statement-identifier: PRESQL) preprocessed by the PREPARE statement.

    CALL PROC1(?,?,?)

    The parameter mode for the first SQL parameter of the procedure PROC1 must be set to IN, the parameter mode for the second SQL parameter must be set to INOUT, and the parameter mode for the third SQL parameter must be set to OUT. The embedded variable XPARAM1 must be specified for the first SQL parameter, the embedded variable XPARAM2 for the second SQL parameter, and the embedded variable XPARAM3 for the third SQL parameter.

    EXECUTE PRESQL
      INTO  :XPARAM2, :XPARAM3
      USING :XPARAM1, :XPARAM2