Informix Guide to SQL: Syntax SQL Statements

Use the PREPARE statement to parse, validate, and generate an execution plan for SQL statements at runtime.

Use this statement with ESQL/C.

The PREPARE statement permits your program to assemble the text of an SQL statement at runtime and make it executable. This dynamic form of SQL is accomplished in three steps:

1. A PREPARE statement accepts statement text as input, either as a quoted string or stored within a character variable. Statement text can contain question-mark (?) placeholders to represent values that are to be defined when the statement is executed.
2. An EXECUTE or OPEN statement can supply the required input values and execute the prepared statement once or many times.
3. Resources allocated to the prepared statement can be released later using the FREE statement.

The number of prepared items in a single program is limited by the available memory. These items include both statement identifiers that are named in PREPARE statements (statement_id or statement_id_var) and declared cursors. To avoid exceeding the limit, use a FREE statement to release some statements or cursors.

For information on statements that you cannot use in a prepared statement, see Restricted Statements in Single-Statement Prepares and Restricted Statements in Multi-Statement Prepares.

The maximum length of a PREPARE statement is 64 kilobytes.

A PREPARE statement sends the statement text to the database server. The database server analyzes the statement text. If the text contains no syntax errors, the database server translates it to an internal form. This translated statement is saved for later execution in a data structure that the PREPARE statement allocates. The name of the structure is the value that is assigned to the statement identifier in the PREPARE statement. Subsequent SQL statements refer to the structure by using the same statement identifier that was used in the PREPARE statement.

A subsequent FREE statement releases the resources that were allocated to the statement. After you release the database-server resources, you cannot use the statement identifier with a DECLARE cursor or with the EXECUTE statement until you prepare the statement again.

A program can consist of one or more source-code files. By default, the scope of a statement identifier is global to the program. Therefore, a statement identifier that is prepared in one file can be referenced from another file.

In a multiple-file program, if you want to limit the scope of a statement identifier to the file in which it is prepared, preprocess all the files with the -local command-line option.

A statement identifier can represent only one SQL statement or sequence of statements at a time. You can execute a new PREPARE statement with an existing statement identifier if you wish to bind a given statement 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 character-string variable.

The first example shows a statement identifier that was prepared as a host variable. The second example shows a statement identifier that was prepared as a character-string constant:

stcopy ("query2", stmtid);

EXEC SQL prepare :stmtid from

	'select * from customer';



EXEC SQL prepare query2 from 

	'select * from customer';

A statement-identifier variable must be a character data type. In C, it must be defined 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 that are preceded by a double dash (--) or enclosed in braces ({ }).

These comment symbols represent 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 sequence of statements that are separated by semicolons.

For more information on how to prepare a single SQL statement, see Restricted Statements in Single-Statement Prepares. For more information on how to prepare a sequence of SQL statements, see Preparing Sequences of Multiple SQL Statements.

• Names of host-language variables are not recognized as such in prepared text.

Therefore, you cannot prepare a SELECT statement that contains an INTO clause or an EXECUTE FUNCTIOn (or EXECUTE PROCEDURE) that contains 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.

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

• The text cannot include an embedded SQL statement prefix or terminator, such as a dollar sign ($) or the words EXEC SQL.

The following ESQL/C example shows a PREPARE statement that takes statement text as a quoted string:

EXEC SQL prepare  new_cust  from

	'insert  into  customer(fname,lname) values(?,?)';

If the prepared statement contains the Collection Derived Table segment on 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 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.

The way to execute a prepared user-defined function depends on whether the function returns only one group of values 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 database manipulation statement.

You can prepare any single SQL statement except the following statements.

You can prepare any single SQL statement except the following statements

You can prepare a SELECT statement. If the SELECT statement 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 following 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. You cannot use a question-mark (?) placeholder to represent 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 (for statements that are associated with a cursor) and EXECUTE (all other prepared statements) statements.

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

In general, you cannot use question-mark (?) placeholders for SQL identifiers. You must specify these identifiers in the statement text when you prepare the statement.

