Prepare a statement

In form, a dynamic SQL statement is like any other SQL statement that is written into a program, except that it cannot contain the names of any host variables.

A prepared SQL statement has two restrictions. First, if it is a SELECT statement, it cannot include the INTO variable clause. The INTO variable clause specifies host variables into which column data is placed, and host variables are not allowed in the text of a prepared object. Second, wherever the name of a host variable normally appears in an expression, a question mark (?) is written as a placeholder in the PREPARE statement. Only the PREPARE statement can specify question mark (?) placeholders.

You can prepare a statement in this form for execution with the PREPARE statement. The following example is written in :

EXEC SQL prepare query_2 from
       'SELECT * from orders
          WHERE customer_num = ? and order_date > ?';

The two question marks in this example indicate that when the statement is executed, the values of host variables are used at those two points.

You can prepare almost any SQL statement dynamically. The only statements that you cannot prepare are the ones directly concerned with dynamic SQL and cursor management, such as the PREPARE and OPEN statements. After you prepare an UPDATE or DELETE statement, it is a good idea to test the fifth field of SQLWARN to see if you used a WHERE clause (see SQLWARN array).

The result of preparing a statement is a data structure that represents the statement. This data structure is not the same as the string of characters that produced it. In the PREPARE statement, you give a name to the data structure; it is query_2 in the preceding example. This name is used to execute the prepared SQL statement.

The PREPARE statement does not limit the character string to one statement. It can contain multiple SQL statements, separated by semicolons. The following example shows a fairly complex transaction in :

strcpy(big_query, "UPDATE account SET balance = balance + ?
WHERE customer_id = ?; \ UPDATE teller SET balance = 
balance + ? WHERE teller_id = ?;");
EXEC SQL PREPARE big1 FROM :big_query;

When this list of statements is executed, host variables must provide values for six place-holding question marks. Although it is more complicated to set up a multistatement list, performance is often better because fewer exchanges take place between the program and the database server.