Aracılığıyla paylaş

Veri yürütme (odbc) kullanarak tablo değerli bir parametre olarak veri gönderme

  • Makale

Bu benzer tüm bellek yordam, ancak tablo değerli parametresi için yürütme sırasında veri kullanır.

Tablo değerli parametreleri gösteren bir başka örnek için bkz: Tablo Valued Parametreler (odbc) kullan.

Bu örnekte, ne zaman SQLExecuteveya SQLExecDirectolan adı, sürücü sırasında veri sql_need_data döndürür. Uygulama çağrıları SQLParamDatakadar tekrar tekrar sürücü sırasında veri sql_need_data dışında bir değer verir. Sürücü döndürür ParameterValuePtruygulama hangi parametre bilgilendirmek için bu veri for. istiyor Uygulama çağrıları SQLPutDataönce gelen bir sonraki çağrı parametresi veri sağlamak için SQLParamData. Tablo değerli parametresi, çağrı SQLPutDatao (Bu örnekte, her zaman 1) sürücüsü için hazırlanan satırların sayısını gösterir. Tüm satırlar tablonun değerinin sürücüye, geçirilen SQLPutData0 satır kullanılabilir olduğunu belirtmek üzere denir.

Yürütme sırasında veri değerleri bir tablo değerli satır içinde kullanmak mümkündür. Tarafından döndürülen değeri SQLParamDatahangi sürücü değeri uygulamanın gerektirdiği bildirir. Normal parametre değerleri gibi SQLPutDatakarakter veya ikili tablo değerli sütun değeri için bir veya daha fazla kez çağrılabilir. Bu parçalar halinde büyük değerleri geçirmek için bir uygulama sağlar.

Ne zaman SQLPutDatatablo-değer için adı verilir DataPtrsatır (Bu örnekte, her zaman 1) mevcut kullanılır. StrLen_or_IndPtrher zaman 0 olmalıdır. Tüm satırlar tablonun değerinin geçirilen zaman, SQLPutDataile adında bir DataPtrdeğeri 0.

Önkoşul

Bu yordam varsayar aşağıdaki Transact-SQLsunucuda yürütülmüştür:

create type TVParam as table(ProdCode integer, Qty integer)
create procedure TVPOrderEntry(@CustCode varchar(5), @Items TVPParam, 
            @OrdNo integer output, @OrdDate datetime output)
         as 
         set @OrdDate = GETDATE();
         insert into TVPOrd (OrdDate, CustCode) values (@OrdDate, @CustCode) output OrdNo); 
         select @OrdNo = SCOPE_IDENTITY(); 
         insert into TVPItem (OrdNo, ProdCode, Qty)
select @OrdNo, @Items.ProdCode, @Items.Qty 
from @Items

create type TVParam as table(ProdCode integer, Qty integer)
create procedure TVPOrderEntry(@CustCode varchar(5), @Items TVPParam, 
            @OrdNo integer output, @OrdDate datetime output)
         as 
         set @OrdDate = GETDATE();
         insert into TVPOrd (OrdDate, CustCode) values (@OrdDate, @CustCode) output OrdNo); 
         select @OrdNo = SCOPE_IDENTITY(); 
         insert into TVPItem (OrdNo, ProdCode, Qty)
select @OrdNo, @Items.ProdCode, @Items.Qty 
from @Items

Veri göndermek için

  1. sql parametreleri değişkenleri bildirin. Arabellekleri için tablo değerli parametreleri bu örnekte diziler olmak zorunda değildir; Örneğin, bir anda bir satır geçirir.

    SQLRETURN r;

// Variables for SQL parameters:
SQLCHAR CustCode[6];
SQLCHAR *TVP = (SQLCHAR *) "TVPInParam";
SQLINTEGER ProdCode, Qty;
SQLINTEGER OrdNo;
char *OrdDate[23];
SQLCHAR *TVP = (SQLCHAR *) "TVParam";
SQLINTEGER ItemNo;
// Variables for indicator/length variables associated with parameters:
SQLLEN cbCustCode, cbTVP, cbProdCode, cbQty, cbOrdNo, cbOrdDate, cbItemNo;
// Token returned by SQLParamData to indicate which param data is needed for:
SQLPOINTER ParamId;

SQLRETURN r;

// Variables for SQL parameters:
SQLCHAR CustCode[6];
SQLCHAR *TVP = (SQLCHAR *) "TVPInParam";
SQLINTEGER ProdCode, Qty;
SQLINTEGER OrdNo;
char *OrdDate[23];
SQLCHAR *TVP = (SQLCHAR *) "TVParam";
SQLINTEGER ItemNo;
// Variables for indicator/length variables associated with parameters:
SQLLEN cbCustCode, cbTVP, cbProdCode, cbQty, cbOrdNo, cbOrdDate, cbItemNo;
// Token returned by SQLParamData to indicate which param data is needed for:
SQLPOINTER ParamId;

  2. Parametre bağlama. ColumnSize1, en çok bir satır bir anda geçirilir anlam.

    // Bind parameters for call to TVPOrderEntryByRow.
r = SQLBindParameter(hstmt, 1, SQL_C_CHAR, SQL_PARAM_INPUT,SQL_VARCHAR, 5, 0, CustCode, sizeof(CustCode), &cbCustCode);

// 2 - Items TVP
r = SQLBindParameter(hstmt, 
    2,         // ParameterNumber
    SQL_C_DEFAULT,   // InputOutputType
    SQL_PARAM_INPUT,   // ValueType 
    SQL_SS_TABLE,   // Parametertype
    1,         // ColumnSize: For a table-valued parameter this the row array size.
    0,         // DecimalDigits: For a table-valued parameter this is always 0. 
    TVP,      // ParameterValuePtr: For a table-valued parameter this is the type name of the TVP,
         //      and also a token returned by SQLParamData.
    SQL_NTS,      // BufferLength: For a table-valued parameter this is the length of the type name or SQL_NTS.
    &cbTVP);      // StrLen_or_IndPtr: For a table-valued parameter this is the number of rows input and output.

