`SQLPrepare()` - Prepare a statement

SQLPrepare() associates an SQL statement with the input statement handle and sends the statement to the database management system where it is prepared. 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), SQLCloseCursor() must be called to close the cursor, before SQLPrepare() is called.

ODBC specifications for `SQLPrepare()`

Table 1. `SQLPrepare()` specifications
ODBC specification level	In X/Open CLI CAE specification?	In ISO CLI specification?
1.0	Yes	Yes

Syntax

SQLRETURN   SQLPrepare       (SQLHSTMT          hstmt,
                              SQLCHAR      FAR  *szSqlStr,
                              SQLINTEGER        cbSqlStr);

Function arguments

The following table lists the data type, use, and description for each argument in this function.

Table 2. `SQLPrepare()` arguments
Data type	Argument	Use	Description
SQLHSTMT	hstmt	input	Statement handle. There must not be an open cursor associated with hstmt.
SQLCHAR *	szSqlStr	input	SQL statement string.
SQLINTEGER	cbSqlStr	input	The length, in bytes, of the contents of the szSqlStr argument. This must be set to either the exact length of the SQL statement in szSqlstr, or to SQL_NTS if the statement text is nul-terminated.

Usage

If the SQL statement text contains vendor escape clause sequences, Db2 ODBC first modifies 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_NOSCAN statement attribute should be set to SQL_NOSCAN_ON at the statement level so that Db2 ODBC does not perform a scan for any vendor escape clauses.

When a statement is prepared using SQLPrepare(), the application can request information about the format of the result set (if the statement is a query) by calling:

SQLNumResultCols()
SQLDescribeCol()
SQLColAttribute()

The SQL statement string can 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 question mark character (?) that indicates a position in the statement where an application supplied value is to be substituted when SQLExecute() is called. The bind parameter functions, SQLBindParameter() is used to bind (associate) application values with each parameter marker and to indicate if any data conversion should be performed at the time the data is transferred.

All parameters must be bound before calling SQLExecute().

After the application processes the results from the SQLExecute() call, it can execute the statement again with new (or the same) parameter values.

The SQL statement cannot be a COMMIT or ROLLBACK. SQLEndTran() must be called to issue COMMIT or ROLLBACK. For more information about SQL statements, that Db2 for z/OS® supports, see the topic Differences between Db2 ODBC and embedded SQL.

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.

If the statement that is being prepared is a MERGE statement, the statement text cannot include the FOR n ROWS clause. To specify the number of rows to be merged, use the SQLSetStmtAttr() function.

Return codes

After you call SQLPrepare(), it returns one of the following values:

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

The following table lists each SQLSTATE that this function generates, with a description and explanation for each value.

