The PREPARE and EXECUTE statements

The PREPARE and EXECUTE statements allow you to separate the execution of a non-SELECT statement into two steps:
  1. PREPARE sends the statement string to the database server, which parses the statement and assigns it a statement identifier.
  2. EXECUTE executes the prepared statement indicated by a statement identifier.

This two-step process is useful for statements that need to be executed more than once. You reduce the traffic between the client application and the database server when you parse the statement only once.

For example, you can write a general-purpose deletion program that works on any table. This program would take the following steps:
  1. Prompt the user for the name of the table and the text of the WHERE clause and put the information into C variables such as tabname and search_condition. The tabname and search_condition variables do not need to be host variables because they do not appear in the actual SQL statement.
  2. Create a text string by concatenating the following four components: DELETE FROM, tabname, WHERE, and search_condition. In this example, the string is in a host variable called stmt_buf:
    sprintf(stmt_buf, "DELETE FROM %s WHERE %s",
      tabname, search_condition);
  3. Prepare the entire statement. The following PREPARE statement operates on the string in stmt_buf and creates a statement identifier called d_id: 
    EXEC SQL prepare d_id from :stmt_buf;
  4. Execute the statement. The following EXECUTE statement executes the DELETE: 
    EXEC SQL execute d_id;
  5. If you do not need to execute the statement again, free the resources used by the statement identifier structure. This example would use the following FREE statement: 
    EXEC SQL free d_id;

If the non-SELECT statement contains input parameters, you must use the USING clause of the EXECUTE statement.

The EXECUTE statement is generally used to execute non-SELECT statements. You can use EXECUTE with the INTO clause for a SELECT or an EXECUTE FUNCTION statement as long as these statements return only one group of values (one row). However, do not use the EXECUTE statement for:
  • An INSERT...VALUES statement that is associated with an insert cursor.
  • An EXECUTE FUNCTION statement for a cursor function (a user-defined function that returns more than one group of values).