How to execute a statement directly (ODBC)

To execute a statement directly and one time only

1. If the statement has parameter markers, use SQLBindParameter to bind each parameter to a program variable. Fill the program variables with data values, and then set up any data-at-execution parameters.
2. Call SQLExecDirect to execute the statement.
3. If data-at-execution input parameters are used, SQLExecDirect returns SQL_NEED_DATA. Send the data in chunks by using SQLParamData and SQLPutData.

To execute a statement multiple times by using 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 the parameter status indicators.
2. 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.
   • Set up any data-at-execution text or image parameters.
   • Put S data values and S data lengths into the bound parameter arrays.
3. Call SQLExecDirect to execute the statement. The driver efficiently executes the statement S times, once for each set of parameters.
4. If data-at-execution input parameters are used, SQLExecDirect returns SQL_NEED_DATA. Send the data in chunks by using SQLParamData and SQLPutData.

To execute a statement multiple times by using row-wise parameter binding

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 the parameter status indicators.
3. For each parameter marker, call SQLBindParameter to point the parameter’s 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.
4. Fill the bound parameter buffer array with data values.
5. Call SQLExecDirect to execute the statement. The driver efficiently executes the statement S times, once for each set of parameters.
6. If data-at-execution input parameters are used, SQLExecDirect returns SQL_NEED_DATA. Send the data in chunks by using SQLParamData and SQLPutData.

Column-wise and row-wise binding are more typically used in conjunction with SQLPrepare and SQLExecute than with SQLExecDirect.

Examples

This example shows executing a SELECT statement by using SQLExecDirect. 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);

    // Execute an SQL statement directly on the statement handle.

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

    retcode = SQLExecDirect(hstmt1,

                            "SELECT au_lname FROM authors",

                            SQL_NTS);

    // 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