SQL Statements
PREPARE
Use the PREPARE statement to parse, validate, and generate an execution plan for SQL statements in an INFORMIX-ESQL/C program at runtime.
Syntax
Usage
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 objects in a single program is limited by the available memory. This limit includes both statement identifiers that are named in PREPARE statements (statement id or statement id variable) and cursor declarations that incorporate SELECT, EXECUTE FUNCTION, or INSERT statements. To avoid exceeding the limit, use a FREE statement to release some statements or cursors.
Using a Statement Identifier
A PREPARE statement sends the statement text to the database server where it is analyzed. 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. See the manual for your SQL API for more information, restrictions, and performance issues when preprocessing with the -local option.
Releasing a Statement Identifier
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. In the following ESQL/C examples, the first example shows a statement identifier that was prepared as an SQL API host variable; the second example shows a statement identifier that was prepared as a character-string constant:
A statement ID variable must be the character data type. In C, it must be defined as char.
Statement Text
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:
Therefore, you cannot prepare a SELECT statement that contains an INTO clause or an EXECUTE FUNCTION that contains an INTO clause because the INTO clause requires a host-language variable.
The following example shows a PREPARE statement in INFORMIX-ESQL/C that takes statement text as a quoted string:
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.
Preparing and Executing User-Defined Routines
The way to prepare a user-defined routine (SPL routine or external routine) depends on whether the routine is a procedure or a function:
To execute the prepared procedure, use the EXECUTE statement.
You cannot include the INTO clause of EXECUTE FUNCTION 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 functions that return only one group of values. To execute functions that return more than one group of return values, you must associate the EXECUTE FUNCTION statement with a cursor.
For information on how to create and execute SPL routines, see Chapter 14 of the Informix Guide to SQL: Tutorial. For more information on how to execute user-defined routines dynamically, see the INFORMIX-ESQL/C Programmer's Manual.
SQL Statements Permitted in Single-Statement Prepares
You can prepare any single SQL statement except the ones in the following list.
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 or FOR READ ONLY clause. These clauses are normally used with the DECLARE statement to create an update cursor or read-only cursor, respectively. The following example shows a SELECT statement with a FOR UPDATE clause in INFORMIX-ESQL/C:
Preparing Statements When Parameters Are Known
In some prepared statements, all needed information is known at the time the statement is prepared. The following example in INFORMIX-ESQL/C shows two statements that are prepared from constant data:
Preparing Statements That Receive Parameters
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 INFORMIX-ESQL/C examples show some uses of question-mark (?) placeholders:
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 INFORMIX-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.
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.
Preparing Statements with SQL Identifiers
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:
Obtaining SQL Identifiers from User Input
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 INFORMIX-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 using 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.
For an explanation of how to use an sqlda structure for statement values, see the INFORMIX-ESQL/C Programmer's Manual.
Preparing Sequences of Multiple SQL Statements
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.
In most situations, compiled products return error-status information on the first error in the multistatement text. No indication exists of which statement in the sequence causes an error. You can use sqlca to find the offset of the ESQL/C error in sqlca.sqlerrd[4]. For more information about sqlca and error-status information, see the INFORMIX-ESQL/C Programmer's Manual.
In a multistatement prepare, if no rows are returned from a WHERE clause in the following statements, you get SQLNOTFOUND (100) in both ANSI-compliant databases and databases that are not ANSI compliant:
In the following example, four SQL statements are prepared into a single INFORMIX-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.
Restrictions for Multistatement Prepares
In addition to the statements listed in "SQL Statements Permitted in Single-Statement Prepares", you cannot use the following statements in text that contains multiple statements that are separated by semicolons.
You cannot use regular SELECT statements in multistatement prepares. The only form of the SELECT statement allowed in a multistatement prepare is a SELECT statement with an INTO TEMP clause.
In addition, the statements that could cause the current database to be closed in the middle of executing the sequence of statements are not allowed in a multistatement prepare.
Using Prepared Statements for Efficiency
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 INFORMIX-ESQL/C statement to improve performance:
References
See the CLOSE, DECLARE, DESCRIBE, EXECUTE, FREE, and OPEN statements in this manual.
In the Informix Guide to SQL: Tutorial, see the discussion of the PREPARE statement and dynamic SQL in Chapter 5.
PUT
Use the PUT statement to store a row in an insert buffer for later insertion into the database.
Syntax
Usage
The PUT statement is one of four statements that are used for inserts that send more than one row to the database. The four statements, DECLARE, OPEN, PUT, and CLOSE, are used in the following sequence:
1. Declare an cursor to control the rows to insert.
2. Open the cursor to create the insert buffer.
3. Put the contents of each row into the cursor.
4. Close the cursor to send the rows to the database server and to break the association between the cursor and the active set.
Each PUT statement stores a row in an insert buffer that was created when cursor name was opened. If the buffer has no room for the new row when the statement executes, the buffered rows are written to the database in a block and the buffer is emptied. As a result, some PUT statement executions cause rows to be written to the database, and some do not.
You can use the FLUSH statement to write buffered rows to the database without adding a new row. The CLOSE statement writes any remaining rows before it closes an insert cursor.
If the current database uses explicit transactions, you must execute a PUT statement within a transaction.
The following example uses a PUT statement in INFORMIX-ESQL/C:
PUT is not an X/Open SQL statement. Therefore, you get a warning message if you compile a PUT statement in X/Open mode in INFORMIX-ESQL/C. For details on compiling in X/Open mode, see the INFORMIX-ESQL/C Programmer's Manual. 
Supplying Inserted Values
The values that reside in the inserted row can come from one of the following sources:
Using Constant Values in INSERT
The VALUES clause of the INSERT statement lists the values of the inserted columns. One or more of these values might be constants (that is, numbers or character strings).
When all the inserted values are constants, the PUT statement has a special effect. Instead of creating a row and putting it in the buffer, the PUT statement merely increments a counter. When you use a FLUSH or CLOSE statement to empty the buffer, one row and a repetition count are sent to the database server, which inserts that number of rows.
In the following INFORMIX-ESQL/C example, 99 empty customer rows are inserted into the customer table. Because all values are constants, no disk output occurs until the cursor closes. (The constant zero for customer_num causes generation of a SERIAL value.)
Naming Program Variables in INSERT
When you associate the INSERT statement with the cursor declaration (in the DECLARE statement), you create an insert cursor. In the INSERT statement, you can name program variables in the VALUES clause. When each PUT statement is executed, the contents of the program variables at that time are used to populate the row that is inserted in the buffer.
If you are creating an insert cursor (using DECLARE with INSERT), you must use only program variables in the VALUES clause. Variable names are not recognized in the context of a prepared statement; you associate a prepared statement with a cursor through its statement identifier.
The following INFORMIX-ESQL/C example illustrates the use of an insert cursor. The code includes the following statements:
Use an indicator variable if the data to be inserted by the INSERT statement might be null. See the INFORMIX-ESQL/C Programmer's Manual for more information about indicator variables.
Naming Program Variables in FROM Clause of PUT
When the INSERT statement is prepared (see the PREPARE statement on page 1-541), you cannot use program variables in its VALUES clause. However, you can represent values using a question-mark (?) placeholder. List the names of program variables in the FROM clause of the PUT statement to supply the missing values.
The following INFORMIX-ESQL/C example lists host variables in a PUT statement:
Using a System-Descriptor Area
If you do not know the number of parameters to be supplied at runtime or their data types, you can associate insert values from a system-descriptor area. A system-descriptor area describes the data type and memory location of one or more values.
You can also use an sqlda structure to supply parameters dynamically. However, a system-descriptor area conforms to the X/Open standards.
To specify a system-descriptor area as the location of parameters, use the USING SQL DESCRIPTOR clause of the PUT statement. Use the SET DESCRIPTOR statement to transfer the insert values for the PUT statement in the system-descriptor area. The USING SQL DESCRIPTOR clause allows you to obtain insert values from a system-descriptor area to put in an insert cursor.
The following INFORMIX-ESQL/C example shows how to associate values from a system-descriptor area:
The COUNT field in the system-descriptor area corresponds to the number of dynamic parameters in the prepared statement. The value of COUNT must be less than or equal to the value of the occurrences that were specified when the system-descriptor area was allocated with the ALLOCATE DESCRIPTOR statement. You can obtain the value of a field with the GET DESCRIPTOR statement and set the value with the SET DESCRIPTOR statement.
For more information on how to use a system-descriptor area, see the INFORMIX-ESQL/C Programmer's Manual.
Using an sqlda Structure
If you do not know the number of parameters to be supplied at runtime or their data types, you can associate insert values from an sqlda structure. An sqlda structure lists the data type and memory location of one or more values to replace question-mark (?) placeholders. To specify an sqlda structure as the location of parameters, use the USING DESCRIPTOR clause of the PUT statement. This clause allows you to obtain insert values from an sqlda structure to put into an insert cursor.
Each time the PUT statement executes, the values that the sqlda structure describes are used to replace question-mark (?) placeholders in the INSERT statement. This process is similar to using a FROM clause with a list of variables, except that your program has full control over the memory location of the data values.
The following example shows the PUT ... USING DESCRIPTOR statement:
For further information, refer to the sqlda discussion in the INFORMIX-ESQL/C Programmer's Manual.
Inserting into a Collection Cursor
A collection cursor allows you to access the individual elements of an ESQL/C collection variable. To declare a collection cursor, use the DECLARE statement and include the Collection Derived Table segment in the INSERT statement that you associate with the cursor. Once you open the collection cursor with the OPEN statement, the cursor allows you to put elements in the collection variable.
For more information on the Collection Derived Table segment, see page 1-831. For more information how to declare a collection cursor for an INSERT statement, see "An Insert Cursor For a Collection Variable".
To put elements, one at a time, into the insert cursor, use the PUT statement and the FROM clause. The PUT statement identifies the collection cursor that is associated with the collection variable. The FROM clause identifies the element value to be inserted into the cursor. The data type of any host variable in the FROM clause must match the element type of the collection.
Suppose you have a table called children with the following structure:
The following ESQL/C code fragment shows how to use an insert cursor to put elements into a collection variable called child_colors:
After the FLUSH statement executes, the collection variable, child_colors, contains the elements {"blue", "purple", "green", "white", "gold"}. The UPDATE statement at the end of this code fragment saves the new collection into the fav_colors column of the database. Without this UPDATE statement, the collection column never has the new collection added.
Writing Buffered Rows
When the OPEN statement opens an insert cursor, an insert buffer is created. The PUT statement puts a row into this insert buffer. The block of buffered rows is inserted into the database table as a block only when necessary; this process is called flushing the buffer. The buffer is flushed after any of the following events:
When the OPEN statement is applied to an open cursor, it closes the cursor before reopening it; this implied CLOSE statement flushes the buffer.
If the program terminates without closing an insert cursor, the buffer remains unflushed. Rows that were inserted into the buffer since the last flush are lost. Do not rely on the end of the program to close the cursor and flush the buffer.
Checking the Result of PUT
The sqlca structure contains information on the success of each PUT statement as well as information that lets you count the rows that were inserted. The result of each PUT statement is contained in the fields of the sqlca, as the following table shows.
Data buffering with an insert cursor means that errors are not discovered until the buffer is flushed. For example, an input value that is incompatible with the data type of the column for which it is intended is discovered only when the buffer is flushed. When an error is discovered, rows in the buffer that are located after the error are not inserted; they are lost from memory.
The SQLCODE variable is set to 0 if no error occurs; otherwise, it is set to an error code. The third element of the sqlerrd array is set to the number of rows that are successfully inserted into the database:
Counting Total and Pending Rows
To count the number of rows that were actually inserted in the database and the number not yet inserted, follow these steps:
At any time, (total - pending) represents the number of rows that were actually inserted. If all commands are successful, pending contains zero after the cursor is closed. If an error occurs during a PUT, FLUSH, or CLOSE statement, the value that remains in pending is the number of uninserted (discarded) rows.
References
See the ALLOCATE DESCRIPTOR, CLOSE, DEALLOCATE DESCRIPTOR, DECLARE, DESCRIBE, GET DESCRIPTOR, OPEN, PREPARE, and SET DESCRIPTOR statements in this manual for further information about using the PUT statement with dynamic management statements.
In the Informix Guide to SQL: Tutorial, see the discussion of the PUT statement in Chapter 6.
For further information about error checking, the system-descriptor area, and the sqlda structure, see the INFORMIX-ESQL/C Programmer's Manual.
|