However, in a few special cases, you can use the question-mark (?) placeholder for an SQL identifier. These cases are as follows:

• You can use the question-mark (?) placeholder for the database name in the DATABASE statement.
• You can use the question-mark (?) placeholder for the dbspace name in the IN dbspace clause of the CREATE DATABASE statement.
• You can use the question-mark (?) placeholder for the cursor name in statements that use cursor names.

If a prepared statement requires identifiers, but the identifiers are unknown when you write the prepared statement, you can construct a statement that receives SQL identifiers from user input.

The following ESQL/C example prompts the user for the name of a table and uses that name in a SELECT statement. Because the table name is unknown until runtime, the number and data types of the table columns are also unknown. Therefore, the program cannot allocate host variables to receive data from each row in advance. Instead, this program fragment describes the statement into an sqlda descriptor and fetches each row with the descriptor. The fetch puts each row into memory locations that the program provides dynamically.

If a program retrieves all the rows in the active set, the FETCH statement would be placed in a loop that fetched each row. If the FETCH statement retrieves more than one data value (column), another loop exists after the FETCH, which performs some action on each data value.

#include <stdio.h>
EXEC SQL include sqlda;
EXEC SQL include sqltypes;

char *malloc( );

main()
{
struct sqlda *demodesc;
char tablename[19];
int i;
EXEC SQL BEGIN DECLARE SECTION;
char demoselect[200];
EXEC SQL END DECLARE SECTION;

/* This program selects all the columns of a given tablename.
The tablename is supplied interactively. */

EXEC SQL connect to 'stores_demo';

printf( "This program does a select * on a table\n" );
printf( "Enter table name: " );
scanf( "%s", tablename );

sprintf(demoselect, "select * from %s", tablename );

EXEC SQL prepare iid from :demoselect;
EXEC SQL describe iid into demodesc;

/* Print what describe returns */

for ( i = 0; i < demodesc->sqld; i++ )
prsqlda (demodesc->sqlvar + i);

/* Assign the data pointers. */

for ( i = 0; i < demodesc->sqld; i++ )
{
switch (demodesc->sqlvar[i].sqltype & SQLTYPE)
{
case SQLCHAR:
demodesc->sqlvar[i].sqltype = CCHARTYPE;
/* make room for null terminator */
demodesc->sqlvar[i].sqllen++;
demodesc->sqlvar[i].sqldata =
malloc( demodesc->sqlvar[i].sqllen );
break;

case SQLSMINT: /* fall through */
case SQLINT: /* fall through */
case SQLSERIAL:
demodesc->sqlvar[i].sqltype = CINTTYPE;
demodesc->sqlvar[i].sqldata =
malloc( sizeof( int ) );
break;

/* And so on for each type. */

}
}

/* Declare and open cursor for select . */
EXEC SQL declare d_curs cursor for iid;
EXEC SQL open d_curs;

/* Fetch selected rows one at a time into demodesc. */

for( ; ; )
{
printf( "\n" );
EXEC SQL fetch d_curs using descriptor demodesc;
if ( sqlca.sqlcode != 0 )
break;
for ( i = 0; i < demodesc->sqld; i++ )
{
switch (demodesc->sqlvar[i].sqltype)
{
case CCHARTYPE:
printf( "%s: \"%s\n", demodesc->sqlvar[i].sqlname,
demodesc->sqlvar[i].sqldata );
break;
case CINTTYPE:
printf( "%s: %d\n", demodesc->sqlvar[i].sqlname,
*((int *) demodesc->sqlvar[i].sqldata) );
break;

/* And so forth for each type... */

}
}
}
EXEC SQL close d_curs;
EXEC SQL free d_curs;

/* Free the data memory. */

for ( i = 0; i < demodesc->sqld; i++ )
free( demodesc->sqlvar[i].sqldata );
free( demodesc );

printf ("Program Over.\n");
}


prsqlda(sp)
struct sqlvar_struct *sp;

