Your Browser is not longer supported

Please use Google Chrome, Mozilla Firefox or Microsoft Edge to view the page correctly
Loading...

{{viewport.spaceProperty.prod}}

Dynamic preparation and execution of SQL statements

To start with, a brief example will be used to introduce dynamic SQL. This example will not use an SQL descriptor area. In the example, the SQL statements are marked with EXEC SQL and END-EXEC as in ESQL-COBOL. The actions carried out by the host language of the ESQL program are shown in italics.


Example

In the ORDERS table, the order status is to be changed to 3 in the row with job number 251 and customer number 105. This can be done with the following statement in static SQL:

UPDATE orders SET order_status=3 WHERE order_num=251 AND cust_num=105

This statement corresponds to the following sequence of statements in dynamic SQL:

    1. Declaration of the host variables Sourcestmt and Condition Sourcestmt='UPDATE orders SET order_status=3 WHERE order_num=251'

    2. Read the text 'AND cust_num=105' into Condition Sourcestmt=Sourcestmt||Condition

    3. EXEC SQL EXECUTE IMMEDIATE :Sourcestmt END_EXEC

Explanation

    1. Sourcestmt is a host variable declared in the ESQL program and which is assigned an alphanumeric string using the UPDATE statement.

    2. The UPDATE statement is expanded to include a condition by concatenating the text contained in Sourcestmt with the text in Condition read in at runtime. Whereas static SQL requires the statement text for the UPDATE statement to be defined before compilation, dynamic SQL only requires the statement to be defined at runtime.

    3. The EXECUTE IMMEDIATE statement checks, compiles and executes the statement.


Example

A more generalized form of the UPDATE statement shown above can be achieved with the following statement in static SQL:

UPDATE orders SET order_status=:ORDER_STATUS WHERE order_num=:ORDER_NUM

AND cust_num=105

Here, the values for ORDER_STATUS and ORDER_NUM are replaced by two host variables whose values are only defined at runtime. This statement corresponds to the following sequence of statements in dynamic SQL:

    1. Declaration of the host variables SOURCEMT, CONDITION, HOSTVAR1 and HOSTVAR2 Sourcestmt='UPDATE orders SET order_status= ? WHERE order_num= ?'

    2. Read the text 'AND cust_num=105' into CONDITION sourcestmt=sourcestmt||condition

    3. EXEC SQL PREPARE dynstmt FROM :SOURCESTMT END-EXEC

    4. Read the value 3 into HOSTVAR1 and 251 into HOSTVAR2

      EXEC SQL EXECUTE dynstmt USING :HOSTVAR1,:HOSTVAR2 END-EXEC

Explanation

    1. The UPDATE statement contains two placeholders, represented by question marks in place of the values for ORDER_STATUS and ORDER_NUM in the WHERE condition.

    2. The UPDATE statement is expanded to include a condition by concatenating the text contained in SOURCESTMT with the text in CONDITION read in at runtime.

    3. This dynamically compilable UPDATE statement is compiled with the following PREPARE statement. DYNSTMT stands for the dynamically compiled SQL statement. This name can then be used to address the statement in an EXECUTE statement.

    4. Before the EXECUTE statement is executed, values for the host variables HOSTVAR1 and HOSTVAR2, which were declared in the ESQL program, are read in. These values are to replace the placeholders “?” in the UPDATE statement. The EXECUTE statement then executes the statement DYNSTMT as prepared by PREPARE.

Example

The cursor CUR_SERVICE is defined for the SERVICES table. The cursor
description is to be generated at program runtime. The user can enter the order
number and the condition that are to apply to the services that are to be output.

  1. Declaration of the host variables ORD_NUM_ENTRY, CONDITION and
    DESCRIPTION. In addition, the SQL variable ORDER_NUM is defined. This takes
    the value of the order number from ORD_NUM_ENTRY.

  2. Define the cursor CUR_SEVICE:

    EXEC SQL
     DECLARE cur_service CURSOR FOR CUR_DESCRIPTION
END-EXEC

  3. Entry of values for ORD_NUM_ENTRY, and CONDITION by the user at program
    runtime.

  4. Generation of the description DESCRIPTION:

    STRING "SELECT * FROM services WHERE order_num = ? AND "
