Dynamically executing a non-select SQL statement with parameter markers

Use PREPARE and EXECUTE for non-SELECT statements like INSERT, UPDATE, and DELETE with parameter markers.

Suppose that you want to execute UPDATE statements repeatedly using a list of hospital numbers. Suppose further that users enter a list of hospital numbers to update. You must construct and execute the entire statement dynamically. Your program must now do these things differently:

Use parameter markers instead of host variables
Use the PREPARE and EXECUTE statement

Parameter markers with PREPARE and EXECUTE: Dynamic SQL statements cannot use host variables. Therefore, you cannot dynamically execute an SQL statement that contains host variables. Instead, use parameter marker. A parameter marker is a question mark (?) that represents a position in a dynamic SQL statement where the application will provide a value.

Example using parameter markers:

DELETE FROM PCB01.HOSPITAL WHERE HOSPCODE = ?;

You associate the host variable HOSPCODE with the parameter marker when you execute the prepared statement.

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.

Using the PREPARE statement: Assume that the character host variable :STMTSTR has the value DELETE FROM PCB01.HOSPITAL WHERE HOSPCODE = ?. To prepare an SQL statement from that string and assign it the name S1, write:

EXEC SQLIMS PREPARE S1 FROM :STMTSTR;

The prepared statement still contains a parameter marker, for which you must supply a value when the statement executes. After the statement is prepared, the parameter marker enables you to execute the same statement many times with different values of the hospital code.

Using the EXECUTE statement: The EXECUTE statement executes a prepared SQL statement by naming a list of one or more host variables or a host structure. This list supplies values for all of the parameter markers. Suppose that S1 is the prepared statement and the parameter value is contained in the host variable HOSPCODE, write:

EXECUTE S1 USING :HOSPCODE

The EXECUTE statement can be executed using different values for HOSPCODE.

Using more than one parameter marker: The prepared statement (S1 in the example) can contain more than one parameter marker. If it does, the USING clause of EXECUTE specifies a list of variables or a host structure. The variables must contain values that match the number and data types of parameters in S1 in the proper order. You must know the number and types of parameters in advance and declare the variables in your program.

Example:

If two parameter markers are in STMT, you need the following statement:

EXEC SQLIMS 
  EXECUTE STMT USING :PARM1, :PARM2
END-EXEC.