Call Level Interface Guide and Reference

SQLExtendedPrepare - Prepare a Statement and Set Statement Attributes

Purpose


Specification: DB2 CLI 6.0    

SQLExtendedPrepare() is used to prepare a statement and set a group of statement attributes, all in one call.

This function can be used in place of a call to SQLPrepare() followed by a number of calls to SQLSetStmtAttr().

Syntax

SQLRETURN SQLExtendedPrepare( SQLHSTMT      StatementHandle,
                    SQLCHAR *     StatementText,
                    SQLINTEGER    TextLength,
                    SQLINTEGER    cPars,
                    SQLSMALLINT   sStmtType,
                    SQLINTEGER    cStmtAttrs,
                    SQLINTEGER *  piStmtAttr,
                    SQLINTEGER *  pvParams );

Function Arguments


Table 65. SQLExtendedPrepare() Arguments
Data Type Argument Use Description
SQLHSTMT StatementHandle Input Statement handle.
SQLCHAR StatementText Input SQL statement string.
SQLINTEGER TextLength Input Length of the contents of the StatementText argument.

This must be set to either the exact length of the SQL statement in StatementText, or to SQL_NTS if the statement text is null-terminated.

SQLINTEGER cPars Input Number of parameter markers in statement.
SQLSMALLINT sStmtType Input Statement type. For possible values see List of cStmtType Values.
SQLINTEGER cStmtAttrs Input Number of statement attributes specified on this call.
SQLINTEGER piStmtAttr Input Array of statement attributes to set.
SQLINTEGER pvParams Input Array of corresponding statement attributes values to set.

Usage

The first three arguments of this function are exactly the same as the arguments in SQLPrepare().

There are two requirements when using SQLExtendedBind():

  1. The SQL statements will not be scanned for ODBC/vendor escape clauses. It behaves as if the SQL_ATTR_NOSCAN statment attribute is set to SQL_NOSCAN. If the SQL statement contains ODBC/vendor escape clauses then SQLExtendedBind() cannot be used.
  2. You must indicate in advance (through cRecords) the number of parameter markers that are included in the SQL statement.

For more information on StatementHandle, StatementText, and TextLength see SQLPrepare - Prepare a Statement.

The cPars argument indicates the number of parameter markers in StatementText.

The argument cStmtType is used to indicate the type of statement that is being prepare. See List of cStmtType Values for the list of possible values.

The final three arguments are used to indicate the set of statement attributes to use. Set cStmtAttrs to the number of statement attributes specified on this call. Create two arrays, one to hold the list of statement attributes, one to hold the value for each. Use these arrays for piStmtAttr and pvParams. For details on the possible statement attributes, see SQLSetStmtAttr - Set Options Related to a Statement.

List of cStmtType Values

The argument cStmtType can be set to one of the following values:

Return Codes

Diagnostics

Table 66. SQLExtendedPrepare SQLSTATEs
SQLSTATE Description Explanation
01000 Warning. Informational message. (Function returns SQL_SUCCESS_WITH_INFO.)
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.
01S02 Option value changed. DB2 CLI did not support a value specified in *pvParams, or a value specified in *pvParams was invalid because of SQL constraints or requirements, so DB2 CLI substituted a similar value. (Function returns SQL_SUCCESS_WITH_INFO.)
08S01 Communication link failure. The communication link between DB2 CLI and the data source to which it was connected failed before the function completed processing.
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:
  • a COMMIT
  • a ROLLBACK
  • 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.
HY000 General error. An error occurred for which there was no specific SQLSTATE. The error message returned by SQLGetDiagRec() in the *MessageText buffer describes the error and its cause.
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.

HY011 Operation invalid at this time. The Attribute was SQL_ATTR_CONCURRENCY, SQL_ ATTR_CURSOR_TYPE, SQL_ ATTR_SIMULATE_CURSOR, or SQL_ATTR_USE_BOOKMARKS and the statement was prepared.
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.
HY017 Invalid use of an automatically allocated descriptor handle. The Attribute argument was SQL_ATTR_IMP_ROW_DESC or SQL_ATTR_IMP_PARAM_DESC. The Attribute argument was SQL_ATTR_APP_ROW_DESC or SQL_ATTR_APP_PARAM_DESC, and the value in *ValuePtr was an implicitly allocated descriptor handle.
HY024 Invalid attribute value. Given the specified Attribute value, an invalid value was specified in *ValuePtr. (DB2 CLI returns this SQLSTATE only for connection and statement attributes that accept a discrete set of values, such as SQL_ATTR_ACCESS_MODE or SQL_ATTR_ASYNC_ENABLE. For all other connection and statement attributes, the driver must verify the value specified in *ValuePtr.)
HY090 Invalid string or buffer length. The argument TextLength was less than 1, but not equal to SQL_NTS.
HY092 Option type out of range. The value specified for the argument Attribute was not valid for this version of DB2 CLI.
HYC00 Driver not capable. The value specified for the argument Attribute was a valid connection or statement attribute for the version of the DB2 CLI driver, but was not supported by the data source.
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:

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 above diagnostic messages at prepare time. Therefore an application must also be able to handle these conditions when calling SQLExecute().

Restrictions

None.

Example

See the README file in the sqllib\samples\cli (or sqllib/samples/cli) subdirectory for a list of appropriate samples.

References


[ Top of Page | Previous Page | Next Page ]