SQLPrepare - Prepare a statement

 

SQLPrepare() associates an SQL statement with the input statement handle and sends the statement to the DBMS to be prepared. The application can reference this prepared statement by passing the statement handle to other functions.

If the statement handle has been used with a SELECT statement, SQLFreeStmt() must be called to close the cursor, before calling SQLPrepare().

 

Syntax

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

 

Function arguments

Table 1. 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 Length of contents of 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 null-terminated.

 

Usage

As soon as a statement has been prepared using SQLPrepare(), the application can request information about the format of the result set (if it is a SELECT statement) by calling:

A prepared statement can be processed once, or multiple times by calling SQLExecute(). The SQL statement remains associated with the statement handle until the handle is used with another SQLPrepare(), SQLExecDirect(), SQLColumns(), SQLSpecialColumns(), SQLStatistics(), or SQLTables().

The SQL statement string might contain parameter markers. A parameter marker is represented by a "?" character, and indicates a position in the statement where the value of an application variable is to be substituted, when SQLExecute() is called. SQLBindParam() is used to bind (or associate) an application variable to each parameter marker, and to indicate if any data conversion should be performed at the time the data is transferred.

The SQL statement cannot be a COMMIT or ROLLBACK. SQLTransact() must be called to issue COMMIT or ROLLBACK.

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.

 

Return codes

 

Diagnostics

Table 2. SQLPrepare SQLSTATEs
SQLSTATE Description Explanation
24000 Cursor state that is not valid There is an open cursor on the specified hstmt.
37xxx Syntax error or access violation szSqlStr contained one or more of the following statements:

  • A COMMIT

  • A ROLLBACK

  • An SQL statement that the connected database server cannot prepare

  • A statement containing a syntax error
HY001 Memory allocation failure The driver is unable to allocate memory required to support the processing or completion of the function.
HY009 Argument value that is not valid szSqlStr is a null pointer.

The argument cbSqlStr is less than 1, but not equal to SQL_NTS.

HY013 * Memory management problem The driver is unable to access memory required to support the processing or completion of the function.
HY021 Internal descriptor that is not valid The internal descriptor cannot be addressed or allocated, or it contains a value that is not valid.

Not all Database Management Systems (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().

 

Example

Refer to Example: Interactive SQL and the equivalent DB2 UDB CLI function calls for a listing of the check_error, initialize, and terminate functions used in the following example.

By using the code examples, you agree to the terms of the Code license and disclaimer information. 

/*************************************************************************
** file = prepare.c **
** Example of preparing then repeatedly executing an SQL statement.
**
** Functions used: **
**        SQLAllocConnect      SQLFreeConnect **        SQLAllocEnv          SQLFreeEnv **        SQLAllocStmt         SQLFreeStmt **        SQLConnect           SQLDisconnect **
**        SQLBindCol           SQLFetch **        SQLTransact          SQLError **        SQLPrepare           SQLSetParam **        SQLExecute **************************************************************************/
 
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include "sqlcli.h"
 
#define MAX_STMT_LEN 255
 
int initialize(SQLHENV *henv,
               SQLHDBC *hdbc);
 
int terminate(SQLHENV henv,
              SQLHDBC hdbc);
 
int print_error (SQLHENV    henv,
                 SQLHDBC    hdbc,
                 SQLHSTMT   hstmt);
 
int check_error (SQLHENV    henv,
                 SQLHDBC    hdbc,
                 SQLHSTMT   hstmt,
                 SQLRETURN  rc);
 
/*******************************************************************
** main ** - initialize ** - terminate *******************************************************************/
int main()
{
    SQLHENV     henv;
    SQLHDBC     hdbc;
    SQLCHAR     sqlstmt[MAX_STMT_LEN + 1]="";
    SQLRETURN   rc;
 
    rc = initialize(&henv, &hdbc);
    if (rc == SQL_ERROR) return(terminate(henv, hdbc));
 
    {SQLHSTMT   hstmt;
     SQLCHAR    sqlstmt[]="SELECT deptname, location from org where division = ?";
     SQLCHAR    deptname[15],
                location[14],
                division[11];
 
     SQLINTEGER rlength,
                plength;
 
        rc = SQLAllocStmt(hdbc, &hstmt);
        if (rc != SQL_SUCCESS )
            check_error (henv, hdbc, SQL_NULL_HSTMT, rc);
 
        /* prepare statement for multiple use */
        rc = SQLPrepare(hstmt, sqlstmt, SQL_NTS);
        if (rc != SQL_SUCCESS )
            check_error (henv, hdbc, hstmt, rc);
 
        /* bind division to parameter marker in sqlstmt */
        rc = SQLSetParam(hstmt, 1, SQL_CHAR, SQL_CHAR, 10, 10, division,
                     &plength);
        if (rc != SQL_SUCCESS )
            check_error (henv, hdbc, hstmt, rc);
 
        /* bind deptname to first column in the result set */
        rc = SQLBindCol(hstmt, 1, SQL_CHAR, (SQLPOINTER) deptname, 15,
                        &rlength);
        if (rc != SQL_SUCCESS )
            check_error (henv, hdbc, hstmt, rc);
        rc = SQLBindCol(hstmt, 2, SQL_CHAR, (SQLPOINTER) location, 14,
                         &rlength);
        if (rc != SQL_SUCCESS )
            check_error (henv, hdbc, hstmt, rc);
 
        printf("\nEnter Division Name or 'q' to quit:\n");
        printf("(Eastern, Western, Midwest, Corporate)\n");
        gets(division);
        plength = SQL_NTS;
 
        while(division[0] != 'q')
        {
            rc = SQLExecute(hstmt);
            if (rc != SQL_SUCCESS )
                check_error (henv, hdbc, hstmt, rc);
 
            printf("Departments in %s Division:\n", division);
            printf("DEPTNAME       Location\n");
            printf("-------------- -------------\n");
 
            while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS)
            {
                printf("%-14.14s %-13.13s \n", deptname, location);
            }
            if (rc != SQL_NO_DATA_FOUND )
                check_error (henv, hdbc, hstmt, rc);
            SQLFreeStmt(hstmt, SQL_CLOSE);
            printf("\nEnter Division Name or 'q' to quit:\n");
            printf("(Eastern, Western, Midwest, Corporate)\n");
            gets(division);
        }
    }
 
    rc = SQLTransact(henv, hdbc, SQL_ROLLBACK);
    if (rc != SQL_SUCCESS )
        check_error (henv, hdbc, SQL_NULL_HSTMT, rc);
 
    terminate(henv, hdbc);
    return (0);
}/* end main */

 

References

 

Parent topic:

DB2 UDB CLI functions