A statement identifier can represent only one SQL statement or series of statements at a time. A new PREPARE statement can specify an existing statement identifier if you want to bind the identifier to a different SQL statement text.

The PREPARE statement supports dynamic statement-identifier names, which allow you to prepare a statement identifier as an identifier or as a host variable of a data type that can contain a character string. The first example that follows shows a statement identifier that was specified as a host variable. The second specifies a statement identifier as a character string.

stcopy ("query2", stmtid);
EXEC SQL prepare :stmtid from 'select * from customer';

EXEC SQL prepare query2 from 'select * from customer';

The variable must be a character data type. In C, it must be declared as char.

The PREPARE statement can take statement text either as a quoted string or as text that is stored in a program variable. The following restrictions apply to the statement text:

• The text can contain only SQL statements. It cannot contain statements or comments from the host programming language.
• The text can contain comments preceded by a double hyphen (--), or that are enclosed in braces ( { } ) or in C-style slash and asterisk ( /* */ ) delimiters.

  These symbols introduce or enclose SQL comments. For more information on SQL comment symbols, see How to Enter SQL Comments.

• The text can contain either a single SQL statement or a series of statements that are separated by semicolon ( ; ) symbols.

  For a list of SQL statements that cannot be prepared, see Restricted Statements in Single-Statement Prepares. For more information on how to prepare multiple SQL statements, see Preparing Multiple SQL Statements.

• The text cannot include an embedded SQL statement prefix or terminator, such as a dollar sign ( $ ) or the words EXEC SQL.
• Host-language variables are not recognized as such in prepared text.

  Therefore, you cannot prepare a SELECT (or EXECUTE FUNCTION or EXECUTE PROCEDURE) statement that includes an INTO clause, because the INTO clause requires a host-language variable.

• The only identifiers that you can use are names that are defined in the database, such as names of tables and columns. For more information on how to use identifiers in statement text, see Preparing Statements with SQL Identifiers.
• Use a question mark ( ? ) as a placeholder to indicate where data is supplied when the statement executes, as in this ESQL/C example:

  EXEC SQL prepare new_cust from
         'insert into customer(fname,lname) values(?,?)';

For more information on how to use question marks as placeholders, see Preparing Statements That Receive Parameters.

In Dynamic Server, if the prepared statement contains the Collection-Derived-Table segment or an ESQL/C collection variable, some additional limitations exist on how you can assemble the text for the PREPARE statement. For information about dynamic SQL, see the IBM Informix ESQL/C Programmer's Manual.

The way to prepare a user-defined routine (UDR) depends on whether the UDR is a user-defined procedure or a user-defined function:

• To prepare a user-defined procedure, prepare the EXECUTE PROCEDURE statement that executes the procedure.

  To execute the prepared procedure, use the EXECUTE statement.

• To prepare a user-defined function, prepare the EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement that executes the function.

  You cannot include the INTO clause of EXECUTE FUNCTION (or EXECUTE PROCEDURE) in the PREPARE statement.

How to execute a prepared user-defined function depends on whether it returns only one group or multiple groups of values. Use the EXECUTE statement for user-defined functions that return only one group of values.

To execute user-defined functions that return more than one group of return values, you must associate the EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement with a cursor.

In general, you can prepare any data manipulation language (DML) statement.

In Dynamic Server, you can prepare any single SQL statement except the following statements:

ALLOCATE COLLECTION ALLOCATE DESCRIPTOR ALLOCATE ROW CLOSE CONNECT CREATE FUNCTION FROM CREATE PROCEDURE FROM CREATE ROUTINE FROM DEALLOCATE COLLECTION DEALLOCATE DESCRIPTOR DEALLOCATE ROW DECLARE DESCRIBE DISCONNECT EXECUTE EXECUTE IMMEDIATE FETCH

FLUSH FREE GET DESCRIPTOR GET DIAGNOSTICS INFO LOAD OPEN OUTPUT PREPARE PUT SET AUTOFREE SET CONNECTION SET DEFERRED_PREPARE SET DESCRIPTOR UNLOAD WHENEVER