// 3 - OrdNo output
r = SQLBindParameter(hstmt, 3, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &OrdNo,
    sizeof(SQLINTEGER), &cbOrdNo);
// 4- OrdDate output
r = SQLBindParameter(hstmt, 4, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_TYPE_TIMESTAMP, 23, 3, &OrdDate,
    sizeof(OrdDate), &cbOrdDate);

// Bind parameters for call to TVPOrderEntryByRow.
r = SQLBindParameter(hstmt, 1, SQL_C_CHAR, SQL_PARAM_INPUT,SQL_VARCHAR, 5, 0, CustCode, sizeof(CustCode), &cbCustCode);

// 2 - Items TVP
r = SQLBindParameter(hstmt, 
    2,         // ParameterNumber
    SQL_C_DEFAULT,   // InputOutputType
    SQL_PARAM_INPUT,   // ValueType 
    SQL_SS_TABLE,   // Parametertype
    1,         // ColumnSize: For a table-valued parameter this the row array size.
    0,         // DecimalDigits: For a table-valued parameter this is always 0. 
    TVP,      // ParameterValuePtr: For a table-valued parameter this is the type name of the TVP,
         //      and also a token returned by SQLParamData.
    SQL_NTS,      // BufferLength: For a table-valued parameter this is the length of the type name or SQL_NTS.
    &cbTVP);      // StrLen_or_IndPtr: For a table-valued parameter this is the number of rows input and output.

// 3 - OrdNo output
r = SQLBindParameter(hstmt, 3, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &OrdNo,
    sizeof(SQLINTEGER), &cbOrdNo);
// 4- OrdDate output
r = SQLBindParameter(hstmt, 4, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_TYPE_TIMESTAMP, 23, 3, &OrdDate,
    sizeof(OrdDate), &cbOrdDate);

  3. Tablo değerli parametresi sütunları bağlama.

    // Bind the table-valued parameter columns.
// First set focus on param 2
r = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 2, SQL_IS_INTEGER);

// ProdCode
r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &ProdCode,
    sizeof(SQLINTEGER), &cbProdCode);
// Qty
r = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &Qty, 
   sizeof(SQLINTEGER), &cbQty);

// Reset param focus
r = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 0, SQL_IS_INTEGER);

// Bind the table-valued parameter columns.
// First set focus on param 2
r = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 2, SQL_IS_INTEGER);

// ProdCode
r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &ProdCode,
    sizeof(SQLINTEGER), &cbProdCode);
// Qty
r = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &Qty, 
   sizeof(SQLINTEGER), &cbQty);

// Reset param focus
r = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 0, SQL_IS_INTEGER);

  4. Parametreleri tanımla. Bu örnek tablo valued parametre satır sayısı yerine, sql_data_at_exec ayarlar.

    // Initialze the TVP for row streaming.
cbTVP = SQL_DATA_AT_EXEC;

// Populate non-data-at-exec parameters.
strcpy_s((char *) CustCode ,sizeof(CustCode), "CUST1"); cbCustCode = SQL_NTS;

// Initialze the TVP for row streaming.
cbTVP = SQL_DATA_AT_EXEC;

// Populate non-data-at-exec parameters.
strcpy_s((char *) CustCode ,sizeof(CustCode), "CUST1"); cbCustCode = SQL_NTS;

  5. Yordam çağrısı. SQLExecDirectyürütme sırasında veri parametre tablo değerli bir parametre olduğu için sırasında veri sql_need_data döndürür.

    // Call the procedure
r = SQLExecDirect(hstmt, (SQLCHAR *) "{call TVPOrderEntry(?, ?, ?, ?)}",SQL_NTS);

// Call the procedure
r = SQLExecDirect(hstmt, (SQLCHAR *) "{call TVPOrderEntry(?, ?, ?, ?)}",SQL_NTS);

  6. Yürütme sırasında veri parametre veri kaynağı. Ne zaman SQLParamDatadöner ParameterValuePtrtablo değerli bir parametre için uygulama sütunlar sonraki satır veya satırları tablosu değerinin için hazırlamalısınız. Sonra Uygulama çağrıları SQLPutDataile DataPtr(Bu örnekte 1) mevcut bir satır ayarlayın ve StrLen_or_IndPtr0 olarak ayarlayın.

    // Check if parameter data is required, and get the first parameter ID token
if (r == SQL_NEED_DATA) {
    r = SQLParamData(hstmt, &ParamId);
}

// Supply parameter row data.
int rowNum = 0;
while (r == SQL_NEED_DATA) {
    if (ParamId == TVP) {
   switch (rowNum) {
       case 0: // Supply data for 1st row
      // Populate input table-valued parameter row constituent columns.
      ProdCode = 1215;   cbProdCode = sizeof(SQLINTEGER); 
      Qty = 5;      cbQty = sizeof(SQLINTEGER);
      // Returning 1 for StrLenOrIndPtr indicates that a row is available.
      r = SQLPutData(hstmt, (SQLPOINTER) 1, 1);
      rowNum++;
      break;

       case 1: // Supply data for the second row.
      // Populate another table-valued parameter row as above.
      ProdCode = 1017;   cbProdCode = sizeof(SQLINTEGER); 
      // This time supply Qty through SQLPutData.
      Qty = 0;      cbQty = SQL_DATA_AT_EXEC; 
      r = SQLPutData(hstmt, (SQLPOINTER) 1, 1);
      rowNum++;
      break;

    default:
      // Passing 0 in StrLenOrIndPtr indicates that no more table-valued parameter rows are available.
      r = SQLPutData(hstmt, (SQLPOINTER) 1, 0);
      break;
       }
    }
    else {
       if (ParamId == &Qty) {
      Qty = 2;
      // For a character or binary parameter, SQLPutData could be called
      // multiple times to pass the value in pieces.
      SQLPutData(hstmt, &Qty, sizeof(SQLINTEGER));
       }
   }
   // Signal that parameter data is available, and get the token for 
   // the next parameter.
   r = SQLParamData(hstmt, &ParamId);
    }
}