Table 3. `SQLPrepare()` SQLSTATEs
SQLSTATE	Description	Explanation
01504	The UPDATE or DELETE statement does not include a WHERE clause.	szSqlStr contains an UPDATE or DELETE statement which did not contain a WHERE clause.
08S01	Communication link failure.	The communication link between the application and data source fails before the function completes.
21S01	Insert value list does not match column list.	szSqlStr contains an INSERT or MERGE 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.	szSqlStr contains a CREATE VIEW statement and the number of names specified is not the same degree as the derived table defined by the query specification.
24000	Invalid cursor state.	A cursor is already opened on the statement handle.
34000	Invalid cursor name.	szSqlStr contains a positioned DELETE or a positioned UPDATE and the cursor referenced by the statement being executed is not open.
37xxx¹	Invalid SQL syntax.	szSqlStr contains one or more of the following: A COMMIT A ROLLBACK An SQL statement that the connected database server cannot prepare A statement containing a syntax error
40001	Transaction rollback.	The transaction to which this SQL statement belongs is rolled back due to deadlock or timeout.
42xxx ¹	Syntax error or access rule violation	These SQLSTATEs indicate one of the following errors: For 425xx, the authorization ID does not have permission to execute the SQL statement that the szSqlStr argument contains. For 42xxx, a variety of syntax or access problems with the statement occur.
42S01	Database object already exists.	szSqlStr contains a CREATE TABLE or CREATE VIEW statement and the table name or view name specified already exists.
42S02	Database object does not exist.	szSqlStr contains an SQL statement that references a table name or a view name that does not exist.
42S11	Index already exists.	szSqlStr contains a CREATE INDEX statement and the specified index name already exists.
42S12	Index not found.	szSqlStr contains a DROP INDEX statement and the specified index name does not exist.
42S21	Column already exists.	szSqlStr contains 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.	szSqlStr contains an SQL statement that references a column name that does not exist.
58004	Unexpected system failure.	Unrecoverable system error.
HY001	Memory allocation failure.	Db2 ODBC is not able to allocate the required memory to support the execution or the completion of the function.
HY009	Invalid use of a null pointer.	szSqlStr is a null pointer.
HY010	Function sequence error.	The function is called during a data-at-execute operation. (That is, the function is called during a procedure that uses the `SQLParamData()` or `SQLPutData()` functions.)
HY013	Unexpected memory handling error.	Db2 ODBC is not able to access the memory that is required to support execution or completion of the function.
HY014	No more handles.	Db2 ODBC is not able to allocate a handle due to low internal resources.
HY090	Invalid string or buffer length.	The argument cbSqlStr is less than 1, but not equal to SQL_NTS.
Note: xxx refers to any SQLSTATE with that class code. For example, 37xxx refers to any SQLSTATE with a class code of '37'.

Not all database management systems 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

If the statement that is being prepared is a MERGE statement, the statement text cannot include the FOR n ROWS clause. To specify the number of rows to be merged, use the SQLSetStmtAttr() function with the SQL_ATTR_PARAMSET_SIZE statement attribute.

Example

The following example shows an application that uses SQLPrepare() to prepare an SQL statement. This same SQL statement is then executed twice, each time with different parameter values.

