| Specification: | DB2 CLI 1.1 | ODBC 1.0 | ISO CLI |
SQLPrepare() associates an SQL statement with the input statement handle and sends the statement to the DBMS to be prepared. The application can reference this prepared statement by passing the statement handle to other functions.
If the statement handle has been previously used with a query statement (or any function that returns a result set), SQLFreeStmt() must be called to close the cursor, before calling SQLPrepare().
Syntax
SQLRETURN SQLPrepare (SQLHSTMT StatementHandle,
SQLCHAR FAR *StatementText,
SQLINTEGER TextLength);
Function Arguments
Table 136. SQLPrepare Arguments
| Data Type | Argument | Use | Description |
|---|---|---|---|
| SQLHSTMT | StatementHandle | input | Statement handle. There must not be an open cursor associated with StatementHandle. |
| SQLCHAR * | StatementText | input | SQL statement string |
| SQLINTEGER | TextLength | input | Length of contents of StatementText argument.
This must be set to either the exact length of the SQL statement in szSqlstr, or to SQL_NTS if the statement text is null-terminated. |
Usage
Starting in DB2 CLI Version 5, deferred prepare is on by default. The PREPARE request is not sent to the server until the corresponding execute request is issued. This minimizes network flow and improves performance. See Deferred Prepare now on by Default for complete details.
If the SQL statement text contains vendor escape clause sequences, DB2 CLI will first modify the SQL statement text to the appropriate DB2 specific format before submitting it to the database for preparation. If the application does not generate SQL statements that contain vendor escape clause sequences (see Using Vendor Escape Clauses); then the SQL_ATTR_NOSCAN statement attribute should be set to SQL_NOSCAN at the connection level so that DB2 CLI does not perform a scan for any vendor escape clauses.
Once a statement has been prepared using SQLPrepare(), the application can request information about the format of the result set (if the statement was a query) by calling:
The SQL statement string may contain parameter markers and SQLNumParams() can be called to determine the number of parameter markers in the statement. A parameter marker is represented by a "?" character, and is used to indicate a position in the statement where an application supplied value is to be substituted when SQLExecute() is called. The bind parameter functions, SQLBindParameter(), SQLSetParam() and SQLBindFileToParam() are used to bind (associate) application values with each parameter marker and to indicate if any data conversion should be performed at the time the data is transferred.
All parameters must be bound before calling SQLExecute(), for more information refer to SQLExecute - Execute a Statement.
Refer to the PREPARE section of the SQL Reference for information on rules related to parameter markers.
Once the application has processed the results from the SQLExecute() call, it can execute the statement again with new (or the same) parameter values.
The SQL statement cannot be a COMMIT or ROLLBACK. SQLTransact() must be called to issue COMMIT or ROLLBACK. For more information about supported SQL statements in DB2 Universal Database, refer to Table 215.
If the SQL statement is a Positioned DELETE or a Positioned UPDATE, the cursor referenced by the statement must be defined on a separate statement handle under the same connection handle and same isolation level.
Return Codes
Diagnostics
Table 137. SQLPrepare SQLSTATEs
| SQLSTATE | Description | Explanation | ||
|---|---|---|---|---|
| 01504 | The UPDATE or DELETE statement does not include a WHERE clause. | StatementText contained an UPDATE or DELETE statement which did not contain a WHERE clause. | ||
| 01508 | Statement disqualified for blocking. | The statement was disqualified for blocking for reasons other than storage. | ||
| 21S01 | Insert value list does not match column list. | StatementText contained an INSERT statement and the number of values to be inserted did not match the degree of the derived table. | ||
| 21S02 | Degrees of derived table does not match column list. | StatementText contained a CREATE VIEW statement and the number of names specified is not the same degree as the derived table defined by the query specification. | ||
| 22018 | Invalid character value for cast specification. | *StatementText contained an SQL statement that contained a literal or parameter and the value was incompatible with the data type of the associated table column. | ||
| 22019 | Invalid escape character | The argument StatementText contained a LIKE predicate with an ESCAPE in the WHERE clause, and the length of the escape character following ESCAPE was not equal to 1. | ||
| 22025 | Invalid escape sequence | The argument StatementText contained "LIKE pattern value ESCAPE escape character" in the WHERE clause, and the character following the escape character in the pattern value was not one of "%" or "_". | ||
| 24000 | Invalid cursor state. | A cursor was already opened on the statement handle. | ||
| 34000 | Invalid cursor name. | StatementText contained a Positioned DELETE or a Positioned UPDATE and the cursor referenced by the statement being executed was not open. | ||
| 37xxx a | Invalid SQL syntax. | StatementText contained one or more of the following:
| ||
| 40001 | Transaction rollback. | The transaction to which this SQL statement belonged was rolled back due to deadlock or timeout. | ||
| 40003 08S01 | Communication link failure. | The communication link between the application and data source failed before the function completed. | ||
| 42xxx a | Syntax Error or Access Rule Violation. | 425xx indicates the authorization ID does not have permission
to execute the SQL statement contained in StatementText.
Other 42xxx SQLSTATES indicate a variety of syntax or access problems with the statement. | ||
| 58004 | Unexpected system failure. | Unrecoverable system error. | ||
| S0001 | Database object already exists. | StatementText contained a CREATE TABLE or CREATE VIEW statement and the table name or view name specified already existed. | ||
| S0002 | Database object does not exist. | StatementText contained an SQL statement that references a table name or a view name which did not exist. | ||
| S0011 | Index already exists. | StatementText contained a CREATE INDEX statement and the specified index name already existed. | ||
| S0012 | Index not found. | StatementText contained a DROP INDEX statement and the specified index name did not exist. | ||
| S0021 | Column already exists. | StatementText contained an ALTER TABLE statement and the column specified in the ADD clause was not unique or identified an existing column in the base table. | ||
| S0022 | Column not found. | StatementText contained an SQL statement that references a column name which did not exist. | ||
| HY001 | Memory allocation failure. | DB2 CLI is unable to allocate memory required to support execution or completion of the function. | ||
| HY008 | Operation canceled. |
Asynchronous processing was enabled for the StatementHandle. The function was called and before it completed execution, SQLCancel() was called on the StatementHandle. Then the function was called again on the StatementHandle. The function was called and, before it completed execution,
SQLCancel() was called on the StatementHandle from a
different thread in a multithread application.
| ||
| HY009 | Invalid argument value. | StatementText was a null pointer. | ||
| HY010 | Function sequence error. | The function was called while in a data-at-execute (SQLParamData(), SQLPutData()) operation.
The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation. | ||
| HY013 | Unexpected memory handling error. | DB2 CLI was unable to access memory required to support execution or completion of the function. | ||
| HY014 | No more handles. | DB2 CLI was unable to allocate a handle due to internal resources. | ||
| HY090 | Invalid string or buffer length. | The argument TextLength was less than 1, but not equal to SQL_NTS. | ||
| HYT00 | Timeout expired. | The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetConnectAttr(). | ||
| ||||
| Note: | Not all DBMSs report all of the above diagnostic messages at prepare time. Therefore an application must also be able to handle these conditions when calling SQLExecute(). |
Authorization
None.
(The complete sample tbread.c is also available here .)
/* From the CLI sample TBREAD.C */
/* ... */
/* prepare the statement */
printf("\n Prepare the statement\n");
printf(" %s\n", stmt);
sqlrc = SQLPrepare( hstmt, stmt, SQL_NTS ) ;
STMT_HANDLE_CHECK( hstmt, sqlrc);
References