EXECUTE

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

The authorization rules are those defined for the SQL statement specified by EXECUTE. For example, see the description of INSERT

for the authorization rules that apply when an INSERT statement is executed using EXECUTE.

The authorization ID of the statement is the run-time authorization ID unless DYNUSRPRF(*OWNER) was specified on the CRTSQLxxx command when the program was created. For more information, see "Authorization IDs and Authorization-Names".

Syntax

>>-EXECUTE--statement-name-------------------------------------->
 
>-----+------------------------------------+-------------------><
      |        .-,----------------.        |
      |        V                  |        |
      +-USING-----host-variable---+--------+
      '-USING DESCRIPTOR--descriptor-name--'
 

Description

statement-name

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

USING

Introduces a list of host variables whose values are substituted for the parameter markers (question marks) in the prepared statement. For an explanation of parameter markers, see PREPARE. If the prepared statement includes parameter markers, the USING clause must be used. USING is ignored if there are no parameter markers.

host-variable,...

Identifies one of more host structures or variables that must be declared in the program in accordance with the rules for declaring host structures and variables. A reference to a host structure is replaced by a reference to each of its variables. The number of variables must be the same as the number of parameter markers in the prepared statement. The nth variable corresponds to the nth parameter marker in the prepared statement.

DESCRIPTOR descriptor-name

Identifies an 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 SQLDA. (The rules for REXX are different. For more information, see the SQL Programming with Host Languages book.)

• 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*(80), where 80 is the length of an SQLVAR occurrence. If LOBs or distinct types are specified, there must be two SQLVAR entries for each parameter marker and SQLN must be set to two times the number of parameter markers.

SQLD must be set to a value greater than or equal to zero and less than or equal to SQLN. It must be the same as the number of parameter markers in the prepared statement. The nth variable described by the SQLDA corresponds to the nth parameter marker in the prepared statement. (For a description of an SQLDA, see Appendix C, SQL Descriptor Area (SQLDA).)

Note that RPG/400 does not provide the function for setting pointers. Because the SQLDA uses pointers to locate the appropriate host variables, you have to set these pointers outside your RPG/400 application.

Parameter Marker Replacement

Before the prepared statement is executed, each parameter marker in the statement is effectively replaced by its corresponding host variable. With the exception of the LIKE predicate, the replacement of a parameter marker is an assignment operation in which the source is the value of the host variable, and the target is a variable within the database manager. The attributes of the target variable are determined as follows:

• If the parameter marker was specified as the operand of an arithmetic operator, the data type, scale, and precision of the target are the same as the other operand of that operator.
• If the parameter marker was specified in an assignment operand, the attributes of the target are the same as the other operand unless the data type of that operand is DATE, TIME, or TIMESTAMP, in which case, the target is effectively character with a length attribute of 32766. A parameter marker is an assignment operand in a SET clause of an UPDATE statement, in the VALUES clause of an INSERT statement, or in the select-list of a select-statement in an INSERT statement.
• If the parameter marker was specified as a comparison operand in any predicate other than the BETWEEN, IN, or LIKE predicates, the attributes of the target are the same as the other operand of the predicate, unless the data type of that operand is DATE, TIME, or TIMESTAMP, in which case, the target is effectively character with a length attribute of 32766.
• If the parameter marker was specified as a comparison operand in a BETWEEN or IN predicate, the attributes of the target are assigned as follows:
  • If only one operand is not a parameter marker, the attributes of the target are the attributes of that operand.
  • If more than one operand is not a parameter marker, the attributes of the target are the attributes that would result if those operands were specified in a VALUE scalar function, except the CCSID attribute is the CCSID of its corresponding host variable.
• If the parameter marker was specified as the pattern or ESCAPE character of a LIKE predicate, the host variable used to replace the parameter marker must be compatible with the first operand.

  If the parameter marker was specified as the pattern, the data type of the target is VARCHAR (if the first operand is CHAR or VARCHAR) or VARGRAPHIC (if the first operand is GRAPHIC or VARGRAPHIC). The length attribute of the target is the same as that of the first operand.

  If the parameter marker was specified as the ESCAPE character, the length attribute of the target is 1.

  Note:

  When the pattern specified in a LIKE predicate is a parameter marker, and a fixed-length character host variable is used to replace the parameter marker; specify a value for the host variable that is the correct length. If you do not specify the correct length, the select will not return the intended results. For example, if the host variable is defined as CHAR(10), and the value WYSE% is assigned to that host variable, the host variable is padded with blanks on assignment. The pattern used is ('WYSE%     '). This pattern requests the database manager to search for all values that start with 'WYSE' and end with '     '. If you intended to search for only the values that start with 'WYSE' you should assign the value 'WSYE%%%%%%' to the host variable.

• If the parameter marker was specified as an operand in a VALUE, COALESCE, IFNULL, MIN, MAX, LAND, LOR, or XOR scalar function, the attributes of the target are assigned as follows:
  • If only one operand is not a parameter marker, the attributes of the target are the attributes of that operand.
  • If more than one operand is not a parameter marker, the attributes of the target are the attributes that would result if the parameter markers were removed from the scalar function.

Let V denote a host variable 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 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.
• If the target cannot contain nulls, the value of V must not be null.

However, unlike the rules for assigning a value to a column:

• If V is a string, the value will be truncated (without an error), if its length is greater than the length attribute 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. 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.

Example

This example of portions of a COBOL program shows how an INSERT statement with parameter markers is prepared and executed.

   EXEC SQL  BEGIN DECLARE SECTION  END-EXEC.
     77 EMP              PIC X(6).
     77 PRJ              PIC X(6).
     77 ACT              PIC S9(4) COMP-4.
     77 TIM              PIC S9(3)V9(2).
     01 HOLDER.
        49  HOLDER-LENGTH    PIC S9(4) COMP-4.
        49  HOLDER-VALUE     PIC X(80).
   EXEC SQL  END DECLARE SECTION  END-EXEC.
   .
   .
   .
   MOVE 70 TO HOLDER-LENGTH.
   MOVE "INSERT INTO EMP_ACT (EMPNO, PROJNO, ACTNO, EMPTIME)
 -               "VALUES (?, ?, ?, ?)" TO HOLDER.
   EXEC SQL  PREPARE MYINSERT FROM :HOLDER  END-EXEC.
 
   IF SQLCODE = 0
     PERFORM DO-INSERT THRU END-DO-INSERT
   ELSE
     PERFORM ERROR-CONDITION.
 
   DO-INSERT.
     MOVE "000010" TO EMP.
     MOVE "AD3100" TO PRJ.
     MOVE 160      TO ACT.
     MOVE .50      TO TIM.
     EXEC SQL  EXECUTE MYINSERT USING :EMP, :PRJ, :ACT, :TIM  END-EXEC.
   END-DO-INSERT.
   .
   .
   .