1 EXECUTE_IMMEDIATE 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. 2 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 --+-> '' +-> +-> --------+ 2 Arguments 3 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 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. 2 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.