Dynamically executing SQL for non-SELECT statements

The easiest way to use dynamic SQL is to use non-SELECT statements such as the INSERT, UPDATE, or DELETE statement.

Procedure

Your program must take the following steps:

  1. Include an SQLIMSCA.
  2. Load the input SQL statement into a data area.
  3. Execute the statement.
    • PREPARE and EXECUTE
  4. Handle any errors that might result. The return code from the most recently executed SQL statement appears in the host variables SQLIMSCODE and SQLIMSSTATE or corresponding fields of the SQLIMSCA.
  5. Check SQLIMSERRD(3) for the number of rows being changed.
Example:

Suppose that your program updates the hospital name by dynamically executing the UPDATE statement of this form:

UPDATE HOSPITAL SET HOSPNAME = 'MISSION CREEK'
WHERE HOSPITAL.HOSPCODE = 'H001007'

In this example, the UPDATE statement is stored in a host variable STMTSTR.

Declare the statement: 
EXEC SQLIMS
  DELCARE STMT STATEMENT
END-EXEC.

Declaring a varying-length character host variable: Before you prepare and execute an SQL statement, you have to assign it into a host variable. Declare varying-length character host variable for the SQL statement. The first two bytes must contain the length of the SQL statement. The maximum length of the SQL statement is 32K. For example:

01  STMTSTR.                                   
    49  STMTSTR-LEN  PIC S9(4) COMP VALUE +180.
    49  STMTSTR-TXT  PIC X(180) VALUE SPACES.
Preparing the statement:

Prepare a statement (STMT) from the STMTSTR host variable. 

EXEC SQLIMS 
PREPARE STMT FROM :STMTSTR
END-EXEC.
Executing the statement:

To execute STMT, your program use the EXECUTE call.

EXEC SQLIMS
  EXECUTE STMT
END-EXEC.