Assemble the statement

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. Quotation marks or double quotation marks around the statement text are valid, although the ANSI SQL standard specifies quotation marks. For example:
EXEC SQL prepare slct_id from 
   'select company from customer where customer_num = 101';
Tip: Although 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 allows 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
Alternatively, you can copy the statement into a char variable as shown in the following code fragment.
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 are not discovered until run time (by the PREPARE statement). If you statically execute the statement, the preprocessor can uncover syntactic errors at compile time (semantic errors might remain undiagnosed until run time). 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 preceding code fragment, 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. 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.
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.
  • You can use string library functions like stcopy() and stcat().
The following code fragment shows the SELECT statement of the preceding code fragment 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;
The following code fragment 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 statements 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 HCL OneDB™ 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.