• 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 variable to an error code.
  • The sqlca.sqlerrd[4] field contains the offset into the statement text where the error was detected.

• how many rows (groups of values) the SQL statement returns:
  • Statements that return one row of data include a singleton SELECT and an EXECUTE FUNCTION statement.
  • Statements that can return more than one row of data require a cursor to execute; they include a non-singleton SELECT and an EXECUTE FUNCTION statement.
  • All other SQL statements, including EXECUTE PROCEDURE, return no rows of data.

For more information on how to execute statements that require cursors, see "Using a Database Cursor".

• whether the statement has input parameters.

• For SELECT and INSERT statements, use the OPEN...USING statement.
• For non-SELECT statements, use the EXECUTE...USING statement.

• whether you know the data types of statement columns at compile time:
  • When you know the number and data types of the columns at compile time, you can use host variables to hold the column values.

For more information, see "SQL Statements That Are Known at Compile Time".

• When you do not know the number and data types of columns at compile time, you must use the DESCRIBE statement to define the column and a dynamic-management structure to hold the column values.

For more information, see Chapter 15, "Determining SQL Statements."

Figure 14-4 summarizes how to execute the different types of prepared SQL statements.

Figure 14-4
Executing a Prepared SQL Statement

(1 of 2)

Type of SQL Statement	Input Parameters	Statement to Execute	Page Reference
Statements that do not return rows (except INSERT that is associated with a cursor)
With no input parameters	No	EXECUTE	14-40
When number and data types of input parameters are known	Yes	EXECUTE...USING	14-52
When number and data types of input parameters are not known	Yes	EXECUTE...USING SQL DESCRIPTOR EXECUTE...USING DESCRIPTOR	16-46 17-43
INSERT that is associated with a cursor
With no input parameters	No	OPEN	14-45
When number and data types of input parameters (insert columns) are known	Yes	OPEN...USING	14-54, 15-25
When number and data types of input parameters are not known	Yes	OPEN...USING SQL DESCRIPTOR OPEN...USING DESCRIPTOR	16-29 17-30
Statements that can return more than one row: non-singleton SELECT, SPL function
With no input parameters	No	OPEN	14-45
When number and data types of select-list columns are not known	No	OPEN	16-15, 17-18
When number and data types of return values are not known	No	OPEN	16-28, 17-28
When number and data types of input parameters are known	Yes	OPEN...USING	14-54
When number and data types of input parameters are not known	Yes	OPEN...USING SQL DESCRIPTOR OPEN...USING DESCRIPTOR	16-37 17-34
Statements that return only one row: singleton SELECT, any external function or an SPL function that returns only one group of values
With no input parameters	No	EXECUTE...INTO	14-43
When number and data types of returned values are not known	No	EXECUTE...INTO DESCRIPTOR EXECUTE...INTO SQL DESCRIPTOR	16-13, 16-23 17-17, 17-28
When number and data types of input parameters are known	Yes	EXECUTE...INTO ...USING	14-52
When number and data types of input parameters are not known	Yes	EXECUTE...INTO ...USING SQL DESCRIPTOR EXECUTE...INTO ...USING DESCRIPTOR	16-43 17-41

Freeing Resources

• If the statement is associated with a cursor, use CLOSE to close the cursor after all the rows are fetched (or inserted).
• Use the FREE statement to release the resources allocated for the prepared statement and any associated cursor. Once you have freed a prepared statement, you can no longer use it in your program until you reprepare or redeclare it. However, once you declare the cursor, you can free the associated statement identifier but not affect the cursor.

If your program uses a dynamic-management structure to describe an SQL statement at runtime, also deallocate the resources of this structure once the structure is no longer needed. For information on how to deallocate a system-descriptor area, see "Freeing Memory Allocated to a System-Descriptor Area". For information on how to deallocate an sqlda structure, see "Freeing Memory Allocated to an sqlda Structure".