// Check if parameter data is required, and get the first parameter ID token
if (r == SQL_NEED_DATA) {
    r = SQLParamData(hstmt, &ParamId);
}

// Supply parameter row data.
int rowNum = 0;
while (r == SQL_NEED_DATA) {
    if (ParamId == TVP) {
   switch (rowNum) {
       case 0: // Supply data for 1st row
      // Populate input table-valued parameter row constituent columns.
      ProdCode = 1215;   cbProdCode = sizeof(SQLINTEGER); 
      Qty = 5;      cbQty = sizeof(SQLINTEGER);
      // Returning 1 for StrLenOrIndPtr indicates that a row is available.
      r = SQLPutData(hstmt, (SQLPOINTER) 1, 1);
      rowNum++;
      break;

       case 1: // Supply data for the second row.
      // Populate another table-valued parameter row as above.
      ProdCode = 1017;   cbProdCode = sizeof(SQLINTEGER); 
      // This time supply Qty through SQLPutData.
      Qty = 0;      cbQty = SQL_DATA_AT_EXEC; 
      r = SQLPutData(hstmt, (SQLPOINTER) 1, 1);
      rowNum++;
      break;

    default:
      // Passing 0 in StrLenOrIndPtr indicates that no more table-valued parameter rows are available.
      r = SQLPutData(hstmt, (SQLPOINTER) 1, 0);
      break;
       }
    }
    else {
       if (ParamId == &Qty) {
      Qty = 2;
      // For a character or binary parameter, SQLPutData could be called
      // multiple times to pass the value in pieces.
      SQLPutData(hstmt, &Qty, sizeof(SQLINTEGER));
       }
   }
   // Signal that parameter data is available, and get the token for 
   // the next parameter.
   r = SQLParamData(hstmt, &ParamId);
    }
}

Örnek

Açıklama

Bu örnek, akış, çağrı başına bir satır satır kullanabileceğiniz gösterir SQLPutData, benzer nasıl BCP.exe bir veritabanına veri yüklemek için kullanım için odbc tvp ile.

Örnek oluşturmadan önce bağlantı dizesi sunucu adı değiştirin.

Bu örnek, varsayılan veritabanını kullanır. Bu örnek çalıştırmadan önce kullanacağınız veritabanında aşağıdaki komutları çalıştırın:

create table MCLOG (
   biSeqNo bigint, 
   iSeries int, 
   bmRestData varbinary(max)
)
go

-- Table type definition
create type MCLOGType 
   as table(biSeqNo bigint, iSeries int, bmRestData varbinary(max) )
go

-- Insert procedure
create procedure MCLOGInsert (@TableVariable MCLOGType READONLY)
   as
   insert into MCLog(biSeqNo,  iSeries, bmRestData) 
   select biSeqNo, iSeries, bmRestData from @TableVariable  
go

create table MCLOG (
   biSeqNo bigint, 
   iSeries int, 
   bmRestData varbinary(max)
)
go

-- Table type definition
create type MCLOGType 
   as table(biSeqNo bigint, iSeries int, bmRestData varbinary(max) )
go

-- Insert procedure
create procedure MCLOGInsert (@TableVariable MCLOGType READONLY)
   as
   insert into MCLog(biSeqNo,  iSeries, bmRestData) 
   select biSeqNo, iSeries, bmRestData from @TableVariable  
go

Kodu

#define UNICODE
#define _UNICODE
#define _SQLNCLI_ODBC_

#include <windows.h>
#include <tchar.h>
#include <sqlext.h>
#include "sqlncli.h"

// link to sqlncli10.lib

#define SUCCESS(x) ( \
   !((x) & 0xFFFE) \
   )