condition

  5. Generate cursor description:

    EXEC SQL
     PREPARE CUR_DESCRIPTION FROM :DESCRIPTION
END-EXEC

  6. Open the cursor CUR_SERVICE. The placeholder “?” in the cursor
    description is now replaced by the order number selected by the user:

    EXEC SQL
     OPEN cur_service USING :ORDER_NUM
END-EXEC

The statements EXECUTE IMMEDIATE, PREPARE and EXECUTE used in the examples are described in detail below. There first follows an overview of the statements which can be dynamically compiled.

Dynamically compilable statements

The following statements can be dynamically compiled (refer to "SQL statements for schema definition and administration" for details on the classification of the statements):

  • SQL statements for schema definition and administration

  • SQL statements for designing and managing routines

  • SQL statements for querying and updating data:

    • SELECT (without INTO clause)

    • UPDATE

    • DELETE

    • INSERT (without RETURN INTO clause)

    • MERGE

    • CALL

  • SQL statements for transaction management

  • SQL statements for session control

  • SQL statements for managing the storage structure

  • SQL statements for managing user entries

  • Utility statements

In addition, cursor descriptions can be dynamically compiled.

The following statements cannot be dynamically compiled

  • the SQL statements for dynamic SQL

  • the following SQL statements for working with a cursor:

    • DECLARE CURSOR

    • OPEN

    • FETCH

    • CLOSE

    • STORE

    • RESTORE

  • the SQL statements INCLUDE and WHENEVER.

EXECUTE IMMEDIATE statement

You use the EXECUTE IMMEDIATE statement to prepare and execute a dynamic statement in one step. In other words, EXECUTE IMMEDIATE corresponds to a PREPARE statement immediately followed by an EXECUTE statement. The statement does not, however, remain prepared and cannot be executed again with EXECUTE.

The statement text cannot include any host variables or question marks as placeholders for unknown values.

PREPARE statement

You use PREPARE to prepare a dynamic statement or the cursor description of a dynamic cursor for execution at a later time.

You execute a statement prepared with PREPARE with the EXECUTE statement.

The statement text cannot include any host variables. Question marks are specified as placeholders for unknown values .

Placeholder

The dynamically compilable statement or dynamically compilable cursor description in statement variable may not contain any host variables. Instead, they may contain placeholders for input values which are represented by question marks. These placeholders are provided with values with the USING clause of a subsequent EXECUTE or OPEN statement.

An SQL data type is determined for every placeholder. There are a number of rules which restrict the use of placeholders in statement_variable. A placeholder is only permitted in a context in which the data type of the placeholder can be determined uniquely.

For this reason, placeholders are not permitted

  • as elements of a select list in the form “?” (“SELECT ?+1” would, however be permitted)

  • as an operand of a monadic operator

  • as an argument of an aggregate function

  • in a query for a null value

  • if both operands of a dyadic operator or a comparison operator are placeholders

  • if, in a range query with BETWEEN where the first operand is a placeholder, one of the other two operands is also a placeholder

  • as the first operand in pattern matching with LIKE

  • if, when comparing an expression with a set of values, both the expression to be compared and the first value in the list following IN are placeholders.

  • if all operands are placeholders in a CASE expression. If the CASE expression contains one or more THEN or ELSE clauses, not all the operands in these clauses can be placeholders.

  • as operands of the string functions LOWER and UPPER

  • as the first operand (character) and/or second operand (expression) of the string function TRIM (e.g. TRIM (TRAILING FROM ?))

  • as the first operand of the string function SUBSTRING (e.g. SUBSTRING ? FROM 1 FOR 5)).

  • if both operands of the numeric function POSITION are placeholders.

EXECUTE statement

You use EXECUTE to execute a statement prepared with PREPARE. Placeholders for input values in the dynamic statement are replaced by specific values.

If the statement is a SELECT statement, the column values of the derived rows are stored in host variables or in an SQL descriptor area.

You can use EXECUTE to execute a previously prepared statement any number of times.

A statement can only be executed with EXECUTE in the compilation unit in which it was previously prepared with PREPARE.

Host variables or SQL descriptor areas can be used for input and output values in the EXECUTE statement. The use of SQL descriptor areas is described in the following section.

Powered by Atlassian Confluence and Scroll Viewport.