MDAC 2.5 SDK - ODBC Programmer's Reference
Chapter 21: ODBC API Reference


 

SQLPrepare

Conformance

Version Introduced: ODBC 1.0
Standards Compliance: ISO 92

Summary

SQLPrepare prepares an SQL string for execution.

Syntax

SQLRETURN SQLPrepare(
     SQLHSTMT     StatementHandle,
     SQLCHAR *     StatementText,
     SQLINTEGER     TextLength);

Arguments

StatementHandle

[Input]
Statement handle.

StatementText

[Input]
SQL text string.

TextLength

[Input]
Length of *StatementText.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.

Diagnostics

When SQLPrepare returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value can be obtained by calling SQLGetDiagRec with a HandleType of SQL_HANDLE_STMT and a Handle of StatementHandle. The following table lists the SQLSTATE values commonly returned by SQLPrepare and explains each one in the context of this function; the notation "(DM)" precedes the descriptions of SQLSTATEs returned by the Driver Manager. The return code associated with each SQLSTATE value is SQL_ERROR, unless noted otherwise.

SQLSTATE Error Description
01000 General warning Driver-specific informational message. (Function returns SQL_SUCCESS_WITH_INFO.)
01S02 Option value changed A specified statement attribute was invalid because of implementation working conditions, so a similar value was temporarily substituted. (SQLGetStmtAttr can be called to determine what the temporarily substituted value is.) The substitute value is valid for the StatementHandle until the cursor is closed. The statement attributes that can be changed are: SQL_ATTR_CONCURRENCY
SQL_ATTR_CURSOR_TYPE
SQL_ATTR_KEYSET_SIZE
SQL_ATTR_MAX_LENGTH
SQL_ATTR_MAX_ROWS
SQL_ATTR_QUERY_TIMEOUT
SQL_ATTR_SIMULATE_CURSOR

(Function returns SQL_SUCCESS_WITH_INFO.)

08S01 Communication link failure The communication link between the driver and the data source to which the driver 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 Degree 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 neither "%" nor "_".
24000 Invalid cursor state (DM) A cursor was open on the StatementHandle, and SQLFetch or SQLFetchScroll had been called.

A cursor was open on the StatementHandle, but SQLFetch or SQLFetchScroll had not been called.

34000 Invalid cursor name *StatementText contained a positioned DELETE or a positioned UPDATE, and the cursor referenced by the statement being prepared was not open.
3D000 Invalid catalog name The catalog name specified in StatementText was invalid.
3F000 Invalid schema name The schema name specified in StatementText was invalid.
42000 Syntax error or access violation *StatementText contained an SQL statement that was not preparable or contained a syntax error.

*StatementText contained a statement for which the user did not have the required privileges.

42S01 Base table or view already exists *StatementText contained a CREATE TABLE or CREATE VIEW statement, and the table name or view name specified already exists.
42S02 Base table or view not found *StatementText contained a DROP TABLE or a DROP VIEW statement, and the specified table name or view name did not exist.

*StatementText contained an ALTER TABLE statement, and the specified table name did not exist.

*StatementText contained a CREATE VIEW statement, and a table name or view name defined by the query specification did not exist.

*StatementText contained a CREATE INDEX statement, and the specified table name did not exist.

*StatementText contained a GRANT or REVOKE statement, and the specified table name or view name did not exist.

*StatementText contained a SELECT statement, and a specified table name or view name did not exist.

*StatementText contained a DELETE, INSERT, or UPDATE statement, and the specified table name did not exist.

*StatementText contained a CREATE TABLE statement, and a table specified in a constraint (referencing a table other than the one being created) did not exist.

42S11 Index already exists *StatementText contained a CREATE INDEX statement, and the specified index name already existed.
42S12 Index not found *StatementText contained a DROP INDEX statement, and the specified index name did not exist.
42S21 Column already exists *StatementText contained an ALTER TABLE statement, and the column specified in the ADD clause is not unique or identifies an existing column in the base table.
42S22 Column not found *StatementText contained a CREATE INDEX statement, and one or more of the column names specified in the column list did not exist.

*StatementText contained a GRANT or REVOKE statement, and a specified column name did not exist.

*StatementText contained a SELECT, DELETE, INSERT, or UPDATE statement, and a specified column name did not exist.

*StatementText contained a CREATE TABLE statement, and a column specified in a constraint (referencing a table other than the one being created) did not exist.

HY000 General error An error occurred for which there was no specific SQLSTATE and for which no implementation-specific SQLSTATE was defined. The error message returned by SQLGetDiagRec in the *MessageText buffer describes the error and its cause.
HY001 Memory allocation error The driver was 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, and 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 use of null pointer (DM) StatementText was a null pointer.
HY010 Function sequence error (DM) An asynchronously executing function (not this one) was called for the StatementHandle and was still executing when this function was called.

