How to prepare and execute a statement (ODBC)

To prepare a statement once, and then execute it multiple times

1. Call SQLPrepare to prepare the statement.
2. Optionally, call SQLNumParams to determine the number of parameters in the prepared statement.
3. Optionally, for each parameter in the prepared statement:
   • Call SQLDescribeParam to get parameter information.
   • Bind each parameter to a program variable by using SQLBindParam. Set up any data-at-execution parameters.
4. For each execution of a prepared statement:
   • If the statement has parameter markers, put the data values into the bound parameter buffer.
   • Call SQLExecute to execute the prepared statement.
   • If data-at-execution input parameters are used, SQLExecute returns SQL_NEED_DATA. Send the data in chunks by using SQLParamData and SQLPutData.

To prepare a statement with column-wise parameter binding

1. Call SQLSetStmtAttr to set the following attributes:
   • Set SQL_ATTR_PARAMSET_SIZE to the number of sets (S) of parameters.
   • Set SQL_ATTR_PARAM_BIND_TYPE to SQL_PARAMETER_BIND_BY_COLUMN.
   • Set the SQL_ATTR_PARAMS_PROCESSED_PTR attribute to point to a SQLUINTEGER variable to hold the number of parameters processed.
   • Set SQL_ATTR_PARAMS_STATUS_PTR to point to an array[S] of SQLUSSMALLINT variables to hold parameter status indicators.
2. Call SQLPrepare to prepare the statement.
3. Optionally, call SQLNumParams to determine the number of parameters in the prepared statement.
4. Optionally, for each parameter in the prepared statement, call SQLDescribeParam to get parameter information.
5. For each parameter marker:
   • Allocate an array of S parameter buffers to store data values.
   • Allocate an array of S parameter buffers to store data lengths.
   • Call SQLBindParameter to bind the parameter data value and data length arrays to the statement parameter.
   • If the parameter is a data-at-execution text or image parameter, set it up.
   • If any data-at-execution parameters are used, set them up.
6. For each execution of a prepared statement:
   • Put the S data values and S data lengths into the bound parameter arrays.
   • Call SQLExecute to execute the prepared statement.
   • If data-at-execution input parameters are used, SQLExecute returns SQL_NEED_DATA. Send the data in chunks by using SQLParamData and SQLPutData.

To prepare a statement with row-wise bound parameters

1. Allocate an array[S] of structures, where S is the number of sets of parameters. The structure has one element for each parameter, and each element has two parts:
   • The first part is a variable of the appropriate data type to hold the parameter data.
   • The second part is a SQLINTEGER variable to hold the status indicator.
2. Call SQLSetStmtAttr to set the following attributes:
   • Set SQL_ATTR_PARAMSET_SIZE to the number of sets (S) of parameters.
   • Set SQL_ATTR_PARAM_BIND_TYPE to the size of the structure allocated in Step 1.
   • Set the SQL_ATTR_PARAMS_PROCESSED_PTR attribute to point to a SQLUINTEGER variable to hold the number of parameters processed.
   • Set SQL_ATTR_PARAMS_STATUS_PTR to point to an array[S] of SQLUSSMALLINT variables to hold parameter status indicators.
3. Call SQLPrepare to prepare the statement.
4. For each parameter marker, call SQLBindParameter to point the parameter data value and data length pointer to their variables in the first element of the array of structures allocated in Step 1. If the parameter is a data-at-execution parameter, set it up.
5. For each execution of a prepared statement:
   • Fill the bound parameter buffer array with data values.
   • Call SQLExecute to execute the prepared statement. The driver efficiently executes the SQL statement S times, once for each set of parameters.
   • If data-at-execution input parameters are used, SQLExecute returns SQL_NEED_DATA. Send the data in chunks by using SQLParamData and SQLPutData.

Examples

This example shows executing a SELECT statement by using SQLPrepare and SQLExecute. It has been simplified by removing all error checking.

#include <stdio.h>

#include <string.h>

#include <windows.h>

#include <sql.h>

#include <sqlext.h>

#include <odbcss.h>

#define MAXBUFLEN    255

SQLHENV        henv = SQL_NULL_HENV;

SQLHDBC        hdbc1 = SQL_NULL_HDBC;    

SQLHSTMT    hstmt1 = SQL_NULL_HSTMT;

int main()

{

    RETCODE retcode;

    // SQLBindCol variables

    SQLCHAR        szName[MAXNAME+1];

    SQLINTEGER    cbName;

     // Allocate the ODBC Environment and save handle.

    retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);

    // Let ODBC know this is an ODBC 3.0 application.

    retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,

                            (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);

    // Allocate an ODBC connection and connect.

    retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);

    retcode = SQLConnect(hdbc1,

                "MyDSN", SQL_NTS,    "sa", SQL_NTS,

                "MyPassWord", SQL_NTS);

    // Allocate a statement handle.

    retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);

    // Prepare and execute an SQL statement on the statement handle.

    // Uses a default result set because no cursor attributes are set.

    retcode = SQLPrepare(hstmt1,

                        "SELECT au_lname from authors", SQL_NTS);

    retcode = SQLExecute(hstmt1);

    // Simplified result set processing. Bind one column and

    // then fetch until SQL_NO_DATA.

    retcode = SQLBindCol(hstmt1, 1, SQL_C_CHAR,

                        szName, MAXNAME, &cbName);

    while ( ( retcode = SQLFetch(hstmt1) ) != SQL_NO_DATA )

        printf("Name = %s\n", szName);

    /* Clean up. */

    SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);

    SQLDisconnect(hdbc1);

    SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);

    SQLFreeHandle(SQL_HANDLE_ENV, henv);

    return(0);

}

See Also