Figure 1. An application that prepares an SQL statement before execution

 /******************************************************************/
 /*  Prepare a query and execute that query twice                  */
 /*  specifying a unique value for the parameter marker.           */
 /******************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>
#include "sqlcli1.h"
int main( )
{
   SQLHENV         hEnv    = SQL_NULL_HENV;
   SQLHDBC         hDbc    = SQL_NULL_HDBC;
   SQLHSTMT        hStmt   = SQL_NULL_HSTMT;
   SQLRETURN       rc      = SQL_SUCCESS;
   SQLINTEGER      RETCODE = 0;
   char            *pDSN = "STLEC1";
   SWORD           cbCursor;
   SDWORD          cbValue1;
   SDWORD          cbValue2;
   char            employee [30];
   int             salary = 0;
   int             param_salary = 30000;
   char            *stmt = "SELECT NAME, SALARY FROM EMPLOYEE WHERE SALARY > ?";
   (void) printf ("**** Entering CLIP07.\n\n");
  /*****************************************************************/
  /* Allocate environment handle                                   */
  /*****************************************************************/
   rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
   if (rc != SQL_SUCCESS)
     goto dberror;
  /*****************************************************************/
  /* Allocate connection handle to DSN                             */
  /*****************************************************************/
   rc = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
   if (rc != SQL_SUCCESS )      // Could not get a connect handle
     goto dberror;
  /*****************************************************************/
  /* CONNECT TO data source (STLEC1)                               */
  /*****************************************************************/
   rc = SQLConnect(hDbc,        // Connect handle
                        (SQLCHAR *) pDSN, // DSN
                        SQL_NTS,     // DSN is nul-terminated
                        NULL,        // Null UID
                        0   ,
                        NULL,        // Null Auth string
                        0);
   if (rc != SQL_SUCCESS )      // Connect failed
     goto dberror;
  /*****************************************************************/
  /* Allocate statement handles                                    */
  /*****************************************************************/
  rc = SQLAllocHandle (SQL_HANDLE_STMT, hDbc, &hStmt);
  if (rc != SQL_SUCCESS)
    goto dberror;
  /*****************************************************************/
  /* Prepare the query for multiple execution within current       */
  /* transaction. Note that query is collapsed when transaction    */
  /* is committed or rolled back.                                  */
  /*****************************************************************/
  rc = SQLPrepare (hStmt,
                   (SQLCHAR *) stmt,
                   strlen(stmt));
  if (rc != SQL_SUCCESS)
  {
    (void) printf ("**** PREPARE OF QUERY FAILED.\n");
    goto dberror;
  }
  rc = SQLBindCol (hStmt,           // bind employee name
                   1,
                   SQL_C_CHAR,
                   employee,
                   sizeof(employee),
                   &cbValue1);
  if (rc != SQL_SUCCESS)
  {
    (void) printf ("**** BIND OF NAME FAILED.\n");
    goto dberror;
  }
  rc = SQLBindCol (hStmt,           // bind employee salary
                   2,
                   SQL_C_LONG,
                   &salary,
                   0,
                   &cbValue2);
  if (rc != SQL_SUCCESS)
  {
    (void) printf ("**** BIND OF SALARY FAILED.\n");
    goto dberror;
  }
  /*****************************************************************/
  /* Bind parameter to replace '?' in query. This has an initial   */
  /* value of 30000.                                               */
  /*****************************************************************/
  rc = SQLBindParameter (hStmt,
                         1,
                         SQL_PARAM_INPUT,
                         SQL_C_LONG,
                         SQL_INTEGER,
                         0,
                         0,
                         &param_salary,
                         0,
                         NULL);
  /*****************************************************************/
  /* Execute prepared statement to generate answer set.            */
  /*****************************************************************/
  rc = SQLExecute (hStmt);
  if (rc != SQL_SUCCESS)
  {
    (void) printf ("**** EXECUTE OF QUERY FAILED.\n");
    goto dberror;
  }
  /*****************************************************************/
  /* Answer set is available -- Fetch rows and print employees     */
  /* and salary.                                                   */
  /*****************************************************************/
  (void) printf ("**** Employees whose salary exceeds %d follow.\n\n",
                 param_salary);
  while ((rc = SQLFetch (hStmt)) == SQL_SUCCESS)
  {
    (void) printf ("**** Employee Name %s with salary %d.\n",
                   employee,
                   salary);
  }
  /*****************************************************************/
  /* Close query --- note that query is still prepared. Then change*/
  /* bound parameter value to 100000. Then re-execute query.       */
  /*****************************************************************/
  rc = SQLCloseCursor(hStmt);
  param_salary = 100000;
  rc = SQLExecute (hStmt);
  if (rc != SQL_SUCCESS)
  {
    (void) printf ("**** EXECUTE OF QUERY FAILED.\n");
    goto dberror;
  }
  /*****************************************************************/
  /* Answer set is available -- Fetch rows and print employees     */
  /* and salary.                                                   */
  /*****************************************************************/
  (void) printf ("**** Employees whose salary exceeds %d follow.\n\n",
                 param_salary);
  while ((rc = SQLFetch (hStmt)) == SQL_SUCCESS)
  {
    (void) printf ("**** Employee Name %s with salary %d.\n",
                   employee,
                   salary);
  }
  /*****************************************************************/
  /* Deallocate statement handles -- statement is no longer in a   */
  /* prepared state.                                               */
  /*****************************************************************/
  rc = SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
  /*****************************************************************/
  /* DISCONNECT from data source                                   */
  /*****************************************************************/
   rc = SQLDisconnect(hDbc);
   if (rc != SQL_SUCCESS)
     goto dberror;
  /*****************************************************************/
  /* Deallocate connection handle                                  */
  /*****************************************************************/
   rc = SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
   if (rc != SQL_SUCCESS)
     goto dberror;
  /*****************************************************************/
  /* Free environment handle                                       */
  /*****************************************************************/
   rc = SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
   if (rc == SQL_SUCCESS)
     goto exit;
   dberror:
   RETCODE=12;
   exit:
   (void) printf ("**** Exiting  CLIP07.\n\n");
   return RETCODE;
}

SQLPrepare() - Prepare a statement

ODBC specifications for SQLPrepare()