Dynamically prepares, executes, and releases an SQL statement.
The EXECUTE IMMEDIATE statement is a dynamic SQL statement.
Dynamic SQL lets programs accept or generate SQL statements at
run time, in contrast to precompiled statements, which must be
embedded in the program before it is compiled. Unlike embedded
statements, such dynamically executed SQL statements are not
necessarily part of the program's source code, but can be created
while the program is running. Dynamic SQL is useful when you
cannot predict the type of SQL statement your program will need
to process.
The EXECUTE IMMEDIATE statement cannot contain parameter markers.
However, if the statement meets those restrictions and will
be dynamically executed only once, use the EXECUTE IMMEDIATE
statement instead of PREPARE and EXECUTE statements.
1 – Environment
You can use the EXECUTE IMMEDIATE statement:
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
2 – Format
EXECUTE IMMEDIATE --+-> '<statement-string>' +->
+-> <parameter> --------+
3 – Arguments
3.1 – statement-string
Specifies the SQL statement to be prepared and executed
dynamically. You either specify the statement string directly
in a character string literal enclosed in single quotation marks,
or in a parameter that contains the statement string.
Whether specified directly or by a parameter, the statement
string must be a character string that is a dynamically
executable SQL statement The form for the statement is the same
as in embedded SQL, except that you do not need to begin the
string with EXEC SQL.
3.2 – parameter
Specifies the SQL statement to be prepared and executed
dynamically. You either specify the statement string directly
in a character string literal enclosed in single quotation marks,
or in a parameter that contains the statement string.
Whether specified directly or by a parameter, the statement
string must be a character string that is a dynamically
executable SQL statement other than the SELECT statement
(the PREPARE lists the SQL statements that can be dynamically
executed). The form for the statement is the same as in embedded
SQL, except that you do not need to begin the string with EXEC
SQL or end it with any statement terminator.
4 – Example
Example 1: Executing an INSERT statement with the EXECUTE
IMMEDIATE statement
This COBOL program illustrates using the EXECUTE IMMEDIATE
statement to prepare and execute a dynamic INSERT statement.
Compare this example with the example for the EXECUTE statement
(see the EXECUTE), which uses an INSERT statement with parameter
markers and displays the result of the insert operation.
IDENTIFICATION DIVISION.
PROGRAM-ID. EXECUTE_IMMEDIATE_EXAMPLE.
*
* Illustrate EXECUTE_IMMEDIATE with a dynamic INSERT statement.
*
DATA DIVISION.
WORKING-STORAGE SECTION.
* Variable for DECLARE SCHEMA:
01 FILESPEC PIC X(20).
* Variables to hold values for
* storage in EMPLOYEES:
01 EMP_ID PIC X(5).
01 FNAME PIC X(10).
01 MID_INIT PIC X(1).
01 LNAME PIC X(14).
01 ADDR_1 PIC X(25).
01 ADDR_2 PIC X(25).
01 CITY PIC X(20).
01 STATE PIC X(2).
01 P_CODE PIC X(5).
01 SEX PIC X(1).
01 BDATE PIC S9(11)V9(7) COMP.
01 S_CODE PIC X(1).
* Indicator variables for retrieving
* the entire row, including columns we
* do not assign values to, from
* the EMPLOYEES table:
01 EMP_ID_IND PIC S9(4) COMP.
01 FNAME_IND PIC S9(4) COMP.
01 MID_INIT_IND PIC S9(4) COMP.
01 LNAME_IND PIC S9(4) COMP.
01 ADDR_1_IND PIC S9(4) COMP.
01 ADDR_2_IND PIC S9(4) COMP.
01 CITY_IND PIC S9(4) COMP.
01 STATE_IND PIC S9(4) COMP.
01 P_CODE_IND PIC S9(4) COMP.
01 SEX_IND PIC S9(4) COMP.
01 BDATE_IND PIC S9(4) COMP.
01 S_CODE_IND PIC S9(4) COMP.
* Buffer for error handling:
01 BUFFER PIC X(300).
01 LEN PIC S9(4) USAGE IS COMP.
* 01 disp_sqlcode pic s9(9) sign leading separate.
* Load definition for SQL Communication Area (SQLCA):
EXEC SQL INCLUDE SQLCA END-EXEC.
********************************************************************
*
* P R O C E D U R E D I V I S I O N
*
********************************************************************
PROCEDURE DIVISION.
START-UP.
* Assign value to FILESPEC:
MOVE "SQL$DATABASE" TO FILESPEC
* Declare the schema:
EXEC SQL DECLARE SCHEMA RUNTIME FILENAME :FILESPEC
END-EXEC
* Use an EXECUTE IMMEDIATE statement
* to execute an INSERT statement:
EXEC SQL EXECUTE IMMEDIATE
"INSERT INTO EMPLOYEES
- "(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,CITY)
- "VALUES ('99999','Les','Warton','Hudson')"
END-EXEC
PERFORM CHECK.
PERFORM FETCHES.
EXEC SQL EXECUTE IMMEDIATE 'ROLLBACK' END-EXEC.
PERFORM CHECK.
DISPLAY "Rolled back changes. All done.".
CLEAR-IT-EXIT.
EXIT PROGRAM.
FETCHES.
DISPLAY "Here's the row we stored:"
EXEC SQL PREPARE STMT FROM
'SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = "99999"'
END-EXEC
EXEC SQL DECLARE C CURSOR FOR STMT END-EXEC
EXEC SQL OPEN C END-EXEC
* Clear values in host language
* variables in case new values
* from the table are null:
MOVE SPACES TO EMP_ID
MOVE SPACES TO FNAME
MOVE SPACES TO MID_INIT
MOVE SPACES TO LNAME
MOVE SPACES TO ADDR_1
MOVE SPACES TO ADDR_2
MOVE SPACES TO CITY
MOVE SPACES TO STATE
MOVE SPACES TO P_CODE
MOVE SPACES TO SEX
MOVE ZERO TO BDATE
MOVE SPACES TO S_CODE
EXEC SQL FETCH C INTO
:EMP_ID:EMP_ID_IND,
:LNAME:LNAME_IND,
:FNAME:FNAME_IND,
:MID_INIT:MID_INIT_IND,
:ADDR_1:ADDR_1_IND,
:ADDR_2:ADDR_2_IND,
:CITY:CITY_IND,
:STATE:STATE_IND,
:P_CODE:P_CODE_IND,
:SEX:SEX_IND,
:BDATE:BDATE_IND,
:S_CODE:S_CODE_IND
END-EXEC
DISPLAY EMP_ID," ",
FNAME," ",
MID_INIT," ",
LNAME," ",
ADDR_1," ",
ADDR_2," ",
CITY," ",
STATE," ",
P_CODE," ",
SEX," ",
BDATE," ",
S_CODE.
PERFORM CHECK.
EXEC SQL CLOSE C END-EXEC.
CHECK.
IF SQLCODE NOT = 100 AND SQLCODE NOT = 0
DISPLAY "Error: SQLCODE = ", SQLCODE
CALL "SQL$GET_ERROR_TEXT" USING
BY DESCRIPTOR BUFFER,
BY REFERENCE LEN
DISPLAY BUFFER(1:LEN)
END-IF.