#define CHKRC(stmt) { \
   rc = (stmt); \
   if (!SUCCESS(rc)) { \
      _tprintf(_T(#stmt) _T(" failed with rc = %ld\r\n"), rc); \
      goto EXIT; \
   } \
};

void PrintError(SQLSMALLINT HandleType, SQLHANDLE Handle) {
   RETCODE rc = SQL_SUCCESS;
   SQLTCHAR szSqlState[6];
   SQLTCHAR szMessage[1024];
   SQLSMALLINT i = 1;
   SQLSMALLINT msgLen = 0;
   SQLINTEGER NativeError;

   i = 1;
   while ( (rc = SQLGetDiagRec(HandleType, Handle, i, szSqlState, &NativeError, szMessage, sizeof(szMessage)/sizeof(SQLTCHAR), &msgLen)) != SQL_NO_DATA) {
      if (!SUCCESS(rc))
         break;
      szMessage[msgLen] = 0;
      szSqlState[5] = 0;
      _tprintf(_T("SQLState=%s, NativeError=%ld, Message=%s\r\n"), szSqlState, NativeError, szMessage);
      i++;
   }
}

int main() {
   RETCODE rc = SQL_SUCCESS;
   HENV henv = SQL_NULL_HENV;
   HDBC hdbc = SQL_NULL_HDBC;
   SQLHSTMT hstmt = SQL_NULL_HSTMT;
   SQLTCHAR * pszConnection = _T("DRIVER={SQL Server Native Client 10.0};Server=your_servername;Trusted_Connection=Yes;");

   // insert one TVP parameter
   SQLTCHAR * pszInsertStmt = _T("{call MCLOGInsert(?)}");
   SQLLEN cbParamLength;
   SQLULEN cMaxRows = 3;

   CHKRC(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HENV, &henv));
   CHKRC(SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0));
   CHKRC(SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc));
   CHKRC(SQLSetConnectAttr(hdbc, SQL_ATTR_LOGIN_TIMEOUT,reinterpret_cast<SQLPOINTER>(60),SQL_IS_UINTEGER));
   CHKRC(SQLDriverConnect(hdbc, NULL, pszConnection, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT));
   CHKRC(SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt));
   CHKRC(SQLPrepare(hstmt, pszInsertStmt, SQL_NTS));

   // Bind the first parameter
   CHKRC(SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_DEFAULT, SQL_SS_TABLE, cMaxRows, 0, (SQLPOINTER)1, 0, &cbParamLength));
   // If the stored procedure is executed as T-SQL ("exec sp_insert ?, ?"), you will supply the type name.
   // CHKRC(SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_DEFAULT, SQL_SS_TABLE, cMaxRows, 0, (SQLPOINTER)lpszTVPParamType, SQL_NTS, &cbParamLengths));

   // bind TVP columns
   CHKRC(SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER)1, SQL_IS_INTEGER));

   // for each TVP column, you can define an array to send more than one row for each SQLPutData call.
   LONGLONG llSeqNo;
   SQLLEN cbSeqNo = sizeof(LONGLONG);
   LONG lSeries;
   SQLLEN cbSeries = sizeof(LONG);
   BYTE rgbRestData[2048];
   SQLLEN cbRestData = SQL_DATA_AT_EXEC;
   SQLUSMALLINT iColumn = 1;

   // Bind biSeqNo 
   CHKRC(SQLBindParameter(hstmt, iColumn, SQL_PARAM_INPUT, SQL_C_SBIGINT, SQL_BIGINT, sizeof(LONGLONG), 0, (SQLPOINTER)&llSeqNo, sizeof(llSeqNo), &cbSeqNo));

   // Bind iSeries 
   iColumn++;
   CHKRC(SQLBindParameter(hstmt, iColumn, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, sizeof(LONG), 0, (SQLPOINTER)&lSeries, sizeof(lSeries), &cbSeries));

   // Bind bmRestData 
   iColumn++;
   CHKRC(SQLBindParameter(hstmt, iColumn, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_VARBINARY, 0, 0, (SQLPOINTER)rgbRestData, 0, &cbRestData));
   CHKRC(SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER)0, SQL_IS_INTEGER));

   // Set cbParamLength to SQL_DATA_AT_EXEC to indicate the TVP parameter is bound as DAE.
   cbParamLength = SQL_DATA_AT_EXEC;
   rc = SQLExecute(hstmt);

   if (rc == SQL_NEED_DATA) {
      SQLPOINTER ptr = NULL;
      SQLULEN cRows = 0;

      rc = ::SQLParamData(hstmt, &ptr);

      while (rc == SQL_NEED_DATA) {
         if (ptr == (SQLPOINTER)1) {
            // it is the TVP parameter
            if (cRows == cMaxRows) {
               // We finish sending the last row already.
               CHKRC(::SQLPutData(hstmt, NULL, 0));
            }
            else {
               // StrLen_or_IndPtr can be changed to SQL_DATA_AT_EXEC or to a byte length before sending
               // the actual TVP rows. SQL_DATA_AT_EXEC means send DAE data.
               llSeqNo = cRows;
               cbSeqNo = sizeof(LONGLONG);   // send as bound TVP column
               lSeries = cRows + 100;
               cbSeries = sizeof(LONG);   // send as bound TVP column
               cbRestData = SQL_DATA_AT_EXEC;   // send as DAE TVP column
               CHKRC(::SQLPutData(hstmt, (SQLPOINTER)1, 1));
               cRows++;
            }
         }
         else if (ptr == (SQLPOINTER)rgbRestData)
            // varbinary(max) column.  Send data in parts.
            for ( int i = 0 ; i < 3 ; i++ ) {
               // Obtain the data in part from somewhere, here we just set all bytes to 'a'.
               ::memset(rgbRestData, 'a', sizeof(rgbRestData));
               CHKRC(::SQLPutData(hstmt, (SQLPOINTER)rgbRestData, sizeof(rgbRestData)));
            }
         else 
            // handling other DAE parameters, but in our case, we don't have other DAE parameters.
            goto EXIT;
         rc = ::SQLParamData(hstmt, &ptr);
      }
   }

   if (hstmt)
      SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
   if (hdbc) {
      SQLDisconnect(hdbc);
      SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
   }
   if (henv)
      SQLFreeHandle(SQL_HANDLE_ENV, henv);

EXIT:
   if (!SUCCESS(rc)) {
      if (hstmt)
         PrintError(SQL_HANDLE_STMT, hstmt);
      if (hdbc)
         PrintError(SQL_HANDLE_DBC, hdbc);
      if(henv)
         PrintError(SQL_HANDLE_ENV, henv);
   }
}

#define UNICODE
#define _UNICODE
#define _SQLNCLI_ODBC_

#include <windows.h>
#include <tchar.h>
#include <sqlext.h>
#include "sqlncli.h"

// link to sqlncli10.lib

#define SUCCESS(x) ( \
   !((x) & 0xFFFE) \
   )

