• The type of statement, such as SELECT, DELETE, EXECUTE PROCEDURE, or GRANT
• The names of any database objects, such as tables, columns, and indexes
• Any WHERE-clause conditions, such as column names and matching criteria
• Where to put any returned values, such as the column values from the select list of a SELECT statement
• Values that need to be sent to the database server, such as the column values for a new row for an INSERT statement

1. Assemble the text of an SQL statement in a character-string variable.

2. Use a PREPARE statement to have the database server examine the statement text and prepare it for execution.

3. Execute the prepared statement with the EXECUTE or OPEN statement.

4. Free dynamic resources that are used to execute the prepared statement.

Assembling the Statement

• As a fixed string, if you know all the information at compile time
• As a series of string operations, if you do not have all the information at compile time

EXEC SQL prepare slct_id from 

	'select company from customer where customer_num = 101';

Tip: The statement text in the preceding example is surrounded with single quotes. Double quotes are also valid, although single quotes are specified by the ANSI SQL standard.

stcopy("select company from customer where customer_num = 101", stmt_txt);
EXEC SQL prepare slct_id from :stmt_txt;

Both of these methods have the same restriction as a static SQL statement. They assume that you know the entire statement structure at compile time. The disadvantage of these dynamic forms over the static one is that any syntax errors encountered in the statement will not be discovered until runtime (by the PREPARE statement). If you statically execute the statement, the ESQL/C preprocessor can uncover syntactic errors at compile time (semantic errors might remain undiagnosed until runtime). You can improve performance when you dynamically execute an SQL statement that is to be executed more than once. The statement is parsed only once.

In Figure 14-1, the stmt_txt variable is a host variable because it is used in an embedded SQL statement (the PREPARE statement). Also the INTO clause of the SELECT statement has been removed because host variables cannot appear in a statement string. Instead, you specify the host variables in the INTO clause of an EXECUTE or FETCH statement (see page 14-10). Other SQL statements like DESCRIBE, EXECUTE, and FREE can access the prepared statement when they specify the slct_id statement identifier.

Important: By default, the scope of a statement identifier is global. If you create a multifile application and you want to restrict the scope of a statement identifier to a single file, preprocess the file with the -local preprocessor option. For more information on -local, see Chapter 1 of this manual.

If you do not know all the information about the statement at compile time, you can use the following features to assemble the statement string:

• The char host variables can hold the identifiers in the SQL statement (column names or table names) or parts of the statement like the WHERE clause. They can also contain keywords of the statement.
• If you know what column values the statement specifies, you can declare host variables to provide column values that are needed in a WHERE clause or to hold column values that are returned by the database server.
• Input-parameter placeholders, represented by a question mark (?), in a WHERE clause indicate a column value to be provided, usually in a host variable at time of execution. Host variables used in this way are called input parameters. For more information, see "Executing Statements with Input Parameters".
• You can use ESQL/C string library functions like stcopy() and stcat(). For more information, see Chapter 3, "Working with Character and String Data Types."

stcopy("select company from customer where customer_num = ", stmt_txt);

stcat(cust_num, stmt_txt);

EXEC SQL prepare slct_id from :stmt_txt;

Figure 14-3 shows how you can use an input parameter to program this same SELECT statement so that the user can enter the customer number

EXEC SQL prepare slct_id from 'select company from customer where customer_num = ?';

Figure 14-3 Using an Input Parameter to Assemble a SELECT Statement

.

You can prepare almost any SQL statement dynamically. The only ones that cannot be prepared are those directly concerned with dynamic SQL and cursor management (such as FETCH and OPEN), and the SQL connection statements. For a complete list of statements, see the PREPARE statement in the Informix Guide to SQL: Syntax.

Tip: You can use the Deferred-PREPARE feature to defer execution of a prepared SELECT, INSERT, or EXECUTE FUNCTION statement until the OPEN statement. For more information, see "Deferring Execution of the PREPARE Statement".

Preparing Statements That Have Collection Variables

When you prepare a statement that manipulates an ESQL/C collection variable, the following restrictions apply:

• You must specify the statement text as a quoted string in the PREPARE statement.

• The quoted string for the statement text cannot contain any collection host variables.

• You cannot perform multi-statement prepares if a statement contains a collection variable.

EXEC SQL BEGIN DECLARE SECTION;

	client collection set(integer not null) a_set;

EXEC SQL END DECLARE SECTION;

EXEC SQL prepare coll_stmt from 

	'insert into table(?) values (1, 2, 3)';

EXEC SQL execute coll_stmt using :a_set;

Important: You must declare an ESQL/C collection variable as a client collection variable (a collection variable that is stored on the client computer).

• If the syntax is correct, the database server sets the following sqlca fields:
  • The sqlca.sqlcode field (SQLCODE) contains zero (0).
  • The sqlca.sqlerrd[0] field contains an estimate of the number of rows affected if the parsed statement was a SELECT, UPDATE, INSERT, or DELETE.
  • The sqlca.sqlerrd[3] field contains an estimated cost of execution if the parsed statement was a SELECT, UPDATE, INSERT, or DELETE. This execution cost is a weighted sum of disk accesses and total number of rows processed.
• If the statement string contains a syntax error, or if some other error was encountered during the PREPARE, the database server sets the following sqlca fields:
  • The sqlca.sqlcode field (SQLCODE) is set to a negative number (<0). The database server also sets the SQLSTATE