How to use data-at-execution parameters (ODBC)

To use data-at-execution text, ntext, or image parameters

1. When calling SQLBindParameter to bind a program buffer to the statement parameter:
   • Use a pcbValue of SQL_LEN_DATA_AT_EXEC(length) where length is the total length of the text, ntext, or image parameter data in bytes.
   • Use an rgbValue of a program-defined parameter identifier.
2. Calling SQLExecDirect or SQLExecute returns SQL_NEED_DATA, which indicates that data-at-execution parameters are ready for processing.
3. For each data-at-execution parameter:
   • Call SQLParamData to get the program-defined parameter ID. It will return SQL_NEED_DATA if there is another data-at-execution parameter.
   • Call SQLPutData one or more times, to send the parameter data, until length is sent.
4. Call SQLParamData to indicate that all the data for the final data-at-execution parameter has been sent. It will not return SQL_NEED_DATA.

Examples

This example shows using SQLPutData to fill the data in a data-at-execution text parameter. Error-checking code was removed to simplify this example.

// Sample ODBC3 console application to write SQL_LONGVARCHAR data

// using SQLPutData.

// Assumes DSN has table:

//  SQLSrvr: CREATE TABLE emp3 (NAME char(30), AGE int,

//           BIRTHDAY datetime, Memo1 text)

#include <stdio.h>

#include <string.h>

#include <windows.h>

#include <sql.h>

#include <sqlext.h>

#include <odbcss.h>

#define TEXTSIZE    12000

SQLHENV        henv = SQL_NULL_HENV;

SQLHDBC        hdbc1 = SQL_NULL_HDBC;    

SQLHSTMT        hstmt1 = SQL_NULL_HSTMT;

int main() {

    RETCODE retcode;

    // SQLBindParameter variables.

    SDWORD        cbTextSize, lbytes;

    //SQLParamData variable.

    PTR            pParmID;

    //SQLPutData variables.

    UCHAR    Data[] =

            "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"

            "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"

            "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"

            "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"

            "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"

            "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"

            "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"

            "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"

            "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz"

            "abcdefghijklmnopqrstuvwxyz";

    SDWORD    cbBatch = (SDWORD)sizeof(Data)-1;

     // Allocate the ODBC environment and save handle.

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

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

    retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,

                            (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);

    // Allocate ODBC connection handle and connect.

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

    retcode = SQLConnect(hdbc1, "MyDSN", SQL_NTS,

                         "sa", SQL_NTS, "MyPassWord", SQL_NTS);

    // Allocate statement handle.

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

    // Set parameters based on total data to send.

    lbytes = (SDWORD)TEXTSIZE;

    cbTextSize = SQL_LEN_DATA_AT_EXEC(lbytes);

    // Bind the parameter marker.

    retcode = SQLBindParameter(hstmt1,    // hstmt

            1,                            // ipar

            SQL_PARAM_INPUT,                // fParamType

            SQL_C_CHAR,                    // fCType

            SQL_LONGVARCHAR,                // FSqlType

            lbytes,                        // cbColDef

            0,                            // ibScale

            (VOID *)1,                    // rgbValue

            0,                            // cbValueMax

            &cbTextSize);                // pcbValue

    // Execute the command.

    retcode = SQLExecDirect(hstmt1,

    "INSERT INTO emp3 VALUES('Paul Borm', 46,'1950-11-24 00:00:00', ?)",

                            SQL_NTS);

    // Check to see if NEED_DATA; if yes, use SQLPutData.

    retcode = SQLParamData(hstmt1, &pParmID);

    if (retcode == SQL_NEED_DATA)

    {

        while (lbytes > cbBatch)

        {

            SQLPutData(hstmt1, Data, cbBatch);

            lbytes -= cbBatch;

        }

        // Put final batch.

        SQLPutData(hstmt1, Data, lbytes);

    }

    else

    {

           ProcessErrorMessages(SQL_HANDLE_STMT, hstmt1,

                           "SQLPutData Failed\n\n");

           return(9);

    }

    // Make final SQLParamData call.

    retcode = SQLParamData(hstmt1, &pParmID);

    /* Clean up. */

    SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);

    SQLDisconnect(hdbc1);

    SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);

    SQLFreeHandle(SQL_HANDLE_ENV, henv);

    return(0);

}

See Also