#define CHKRC(stmt) { \
   rc = (stmt); \
   if (!SUCCESS(rc)) { \
      _tprintf(_T(#stmt) _T(" failed with rc = %ld\r\n"), rc); \
      goto EXIT; \
   } \
};

void PrintError(SQLSMALLINT HandleType, SQLHANDLE Handle) {
   RETCODE rc = SQL_SUCCESS;
   SQLTCHAR szSqlState[6];
   SQLTCHAR szMessage[1024];
   SQLSMALLINT i = 1;
   SQLSMALLINT msgLen = 0;
   SQLINTEGER NativeError;

   i = 1;
   while ( (rc = SQLGetDiagRec(HandleType, Handle, i, szSqlState, &NativeError, szMessage, sizeof(szMessage)/sizeof(SQLTCHAR), &msgLen)) != SQL_NO_DATA) {
      if (!SUCCESS(rc))
         break;
      szMessage[msgLen] = 0;
      szSqlState[5] = 0;
      _tprintf(_T("SQLState=%s, NativeError=%ld, Message=%s\r\n"), szSqlState, NativeError, szMessage);
      i++;
   }
}

int main() {
   RETCODE rc = SQL_SUCCESS;
   HENV henv = SQL_NULL_HENV;
   HDBC hdbc = SQL_NULL_HDBC;
   SQLHSTMT hstmt = SQL_NULL_HSTMT;
   SQLTCHAR * pszConnection = _T("DRIVER={SQL Server Native Client 10.0};Server=your_servername;Trusted_Connection=Yes;");

   // insert one TVP parameter
   SQLTCHAR * pszInsertStmt = _T("{call MCLOGInsert(?)}");
   SQLLEN cbParamLength;
   SQLULEN cMaxRows = 3;

   CHKRC(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HENV, &henv));
   CHKRC(SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0));
   CHKRC(SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc));
   CHKRC(SQLSetConnectAttr(hdbc, SQL_ATTR_LOGIN_TIMEOUT,reinterpret_cast<SQLPOINTER>(60),SQL_IS_UINTEGER));
   CHKRC(SQLDriverConnect(hdbc, NULL, pszConnection, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT));
   CHKRC(SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt));
   CHKRC(SQLPrepare(hstmt, pszInsertStmt, SQL_NTS));

   // Bind the first parameter
   CHKRC(SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_DEFAULT, SQL_SS_TABLE, cMaxRows, 0, (SQLPOINTER)1, 0, &cbParamLength));
   // If the stored procedure is executed as T-SQL ("exec sp_insert ?, ?"), you will supply the type name.
   // CHKRC(SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_DEFAULT, SQL_SS_TABLE, cMaxRows, 0, (SQLPOINTER)lpszTVPParamType, SQL_NTS, &cbParamLengths));

   // bind TVP columns
   CHKRC(SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER)1, SQL_IS_INTEGER));

   // for each TVP column, you can define an array to send more than one row for each SQLPutData call.
   LONGLONG llSeqNo;
   SQLLEN cbSeqNo = sizeof(LONGLONG);
   LONG lSeries;
   SQLLEN cbSeries = sizeof(LONG);
   BYTE rgbRestData[2048];
   SQLLEN cbRestData = SQL_DATA_AT_EXEC;
   SQLUSMALLINT iColumn = 1;

   // Bind biSeqNo 
   CHKRC(SQLBindParameter(hstmt, iColumn, SQL_PARAM_INPUT, SQL_C_SBIGINT, SQL_BIGINT, sizeof(LONGLONG), 0, (SQLPOINTER)&llSeqNo, sizeof(llSeqNo), &cbSeqNo));

   // Bind iSeries 
   iColumn++;
   CHKRC(SQLBindParameter(hstmt, iColumn, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, sizeof(LONG), 0, (SQLPOINTER)&lSeries, sizeof(lSeries), &cbSeries));

   // Bind bmRestData 
   iColumn++;
   CHKRC(SQLBindParameter(hstmt, iColumn, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_VARBINARY, 0, 0, (SQLPOINTER)rgbRestData, 0, &cbRestData));
   CHKRC(SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER)0, SQL_IS_INTEGER));

   // Set cbParamLength to SQL_DATA_AT_EXEC to indicate the TVP parameter is bound as DAE.
   cbParamLength = SQL_DATA_AT_EXEC;
   rc = SQLExecute(hstmt);

   if (rc == SQL_NEED_DATA) {
      SQLPOINTER ptr = NULL;
      SQLULEN cRows = 0;

      rc = ::SQLParamData(hstmt, &ptr);

      while (rc == SQL_NEED_DATA) {
         if (ptr == (SQLPOINTER)1) {
            // it is the TVP parameter
            if (cRows == cMaxRows) {
               // We finish sending the last row already.
               CHKRC(::SQLPutData(hstmt, NULL, 0));
            }
            else {
               // StrLen_or_IndPtr can be changed to SQL_DATA_AT_EXEC or to a byte length before sending
               // the actual TVP rows. SQL_DATA_AT_EXEC means send DAE data.
               llSeqNo = cRows;
               cbSeqNo = sizeof(LONGLONG);   // send as bound TVP column
               lSeries = cRows + 100;
               cbSeries = sizeof(LONG);   // send as bound TVP column
               cbRestData = SQL_DATA_AT_EXEC;   // send as DAE TVP column
               CHKRC(::SQLPutData(hstmt, (SQLPOINTER)1, 1));
               cRows++;
            }
         }
         else if (ptr == (SQLPOINTER)rgbRestData)
            // varbinary(max) column.  Send data in parts.
            for ( int i = 0 ; i < 3 ; i++ ) {
               // Obtain the data in part from somewhere, here we just set all bytes to 'a'.
               ::memset(rgbRestData, 'a', sizeof(rgbRestData));
               CHKRC(::SQLPutData(hstmt, (SQLPOINTER)rgbRestData, sizeof(rgbRestData)));
            }
         else 
            // handling other DAE parameters, but in our case, we don't have other DAE parameters.
            goto EXIT;
         rc = ::SQLParamData(hstmt, &ptr);
      }
   }

   if (hstmt)
      SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
   if (hdbc) {
      SQLDisconnect(hdbc);
      SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
   }
   if (henv)
      SQLFreeHandle(SQL_HANDLE_ENV, henv);

EXIT:
   if (!SUCCESS(rc)) {
      if (hstmt)
         PrintError(SQL_HANDLE_STMT, hstmt);
      if (hdbc)
         PrintError(SQL_HANDLE_DBC, hdbc);
      if(henv)
         PrintError(SQL_HANDLE_ENV, henv);
   }
}

Örnek

