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 is 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);
   // Notify ODBC that 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

Bound vs. Unbound text and image Columns

SQLBindCol

Data-at-execution and text/ntext/image Columns

SQLPutData

Managing text and image Columns