Using the PREPARE and EXECUTE statements

 

If the non-SELECT statement does not contain parameter markers, you can run it dynamically using the EXECUTE IMMEDIATE statement. However, if the non-SELECT statement contains parameter markers, run it using the PREPARE and EXECUTE statements.

The PREPARE statement prepares the non-SELECT statement (for example, the DELETE statement) and gives it a statement name you choose. If DLYPRP (*YES) is specified on the CRTSQLxxx command, the preparation is delayed until the first time the statement is used in an EXECUTE or DESCRIBE statement, unless the USING clause is specified on the PREPARE statement. After the statement has been prepared, it can be run many times within the same program, using different values for the parameter markers. The following example is of a prepared statement being run multiple times:

  DSTRING = 'DELETE FROM CORPDATA.EMPLOYEE WHERE EMPNO = ?';
 
  /*The ? is a parameter marker which denotes     that this value is a host variable that is     to be substituted each time the statement is run.*/
 
  EXEC SQL PREPARE S1 FROM :DSTRING;
 
  /*DSTRING is the delete statement that the PREPARE statement is     naming S1.*/
 
   DO UNTIL (EMP =0);
  /*The application program reads a value for EMP from the     display station.*/
     EXEC SQL        EXECUTE S1 USING :EMP;
 
 
  END;

In routines similar to the example above, know the number of parameter markers and their data types, because the host variables that provide the input data are declared when the program is being written.

All prepared statements that are associated with an application server are destroyed whenever the connection to the application server ends. Connections are ended by a CONNECT (Type 1) statement, a DISCONNECT statement, or a RELEASE followed by a successful COMMIT.

 

Parent topic:

Processing non-SELECT statements