Açıklama

Bu örnek, akış, çağrı başına birden çok satır satır kullanabileceğiniz gösterir SQLPutData, benzer nasıl BCP.exe bir veritabanına veri yüklemek için kullanım için odbc tvp ile.

Örnek oluşturmadan önce bağlantı dizesi sunucu adı değiştirin.

Bu örnek, varsayılan veritabanını kullanır. Bu örnek çalıştırmadan önce kullanacağınız veritabanında aşağıdaki komutları çalıştırın:

create table MCLOG (
   biSeqNo bigint, 
   iSeries int, 
   bmRestData varbinary(max)
)
go

-- Table type definition
create type MCLOGType 
   as table(biSeqNo bigint, iSeries int, bmRestData varbinary(max) )
go

-- Insert procedure
create procedure MCLOGInsert (@TableVariable MCLOGType READONLY)
   as
   insert into MCLog(biSeqNo,  iSeries, bmRestData) 
   select biSeqNo, iSeries, bmRestData from @TableVariable  
go

create table MCLOG (
   biSeqNo bigint, 
   iSeries int, 
   bmRestData varbinary(max)
)
go

-- Table type definition
create type MCLOGType 
   as table(biSeqNo bigint, iSeries int, bmRestData varbinary(max) )
go

-- Insert procedure
create procedure MCLOGInsert (@TableVariable MCLOGType READONLY)
   as
   insert into MCLog(biSeqNo,  iSeries, bmRestData) 
   select biSeqNo, iSeries, bmRestData from @TableVariable  
go

Kodu

#define UNICODE
#define _UNICODE
#define _SQLNCLI_ODBC_

#include <windows.h>
#include <tchar.h>
#include <sqlext.h>
#include "sqlncli.h"

// link to sqlncli10.lib

#define SUCCESS(x) ( \
   !((x) & 0xFFFE) \
   )

#define CHKRC(stmt) { \
   rc = (stmt); \
   if (!SUCCESS(rc)) { \
      _tprintf(_T(#stmt) _T(" failed with rc = %ld\r\n"), rc); \
      goto EXIT; \
   } \
};

void PrintError(SQLSMALLINT HandleType, SQLHANDLE Handle) {
   RETCODE rc = SQL_SUCCESS;
   SQLTCHAR szSqlState[6];
   SQLTCHAR szMessage[1024];
   SQLSMALLINT i = 1;
   SQLSMALLINT msgLen = 0;
   SQLINTEGER NativeError;

   i = 1;
   while ( (rc = SQLGetDiagRec(HandleType, Handle, i, szSqlState, &NativeError, szMessage, sizeof(szMessage)/sizeof(SQLTCHAR), &msgLen)) != SQL_NO_DATA) {
      if (!SUCCESS(rc))
         break;
      szMessage[msgLen] = 0;
      szSqlState[5] = 0;
      _tprintf(_T("SQLState=%s, NativeError=%ld, Message=%s\r\n"), szSqlState, NativeError, szMessage);
      i++;
   }
}

int main() {
   RETCODE rc = SQL_SUCCESS;
   HENV henv = SQL_NULL_HENV;
   HDBC hdbc = SQL_NULL_HDBC;
   SQLHSTMT hstmt = SQL_NULL_HSTMT;
   SQLTCHAR * pszConnection = _T("DRIVER={SQL Server Native Client 10.0};Server=MyServer;Trusted_Connection=Yes;");

   // insert one TVP parameter
   SQLTCHAR * pszInsertStmt = _T("{call MCLOGInsert(?)}");
   SQLLEN cbParamLength;
   SQLULEN cMaxRows = 9;

   CHKRC(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HENV, &henv));
   CHKRC(SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0));

   CHKRC(SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc));
   CHKRC(SQLSetConnectAttr( hdbc, SQL_ATTR_LOGIN_TIMEOUT, reinterpret_cast<SQLPOINTER>(60), SQL_IS_UINTEGER));
   CHKRC(SQLDriverConnect( hdbc, NULL, pszConnection, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT)); 
   CHKRC(SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt));
   CHKRC(SQLPrepare(hstmt, pszInsertStmt, SQL_NTS));

   // Bind the first parameter
   CHKRC(SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_DEFAULT, SQL_SS_TABLE, cMaxRows, 0, (SQLPOINTER)1, 0, &cbParamLength));

   /*
   // If the stored procedure is executed as T-SQL ("exec sp_insert ?, ?"), then, supply the type name.
   CHKRC(SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_DEFAULT, SQL_SS_TABLE, cMaxRows, 0, (SQLPOINTER)lpszTVPParamType, SQL_NTS, &cbParamLengths));
   */

   // bind TVP columns.
   CHKRC(SQLSetStmtAttr( hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER)1, SQL_IS_INTEGER)); 

   // For the first and the second TVP columns (bigint, int), always send them as bound. 
   // For the third column varbinary(max), either send them as bound or DAE.
   const size_t ARRAY_SIZE = 3;
   LONGLONG llSeqNo[ARRAY_SIZE];
   SQLLEN cbSeqNo[ARRAY_SIZE] = {sizeof(LONGLONG), sizeof(LONGLONG), sizeof(LONGLONG)};
   LONG lSeries[ARRAY_SIZE];
   SQLLEN cbSeries[ARRAY_SIZE] = {sizeof(LONG), sizeof(LONG), sizeof(LONG)};
   BYTE rgbRestData[ARRAY_SIZE][2048];
   SQLLEN cbRestData[ARRAY_SIZE] = {sizeof(rgbRestData[0]), sizeof(rgbRestData[0]), sizeof(rgbRestData[0])};
   SQLUSMALLINT iColumn = 1;

   // Bind biSeqNo 
   CHKRC(SQLBindParameter( hstmt, iColumn, SQL_PARAM_INPUT, SQL_C_SBIGINT, SQL_BIGINT, sizeof(LONGLONG), 0, (SQLPOINTER)&llSeqNo, sizeof(llSeqNo[0]), cbSeqNo));

   // Bind iSeries 
   iColumn++;
   CHKRC(SQLBindParameter( hstmt, iColumn, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, sizeof(LONG), 0, (SQLPOINTER)&lSeries, sizeof(lSeries[0]), cbSeries));

   // Bind bmRestData 
   iColumn++;
   CHKRC(SQLBindParameter(hstmt, iColumn, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_VARBINARY, 0, 0, (SQLPOINTER)rgbRestData, sizeof(rgbRestData[0]), cbRestData));

   CHKRC(SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER)0, SQL_IS_INTEGER));

   // Set cbParamLength to SQL_DATA_AT_EXEC to indicate the TVP parameter is bound as DAE.
   cbParamLength = SQL_DATA_AT_EXEC;
   rc = SQLExecute(hstmt);

   if (rc == SQL_NEED_DATA) {
      SQLPOINTER ptr = NULL;
      SQLUINTEGER cRows = 0;

      rc = ::SQLParamData(hstmt, &ptr);

      while (rc == SQL_NEED_DATA) {
         if (ptr == (SQLPOINTER)1) {
            // it is the TVP parameter
            if (cRows >= cMaxRows) {
               // We finish sending the last row already.
               CHKRC(::SQLPutData(hstmt, NULL, 0));
            }
            else {
               // Obtaining row data from somewhere. In this case we will fill 3 rows.
               for (size_t i = 0; i < ARRAY_SIZE; i++) {
                  llSeqNo[i] = cRows + i + 1;
                  lSeries[i] = llSeqNo[i] * 10;

                  // Now fill the varbinary(max) column.  Assume that the even row can't be fit into 
                  // the buffer provided as send them as DAE.
                  if (!((cRows + i) % 2)) {
                     // SQL_DATA_AT_EXEC means send DAE data.
                     cbRestData[i] = SQL_DATA_AT_EXEC;
                  }
                  else {
                     // data can fit into the buffer, then copy the data to the buffer directly.
                     cbRestData[i] = 100;
                     ::memset(&rgbRestData[i], 'b', cbRestData[i]);
                  }
               }
               CHKRC(::SQLPutData(hstmt, (SQLPOINTER)1, ARRAY_SIZE));
               cRows += ARRAY_SIZE;
            }
         }
         else if ((SQLPOINTER)&rgbRestData[0] <= ptr && ptr <= (SQLPOINTER)&rgbRestData[ARRAY_SIZE-1]) {
            // it is varbinary(max) column
            // Send data in parts.
            for (int i = 0; i < 3; i++) {
               // Obtain the data in part from somewhere, here we just set all bytes to 'a'.
               ::memset(ptr, 'a', sizeof(rgbRestData[0]));
               CHKRC(::SQLPutData(hstmt, (SQLPOINTER)ptr, sizeof(rgbRestData[0])));
            }
         }
         else {
            // handling other DAE parameters, but in our case, we don't have other DAE parameters.
            goto EXIT;
         }
         rc = ::SQLParamData(hstmt, &ptr);
      }
   }

