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

How to Install SQL Server 2000

How To

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 is 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 is 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);
   // 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.
   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

Bound vs. Unbound text and image Columns

SQLBindParameter

Data-at-execution and text, ntext, or image Columns

SQLPutData

Managing text and image Columns