SQLPrepare function (CLI) - Prepare a statement

SQLPrepare() associates an SQL statement or XQuery expression with the input statement handle provided. The application can include one or more parameter markers in the SQL statement. To include a parameter marker, the application embeds a question mark (?) or a colon followed by a name (:name) into the SQL string at the appropriate position. The application can reference this prepared statement by passing the statement handle to other functions.

Specification:

  • CLI 1.1
  • ODBC 1.0
  • ISO CLI
Note: For XQuery expressions, you cannot specify parameter markers in the expression itself. You can, however, use the XMLQUERY function to bind parameter markers to XQuery variables. The values of the bound parameter markers will then be passed to the XQuery expression specified in XMLQUERY for execution.

If the statement handle has been previously used with a query statement (or any function that returns a result set), either SQLCloseCursor() or SQLFreeStmt() with the SQL_CLOSE option must be called to close the cursor before calling SQLPrepare().

XQuery expressions must be prefixed with the "XQUERY" keyword. To prepare and execute XQuery expressions without having to include this keyword, set the statement attribute SQL_ATTR_XQUERY_STATEMENT to SQL_TRUE before calling SQLPrepare() or SQLExecDirect().

Unicode equivalent: This function can also be used with the Unicode character set. The corresponding Unicode function is SQLPrepareW(). See Unicode functions (CLI) for information about ANSI to Unicode function mappings.

Syntax

SQLRETURN   SQLPrepare (
               SQLHSTMT          StatementHandle,  /* hstmt */
               SQLCHAR           *StatementText,   /* szSqlStr */
               SQLINTEGER        TextLength);      /* cbSqlStr */

Function arguments

Table 1. 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 Number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) needed to store the StatementText argument, or SQL_NTS if StatementText is null-terminated.

Usage

Deferred prepare is on by default. The prepare request is not sent to the server until either SQLDescribeParam(), SQLExecute(), SQLNumResultCols(), SQLDescribeCol(), or SQLColAttribute() is called using the same statement handle as the prepared statement. This minimizes network flow and improves performance.

If the SQL statement text contains vendor escape clause sequences, 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 then the SQL_ATTR_NOSCAN statement attribute should be set to SQL_NOSCAN at the connection level so that 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:
  • SQLNumResultCols()
  • SQLDescribeCol()
  • SQLColAttribute()
Information about the parameter markers in StatementText can be requested using the following functions:
  • SQLDescribeParam()
  • SQLNumParams()
Note: The first invocation of SQLNumResultCols(), SQLDescribeCol(), SQLColAttribute(), or SQLDescribeParam() will force the PREPARE request to be sent to the server if deferred prepare is enabled.

The SQL statement string might 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 or a colon followed by a name (:name), 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 or associate application variables with each parameter marker and to indicate if any data conversion should be performed at the time the data is transferred. An application can call SQLDescribeParam() to retrieve information about the data expected by the database server for the parameter marker.

All parameters must be bound before calling SQLExecute().

Refer to the PREPARE statement for information about 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 can be COMMIT or ROLLBACK and executing either of these statements has the same effect as calling SQLEndTran() on the current connection handle.

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

  • SQL_SUCCESS
  • SQL_SUCCESS_WITH_INFO
  • SQL_STILL_EXECUTING
  • SQL_ERROR
  • SQL_INVALID_HANDLE

Diagnostics

Table 2. 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 issues:
  • an SQL statement that the connected database server could not prepare
  • a statement containing a syntax error
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. It is likely that process-level memory has been exhausted for the application process. Consult the operating system configuration for information about process-level memory limitations.
HY008 Operation was Canceled. Asynchronous processing was enabled for StatementHandle. The function was called and before it completed execution, SQLCancel() was called on StatementHandle from a different thread in a multithreaded application. Then the function was called again on StatementHandle.
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 resource limitations.
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. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetStmtAttr().
Note: This SQLSTATE applies only to .Net applications.
Note:
a
xxx refers to any SQLSTATE with that class code. Example, 37xxx refers to any SQLSTATE in the 37 class.
Note: Not all DBMSs report all of the listed diagnostic messages at prepare time. If deferred prepare is left on as the default behavior (controlled by the SQL_ATTR_DEFERRED_PREPARE statement attribute), then these errors could occur when the PREPARE is flowed to the server. The application must be able to handle these conditions when calling functions that cause this flow. These functions include SQLExecute(), SQLDescribeParam(), SQLNumResultCols(), SQLDescribeCol(), and SQLColAttribute().

Authorization

None.

Example

  SQLCHAR *stmt = (SQLCHAR *)"DELETE FROM org WHERE deptnumb = ? ";

  /* ... */
  
  /* prepare the statement */
  cliRC = SQLPrepare(hstmt, stmt, SQL_NTS);