EXIT:
   if (!SUCCESS(rc)) {
      if (hstmt) 
         PrintError(SQL_HANDLE_STMT, hstmt);
      if (hdbc)
         PrintError(SQL_HANDLE_DBC, hdbc);
      if(henv)
         PrintError(SQL_HANDLE_ENV, henv);
   }

   if (hstmt)
      SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
   if (hdbc) {
      SQLDisconnect(hdbc);
      SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
   }
   if (henv)
      SQLFreeHandle(SQL_HANDLE_ENV, henv);
}

#define UNICODE
#define _UNICODE
#define _SQLNCLI_ODBC_

#include <windows.h>
#include <tchar.h>
#include <sqlext.h>
#include "sqlncli.h"

// link to sqlncli10.lib

#define SUCCESS(x) ( \
   !((x) & 0xFFFE) \
   )

#define CHKRC(stmt) { \
   rc = (stmt); \
   if (!SUCCESS(rc)) { \
      _tprintf(_T(#stmt) _T(" failed with rc = %ld\r\n"), rc); \
      goto EXIT; \
   } \
};

void PrintError(SQLSMALLINT HandleType, SQLHANDLE Handle) {
   RETCODE rc = SQL_SUCCESS;
   SQLTCHAR szSqlState[6];
   SQLTCHAR szMessage[1024];
   SQLSMALLINT i = 1;
   SQLSMALLINT msgLen = 0;
   SQLINTEGER NativeError;

   i = 1;
   while ( (rc = SQLGetDiagRec(HandleType, Handle, i, szSqlState, &NativeError, szMessage, sizeof(szMessage)/sizeof(SQLTCHAR), &msgLen)) != SQL_NO_DATA) {
      if (!SUCCESS(rc))
         break;
      szMessage[msgLen] = 0;
      szSqlState[5] = 0;
      _tprintf(_T("SQLState=%s, NativeError=%ld, Message=%s\r\n"), szSqlState, NativeError, szMessage);
      i++;
   }
}

int main() {
   RETCODE rc = SQL_SUCCESS;
   HENV henv = SQL_NULL_HENV;
   HDBC hdbc = SQL_NULL_HDBC;
   SQLHSTMT hstmt = SQL_NULL_HSTMT;
   SQLTCHAR * pszConnection = _T("DRIVER={SQL Server Native Client 10.0};Server=MyServer;Trusted_Connection=Yes;");

   // insert one TVP parameter
   SQLTCHAR * pszInsertStmt = _T("{call MCLOGInsert(?)}");
   SQLLEN cbParamLength;
   SQLULEN cMaxRows = 9;

   CHKRC(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HENV, &henv));
   CHKRC(SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0));

   CHKRC(SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc));
   CHKRC(SQLSetConnectAttr( hdbc, SQL_ATTR_LOGIN_TIMEOUT, reinterpret_cast<SQLPOINTER>(60), SQL_IS_UINTEGER));
   CHKRC(SQLDriverConnect( hdbc, NULL, pszConnection, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT)); 
   CHKRC(SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt));
   CHKRC(SQLPrepare(hstmt, pszInsertStmt, SQL_NTS));

   // Bind the first parameter
   CHKRC(SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_DEFAULT, SQL_SS_TABLE, cMaxRows, 0, (SQLPOINTER)1, 0, &cbParamLength));

   /*
   // If the stored procedure is executed as T-SQL ("exec sp_insert ?, ?"), then, supply the type name.
   CHKRC(SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_DEFAULT, SQL_SS_TABLE, cMaxRows, 0, (SQLPOINTER)lpszTVPParamType, SQL_NTS, &cbParamLengths));
   */

   // bind TVP columns.
   CHKRC(SQLSetStmtAttr( hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER)1, SQL_IS_INTEGER)); 

   // For the first and the second TVP columns (bigint, int), always send them as bound. 
   // For the third column varbinary(max), either send them as bound or DAE.
   const size_t ARRAY_SIZE = 3;
   LONGLONG llSeqNo[ARRAY_SIZE];
   SQLLEN cbSeqNo[ARRAY_SIZE] = {sizeof(LONGLONG), sizeof(LONGLONG), sizeof(LONGLONG)};
   LONG lSeries[ARRAY_SIZE];
   SQLLEN cbSeries[ARRAY_SIZE] = {sizeof(LONG), sizeof(LONG), sizeof(LONG)};
   BYTE rgbRestData[ARRAY_SIZE][2048];
   SQLLEN cbRestData[ARRAY_SIZE] = {sizeof(rgbRestData[0]), sizeof(rgbRestData[0]), sizeof(rgbRestData[0])};
   SQLUSMALLINT iColumn = 1;

   // Bind biSeqNo 
   CHKRC(SQLBindParameter( hstmt, iColumn, SQL_PARAM_INPUT, SQL_C_SBIGINT, SQL_BIGINT, sizeof(LONGLONG), 0, (SQLPOINTER)&llSeqNo, sizeof(llSeqNo[0]), cbSeqNo));

   // Bind iSeries 
   iColumn++;
   CHKRC(SQLBindParameter( hstmt, iColumn, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, sizeof(LONG), 0, (SQLPOINTER)&lSeries, sizeof(lSeries[0]), cbSeries));

   // Bind bmRestData 
   iColumn++;
   CHKRC(SQLBindParameter(hstmt, iColumn, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_VARBINARY, 0, 0, (SQLPOINTER)rgbRestData, sizeof(rgbRestData[0]), cbRestData));

   CHKRC(SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER)0, SQL_IS_INTEGER));

   // Set cbParamLength to SQL_DATA_AT_EXEC to indicate the TVP parameter is bound as DAE.
   cbParamLength = SQL_DATA_AT_EXEC;
   rc = SQLExecute(hstmt);

   if (rc == SQL_NEED_DATA) {
      SQLPOINTER ptr = NULL;
      SQLUINTEGER cRows = 0;

      rc = ::SQLParamData(hstmt, &ptr);

      while (rc == SQL_NEED_DATA) {
         if (ptr == (SQLPOINTER)1) {
            // it is the TVP parameter
            if (cRows >= cMaxRows) {
               // We finish sending the last row already.
               CHKRC(::SQLPutData(hstmt, NULL, 0));
            }
            else {
               // Obtaining row data from somewhere. In this case we will fill 3 rows.
               for (size_t i = 0; i < ARRAY_SIZE; i++) {
                  llSeqNo[i] = cRows + i + 1;
                  lSeries[i] = llSeqNo[i] * 10;

                  // Now fill the varbinary(max) column.  Assume that the even row can't be fit into 
                  // the buffer provided as send them as DAE.
                  if (!((cRows + i) % 2)) {
                     // SQL_DATA_AT_EXEC means send DAE data.
                     cbRestData[i] = SQL_DATA_AT_EXEC;
                  }
                  else {
                     // data can fit into the buffer, then copy the data to the buffer directly.
                     cbRestData[i] = 100;
                     ::memset(&rgbRestData[i], 'b', cbRestData[i]);
                  }
               }
               CHKRC(::SQLPutData(hstmt, (SQLPOINTER)1, ARRAY_SIZE));
               cRows += ARRAY_SIZE;
            }
         }
         else if ((SQLPOINTER)&rgbRestData[0] <= ptr && ptr <= (SQLPOINTER)&rgbRestData[ARRAY_SIZE-1]) {
            // it is varbinary(max) column
            // Send data in parts.
            for (int i = 0; i < 3; i++) {
               // Obtain the data in part from somewhere, here we just set all bytes to 'a'.
               ::memset(ptr, 'a', sizeof(rgbRestData[0]));
               CHKRC(::SQLPutData(hstmt, (SQLPOINTER)ptr, sizeof(rgbRestData[0])));
            }
         }
         else {
            // handling other DAE parameters, but in our case, we don't have other DAE parameters.
            goto EXIT;
         }
         rc = ::SQLParamData(hstmt, &ptr);
      }
   }

EXIT:
   if (!SUCCESS(rc)) {
      if (hstmt) 
         PrintError(SQL_HANDLE_STMT, hstmt);
      if (hdbc)
         PrintError(SQL_HANDLE_DBC, hdbc);
      if(henv)
         PrintError(SQL_HANDLE_ENV, henv);
   }

   if (hstmt)
      SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
   if (hdbc) {
      SQLDisconnect(hdbc);
      SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
   }
   if (henv)
      SQLFreeHandle(SQL_HANDLE_ENV, henv);
}

Ayrıca bkz.

Kavramlar

odbc tablo Valued parametre programlama örnekleri