In Extended Parallel Server, you can prepare any single SQL statement except the following statements:

ALLOCATE DESCRIPTOR CLOSE CONNECT CREATE PROCEDURE FROM DEALLOCATE DESCRIPTOR DECLARE DESCRIBE DISCONNECT EXECUTE EXECUTE IMMEDIATE FETCH FLUSH FREE

GET DESCRIPTOR GET DIAGNOSTICS INFO LOAD OPEN OUTPUT PREPARE PUT SET CONNECTION SET DEFERRED_PREPARE SET DESCRIPTOR UNLOAD WHENEVER

You can prepare a SELECT statement. If SELECT includes the INTO TEMP clause, you can execute the prepared statement with an EXECUTE statement. If it does not include the INTO TEMP clause, the statement returns rows of data. Use DECLARE, OPEN, and FETCH cursor statements to retrieve the rows.

A prepared SELECT statement can include a FOR UPDATE clause. This clause is used with the DECLARE statement to create an update cursor. The next example shows a SELECT statement with a FOR UPDATE clause in ESQL/C:

sprintf(up_query, "%s %s %s",
   "select * from customer ",
   "where customer_num between ? and ? ",
   "for update");
EXEC SQL prepare up_sel from :up_query;
EXEC SQL declare up_curs cursor for up_sel;
EXEC SQL open up_curs using :low_cust,:high_cust; 

In some prepared statements, all necessary information is known at the time the statement is prepared. The following example in ESQL/C shows two statements that were prepared from constant data:

sprintf(redo_st, "%s %s",
   "drop table workt1; ",
   "create table workt1 (wtk serial, wtv float)" );
EXEC SQL prepare redotab from :redo_st;

In some statements, parameters are unknown when the statement is prepared because a different value can be inserted each time the statement is executed. In these statements, you can use a question-mark ( ? ) placeholder where a parameter must be supplied when the statement is executed.

The PREPARE statements in the following ESQL/C examples show some uses of question-mark ( ? ) placeholders:

EXEC SQL prepare s3 from
   'select * from customer where state matches ?';
EXEC SQL prepare in1 from 'insert into manufact values (?,?,?)';
sprintf(up_query, "%s %s", 
   "update customer set zipcode = ?"
   "where current of zip_cursor"); 
EXEC SQL prepare update2 from :up_query;
EXEC SQL prepare exfunc from
   'execute function func1 (?, ?)';

You can use a placeholder to defer evaluation of a value until runtime only for an expression, but not for an SQL identifier, except as noted in Preparing Statements with SQL Identifiers.

The following example of an ESQL/C code fragment prepares a statement from a variable that is named demoquery. The text in the variable includes one question-mark ( ? ) placeholder. The prepared statement is associated with a cursor and, when the cursor is opened, the USING clause of the OPEN statement supplies a value for the placeholder:

EXEC SQL BEGIN DECLARE SECTION;
   char queryvalue [6];
   char demoquery  [80];
EXEC SQL END DECLARE SECTION;

EXEC SQL connect to 'stores_demo';
sprintf(demoquery, "%s %s",
      "select fname, lname from customer ",
      "where lname > ? ");
EXEC SQL prepare quid from :demoquery;
EXEC SQL declare democursor cursor for quid;
stcopy("C", queryvalue);
EXEC SQL open democursor using :queryvalue;

The USING clause is available in both OPEN statements that are associated with a cursor and EXECUTE statements (all other prepared statements).

You can use a question-mark ( ? ) placeholder to represent the name of an ESQL/C or SPL collection variable.

In general, you must specify SQL identifiers explicitly in the statement text when you prepare the statement. In a few special cases, however, you can use the question-mark ( ? ) placeholder for an SQL identifier:

• For the database name in the DATABASE statement.
• For the dbspace name in the IN dbspace clause of the CREATE DATABASE statement.
• For the cursor name in statements that use cursor names.

Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]