(DM) SQLExecute, SQLExecDirect, SQLBulkOperations, or SQLSetPos was called for the StatementHandle and returned SQL_NEED_DATA. This function was called before data was sent for all data-at-execution parameters or columns.

HY013 Memory management error The function call could not be processed because the underlying memory objects could not be accessed, possibly because of low memory conditions.
HY090 Invalid string or buffer length (DM) The argument TextLength was less than or equal to 0 but not equal to SQL_NTS.
HYC00 Optional feature not implemented The concurrency setting was invalid for the type of cursor defined.

The SQL_ATTR_USE_BOOKMARKS statement attribute was set to SQL_UB_VARIABLE, and the SQL_ATTR_CURSOR_TYPE statement attribute was set to a cursor type for which the driver does not support bookmarks.

HYT00 Timeout expired The timeout period expired before the data source returned the result set. The timeout period is set through SQLSetStmtAttr, SQL_ATTR_QUERY_TIMEOUT.
HYT01 Connection timeout expired The connection timeout period expired before the data source responded to the request. The connection timeout period is set through SQLSetConnectAttr, SQL_ATTR_CONNECTION_TIMEOUT.
IM001 Driver does not support this function (DM) The driver associated with the StatementHandle does not support the function.

Comments

The application calls SQLPrepare to send an SQL statement to the data source for preparation. For more information about prepared execution, see "Prepared Execution" in Chapter 9, "Executing Statements." The application can include one or more parameter markers in the SQL statement. To include a parameter marker, the application embeds a question mark (?) into the SQL string at the appropriate position. For information about parameters, see "Statement Parameters" in Chapter 9, "Executing Statements."

Note   If an application uses SQLPrepare to prepare and SQLExecute to submit a COMMIT or ROLLBACK statement, it will not be interoperable between DBMS products. To commit or roll back a transaction, call SQLEndTran.

The driver can modify the statement to use the form of SQL used by the data source and then submit it to the data source for preparation. In particular, the driver modifies the escape sequences used to define SQL syntax for certain features. (For a description of SQL statement grammar, see "Escape Sequences in ODBC" in Chapter 8, "SQL Statements," and Appendix C, "SQL Grammar.") For the driver, a statement handle is similar to a statement identifier in embedded SQL code. If the data source supports statement identifiers, the driver can send a statement identifier and parameter values to the data source.

Once a statement is prepared, the application uses the statement handle to refer to the statement in later function calls. The prepared statement associated with the statement handle can be reexecuted by calling SQLExecute until the application frees the statement with a call to SQLFreeStmt with the SQL_DROP option or until the statement handle is used in a call to SQLPrepare, SQLExecDirect, or one of the catalog functions (SQLColumns, SQLTables, and so on). Once the application prepares a statement, it can request information about the format of the result set. For some implementations, calling SQLDescribeCol or SQLDescribeParam after SQLPrepare might not be as efficient as calling the function after SQLExecute or SQLExecDirect.

Some drivers cannot return syntax errors or access violations when the application calls SQLPrepare. A driver can handle syntax errors and access violations, only syntax errors, or neither syntax errors nor access violations. Therefore, an application must be able to handle these conditions when calling subsequent related functions such as SQLNumResultCols, SQLDescribeCol, SQLColAttribute, and SQLExecute.

Depending on the capabilities of the driver and data source, parameter information (such as data types) might be checked when the statement is prepared (if all parameters have been bound) or when it is executed (if all parameters have not been bound). For maximum interoperability, an application should unbind all parameters that applied to an old SQL statement before preparing a new SQL statement on the same statement. This prevents errors that are due to old parameter information being applied to the new statement.

Important   Committing a transaction, either by explicitly calling SQLEndTran or by working in autocommit mode, can cause the data source to delete the access plans for all statements on a connection. For more information, see the SQL_CURSOR_COMMIT_BEHAVIOR and SQL_CURSOR_ROLLBACK_BEHAVIOR information types in SQLGetInfo and "Effect of Transactions on Cursors and Prepared Statements" in Chapter 14, "Transactions."

Code Example

See SQLBindParameter, SQLPutData, and SQLSetPos.

Related Functions

For information about See
Allocating a statement handle SQLAllocHandle
Binding a buffer to a column in a result set SQLBindCol
Binding a buffer to a parameter SQLBindParameter
Canceling statement processing SQLCancel
Executing a commit or rollback operation SQLEndTran
Executing an SQL statement SQLExecDirect
Executing a prepared SQL statement SQLExecute
Returning the number of rows affected by a statement SQLRowCount
Setting a cursor name SQLSetCursorName