{
printf ("type = %d\n", sp->sqltype);
printf ("len = %d\n", sp->sqllen);
printf ("data = %lx\n", sp->sqldata);
printf ("ind = %lx\n", sp->sqlind);
printf ("name = %s\n", sp->sqlname);
}

You can execute several SQL statements as one action if you include them in the same PREPARE statement. Multistatement text is processed as a unit; actions are not treated sequentially. Therefore, multistatement text cannot include statements that depend on actions that occur in a previous statement in the text. For example, you cannot create a table and insert values into that table in the same prepared block.

If a statement in a multistatement prepare returns an error, the whole prepared statement stops executing. The database server does not execute any remaining statements.

In most situations, compiled products return error-status information on the error, but do not indicate which statement in the sequence causes an error. You can use the sqlca.sqlerrd[4] field in the sqlca to find the offset of the errors.

In a multistatement prepare, if no rows are returned from a WHERE clause in the following statements, the database server returns the error,
SQLNOTFOUND (100):

• UPDATEWHERE
• SELECT INTO TEMPWHERE
• INSERT INTOWHERE
• DELETE FROMWHERE

In the following example, four SQL statements are prepared into a single ESQL/C string that is called query. Individual statements are delimited with semicolons. A single PREPARE statement can prepare the four statements for execution, and a single EXECUTE statement can execute the statements that are associated with the qid statement identifier.

sprintf  (query,    "%s  %s  %s  %s  %s  %s  %s",

	"update  account  set  balance  =  balance  +  ?  ",

		"where  acct_number  =  ?;",

	"update  teller  set  balance  =  balance  +  ?  ",

		"where  teller_number  =  ?;",

	"update  branch  set  balance  =  balance  +  ?  ",

		"where  branch_number  =  ?;",

	"insert  into  history  values  (?,  ?);";

EXEC SQL prepare  qid  from  :query;



EXEC SQL begin work;

EXEC SQL execute  qid  using

		:delta,  :acct_number,  :delta,  :teller_number,

		:delta,  :branch_number,  :timestamp, :values;

EXEC SQL commit work;

In the preceding code fragment, the semicolons (;) are required as SQL statement-terminator symbols between each SQL statement in the text that query holds.

In addition to the statements listed as exceptions in Restricted Statements in Single-Statement Prepares, you cannot use the following statements in text that contains multiple statements that are separated by semicolons.

In addition, the following types of statements are not allowed in a multistatement prepare:

• Statements that can cause the current database to close during the execution of the multistatement sequence
• Statements that include references to TEXT or BYTE host variables

In general, you cannot use the SELECT statement in a multistatement prepare. The only form of the SELECT statement allowed in a multistatement prepare is a SELECT statement with an INTO temporary table clause.

To increase performance efficiency, you can use the PREPARE statement and an EXECUTE statement in a loop to eliminate overhead that redundant parsing and optimizing cause. For example, an UPDATE statement that is located within a WHILE loop is parsed each time the loop runs. If you prepare the UPDATE statement outside the loop, the statement is parsed only once, eliminating overhead and speeding statement execution. The following example shows how to prepare an ESQL/C statement to improve performance:

EXEC SQL BEGIN DECLARE SECTION;

	char disc_up[80];

	int cust_num;

EXEC SQL END DECLARE SECTION;



main()

{

	sprintf(disc_up, "%s %s",

		"update  customer  ",

		"set  discount  =  0.1  where  customer_num  =  ?");

	EXEC SQL prepare up1 from :disc_up;



	while  (1)

		{

		printf("Enter  customer  number (or 0 to quit): ");

		scanf("%d", cust_num);

		if  (cust_num  ==  0)

			break;

		EXEC SQL execute  up1  using  :cust_num;

		}

}

Related statements: CLOSE, DECLARE, DESCRIBE, EXECUTE, FREE, OPEN, SET AUTOFREE, and SET DEFERRED_PREPARE

For information about basic concepts relating to the PREPARE statement, see the Informix Guide to SQL: Tutorial.

For information about more advanced concepts relating to the PREPARE statement, see the Informix ESQL/C Programmer's Manual.