DB2 10.5 for Linux, UNIX, and Windows

SQLExtendedPrepare function (CLI) - Prepare a statement and set statement attributes

Prepares a statement and set a group of statement attributes, all in one call.

Specification:

CLI 6.0

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

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

Syntax

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

Function arguments

Table 1. SQLExtendedPrepare() arguments
Data type	Argument	Use	Description
SQLHSTMT	StatementHandle	Input	Statement handle.
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.
SQLINTEGER	cPars	Input	Number of parameter markers in statement.
SQLSMALLINT	cStmtType	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 SQLExtendedPrepare():

The SQL statements will not be scanned for ODBC/vendor escape clauses. It behaves as if the SQL_ATTR_NOSCAN statement attribute is set to SQL_NOSCAN. If the SQL statement contains ODBC/vendor escape clauses then SQLExtendedPrepare() cannot be used.
You must indicate in advance (through cPars) the number of parameter markers that are included in the SQL 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 prepared. See List of cStmtType Values for the list of possible values.

The final three arguments are used to indicate a 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.

List of cStmtType Values

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

SQL_CLI_STMT_UNDEFINED
SQL_CLI_STMT_ALTER_TABLE
SQL_CLI_STMT_CREATE_INDEX
SQL_CLI_STMT_CREATE_TABLE
SQL_CLI_STMT_CREATE_VIEW
SQL_CLI_STMT_DELETE_SEARCHED
SQL_CLI_STMT_DELETE_POSITIONED
SQL_CLI_STMT_GRANT
SQL_CLI_STMT_INSERT
SQL_CLI_STMT_INSERT_VALUES
SQL_CLI_STMT_REVOKE
SQL_CLI_STMT_SELECT
SQL_CLI_STMT_UPDATE_SEARCHED
SQL_CLI_STMT_UPDATE_POSITIONED
SQL_CLI_STMT_CALL
SQL_CLI_STMT_SELECT_FOR_UPDATE
SQL_CLI_STMT_WITH
SQL_CLI_STMT_SELECT_FOR_FETCH
SQL_CLI_STMT_VALUES
SQL_CLI_STMT_CREATE_TRIGGER
SQL_CLI_STMT_SELECT_OPTIMIZE_FOR_NROWS
SQL_CLI_STMT_SELECT_INTO
SQL_CLI_STMT_CREATE_PROCEDURE
SQL_CLI_STMT_CREATE_FUNCTION
SQL_CLI_STMT_SET_CURRENT_QUERY_OPT

Return codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_STILL_EXECUTING
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 2. 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.	CLI did not support a value specified in pvParams, or a value specified in pvParams was invalid because of SQL constraints or requirements, so CLI substituted a similar value. (Function returns SQL_SUCCESS_WITH_INFO.)
08S01	Communication link failure.	The communication link between 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 complications: 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. 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.
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 resource limitations.
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. (CLI returns this SQLSTATE only for connection and statement attributes that accept a discrete set of values, such as SQL_ATTR_ACCESS_MODE. 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 CLI.
HYC00	Driver not capable.	The value specified for the argument Attribute was a valid connection or statement attribute for the version of the 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. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for `SQLSetStmtAttr()`.
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 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().

Restrictions

When accessing Informix® database servers, only Informix database server specific SQLExtendedPrepare() attributes are supported. If any SQLExtendedPrepare() attributes not supported by the Informix database server are used, a "Driver not capable" error is returned.