SQLPrepare function (CLI) - Prepare a statement

Purpose

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.

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().

Syntax

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

Function arguments

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.

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 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 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

Authorization

None.

Example

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

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