INFORMIX-ESQL/C Programmer's Manual Using Dynamic SQL

To execute an SQL statement, the database server must have the following information about the statement:

• 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

If information in an SQL statement varies according to some conditions in the application, your ESQL/C program can use dynamic SQL to build the SQL statement at runtime. The basic process to dynamically execute SQL statements consists of the following steps:

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.

Dynamic SQL allows you to assemble an SQL statement in a character string as the user interacts with your program. A dynamic SQL statement is like any other SQL statement that is embedded into a program, except that the statement string cannot contain the names of any host variables. The PREPARE statement sends the contents of an SQL statement string to the database server, which parses it and creates a statement identifier structure (statement identifier).

Assign the text for the SQL statement to a single host variable, which appears in the PREPARE statement. The key to dynamically execute an SQL statement is to assemble the text of the statement into a character string. You can assemble this statement string in the following two ways:

• 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

If you know the whole statement structure, you can list it after the FROM keyword of the PREPARE statement. Single quotes or double quotes around the statement text are valid, although the ANSI SQL standard specifies single quotes.

For example:

EXEC SQL prepare slct_id from 

	'select company from customer where customer_num = 101';


Tip: Although ESQL/C does not allow newline characters in quoted strings, you can include newline characters in the quoted string of a PREPARE statement. The quoted string is passed to the database server with the PREPARE statement and, if you specify that it should, the database server will allow newline characters in quoted strings. Therefore, you can allow a user to enter the preceding SQL statement from the command line as follows:
select lname from customer

where customer_num = 101

For more information on allowing newlines in quoted strings, see Including Newline in Quoted Strings.

Alternatively, you can copy the statement into a char variable as shown in Figure 14-1.

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 was 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, Programming with INFORMIX-ESQL/C.

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 4, Working with Character and String Data Types.

Figure 14-2 shows the SELECT statement of Figure 14-1 changed so that it uses a host variable to determine the customer number dynamically.

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 = ?';

You can prepare almost any SQL statement dynamically. The only statements that you cannot prepare dynamically 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.

You use the Collection Derived Table clause with an INSERT or SELECT statement to access an ESQL/C collection variable. (For more information on how to use the Collection Derived Table clause and collection variables, see Chapter 9 of this manual.)

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.

For collection variables, ESQL/C does not support statement text that is stored in a program variable.

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

To manipulate a collection variable, you must use the question mark (?) symbol to indicate an input parameter and then provide the collection variable when you execute the statement.

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

For example, the following ESQL/C code fragment prepares an INSERT on the a_set client 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). 

When PREPARE sends the statement string to the database server, the database server parses it to analyze it for errors. The database server indicates the success of the parse in the sqlca structure, as follows:

• 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.

Once an SQL statement was prepared, the database server can execute it. The way to execute a prepared statement depends on:

• 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.

If so, the statement must be executed with the USING clause:

• 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.

Freeing Resources

Sometimes you can ignore the cost of resources allocated to prepared statements and cursors. However, the number of prepared objects that the application can create is limited. Free resources that ESQL/C uses to execute a prepared statement, as follows:

• 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.

You can use the AUTOFREE feature to have the database server automatically free resources for a cursor and its prepared statement. For more information, see Automatically Freeing a 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.