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

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

1. For each data-at-execution column, put special values into the buffers previously bound by SQLBindCol:
   • Into the pcbValue data value buffer, put SQL_LEN_DATA_AT_EXEC(length) where length is the total length of the text, ntext, or image column data in bytes.
   • Into the rgbValue data length buffer, put a program-defined column identifier.
2. Calling SQLSetPos returns SQL_NEED_DATA, which indicates that data-at-execution columns are ready for processing.
3. For each data-at-execution column:
   • Call SQLParamData to get the column array pointer. It will return SQL_NEED_DATA if there is another data-at-execution column.
   • Call SQLPutData one or more times to send the column data, until length is sent.
4. Call SQLParamData to indicate that all the data for the final data-at-execution column has been sent. It will not return SQL_NEED_DATA.

Examples

This example shows using SQLGetData to retrieve the data from a data-at-execution text column. Error-checking code was removed to simplify this example.

// Sample ODBC3 console application to read SQL_LONGVARChar

// data using SQLGetData.

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

#define BUFFERSIZE    450

SQLHENV        henv = SQL_NULL_HENV;

SQLHDBC        hdbc1 = SQL_NULL_HDBC;

SQLHSTMT        hstmt1 = SQL_NULL_HSTMT;

int main() {

    RETCODE retcode;

    SWORD    cntr;

    //SQLGetData variables.

    UCHAR    Data[BUFFERSIZE];

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

    SDWORD    cbTxtSize;

    // Clear data array.

    for(cntr = 0; cntr < BUFFERSIZE; cntr++)

        Data[cntr] = 0x00;

     // 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; prepare, then execute command.

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

    retcode = SQLExecDirect(hstmt1,

                            "SELECT Memo1 FROM emp3",

                            SQL_NTS);

    // Get first row.

    retcode = SQLFetch(hstmt1);

    // Get the SQL_LONG column.

    cntr = 1;

    do {

        retcode = SQLGetData(hstmt1,    // hstmt

            1,                        // ipar

            SQL_C_CHAR,                // fCType

            Data,                    // rgbValue

            cbBatch,                    // cbValueMax

            &cbTxtSize);                // pcbValue

        if ( retcode != SQL_NO_DATA ) {

        printf("GetData iteration %d, pcbValue = %d,\n",

               cntr++, cbTxtSize);

        printf("Data = %s\n\n", Data);

        }

    } while (retcode != SQL_NO_DATA);

    /* Clean up. */

    SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);

    SQLDisconnect(hdbc1);

    SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);

    SQLFreeHandle(SQL_HANDLE_ENV, henv);

    return(0);

} // End Main.

See Also