使用資料表值參數 (ODBC)

發行項
05/07/2013
此範例示範如何使用資料表值參數插入多個資料列、搭配多個資料行使用資料表值參數、搭配一個伺服器的呼叫使用資料表值參數。
如需有關資料表值參數的詳細資訊，請參閱＜資料表值參數 (ODBC)＞。如需使用資料表值參數的其他範例，請參閱＜ODBC 資料表值參數程式設計範例＞。
範例

您將需要稱為 TVPDemo 的 ODBC 資料來源。 TVPDemo 的預設資料庫可以是您電腦上的任何測試資料庫。對於 SQL Server Native Client，此資料來源必須以 ODBC 驅動程式為基礎。
如果您要建立並執行此範例，當做 64 位元作業系統上的 32 位元應用程式，您必須利用 %windir%\SysWOW64\odbcad32.exe，以 ODBC 管理員身分建立 ODBC 資料來源。
這個範例會連接到電腦的預設 SQL Server 執行個體。若要連接到具名執行個體，請變更 ODBC 資料來源的定義，以便使用下列格式指定執行個體：server\namedinstance。根據預設，SQL Server Express 會安裝至具名執行個體。
使用 odbc32.lib 和 user32.lib 編譯 (C++) 程式碼清單。請確認您的 INCLUDE 環境變數包含的目錄內含 sqlncli.h。
// compile with: odbc32.lib user32.lib
#pragma once
#define WIN32_LEAN_AND_MEAN   // Exclude rarely-used stuff from Windows headers
#include <stdio.h>
#include <stdlib.h>
#include <tchar.h>
#include <windows.h>
#include "sql.h"
#include "sqlext.h"
#include "sqlncli.h"

// cardinality of order item related array variables
#define ITEM_ARRAY_SIZE 20

// struct to pass order entry data
typedef struct OrdEntry_struct {
   SQLINTEGER OrdNo;
   SQLTCHAR OrdDate[24];
   SQLTCHAR CustCode[6];
   SQLUINTEGER ItemCount;
   SQLINTEGER ProdCode[ITEM_ARRAY_SIZE];
   SQLINTEGER Qty[ITEM_ARRAY_SIZE];
} OrdEntryData;

SQLHANDLE henv, hdbc, hstmt;

void ODBCError(SQLHANDLE henv, SQLHANDLE hdbc, SQLHANDLE hstmt, SQLHANDLE hdesc, bool ShowError) {
   SQLRETURN r = 0;
   SQLTCHAR szSqlState[6] = {0};
   SQLINTEGER fNativeError = 0;
   SQLTCHAR szErrorMsg[256] = {0};
   SQLSMALLINT cbErrorMsgMax = sizeof(szErrorMsg) - 1;
   SQLSMALLINT cbErrorMsg = 0;
   TCHAR text[1024] = {0}, title[256] = {0};

   if (hdesc != NULL)
      r = SQLGetDiagRec(SQL_HANDLE_DESC, hdesc, 1, szSqlState, &fNativeError, szErrorMsg, cbErrorMsgMax, &cbErrorMsg);
   else {
      if (hstmt != NULL)
         r = SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, szSqlState, &fNativeError, szErrorMsg, cbErrorMsgMax, &cbErrorMsg);
      else {
         if (hdbc != NULL)
            r = SQLGetDiagRec(SQL_HANDLE_DBC, hdbc, 1, szSqlState, &fNativeError, szErrorMsg, cbErrorMsgMax, &cbErrorMsg);
         else
            r = SQLGetDiagRec(SQL_HANDLE_ENV, henv, 1, szSqlState, &fNativeError, szErrorMsg, cbErrorMsgMax, &cbErrorMsg);
      }
   }

   if (ShowError) {
      _sntprintf_s(title, _countof(title), _TRUNCATE, _T("ODBC Error %i"), fNativeError);
      _sntprintf_s(text, _countof(text), _TRUNCATE, _T("[%s] - %s"), szSqlState, szErrorMsg);

      MessageBox(NULL, (LPCTSTR) text, (LPCTSTR) _T("ODBC Error"), MB_OK);
   }
}

void connect() {
   SQLRETURN r;

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

   // This is an ODBC v3 application
   r = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, NULL, NULL, NULL, true);
      exit(-1);
   }

   r = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

   // Run in ANSI/implicit transaction mode
   r = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) SQL_AUTOCOMMIT_OFF, SQL_IS_INTEGER);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, NULL, NULL, NULL, true);
      exit(-1);
   }

   TCHAR szConnStrIn[256] = _T("DSN=TVPDemo");

   r = SQLDriverConnect(hdbc, NULL, (SQLTCHAR *) szConnStrIn, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, NULL, NULL, true);
      exit(-1);
   }
}

void setup_ODBC_basics() {
   SQLRETURN r;

   r = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true);
      exit(-1);
   }
}

void setup_TVP_demo () {
   SQLRETURN r;
   // Drop prior versions of table and procedure
   r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("drop procedure TVPOrderEntry"), SQL_NTS);
   r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("drop procedure TVPOrderInsert"), SQL_NTS);
   r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("drop procedure TVPItemInsert"), SQL_NTS);
   r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("drop type TVPParam"), SQL_NTS);
   r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("drop table TVPOrd"), SQL_NTS);
   r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("drop table TVPItem"), SQL_NTS);

   // Create tables
   r = SQLExecDirect(hstmt, 
      (SQLTCHAR *) _T("create table TVPOrd( OrdNo integer identity(1,1), OrdDate datetime, CustCode varchar(5))"), SQL_NTS);

   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true);
      exit(-1);
   }

   r = SQLExecDirect(hstmt, 
      (SQLTCHAR *) _T("create table TVPItem( OrdNo integer, ItemNo integer identity(1,1), ProdCode integer, Qty integer)"), SQL_NTS);

   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Create TABLE type for use as a TVP
   r = SQLExecDirect(hstmt, 
      (SQLTCHAR *) _T("create type TVPParam as table(ProdCode integer, Qty integer)"), SQL_NTS);

   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Create procedure for TVPOrd insert 
   r = SQLExecDirect(hstmt, (SQLTCHAR *) 
      _T("create procedure TVPOrderInsert(@CustCode varchar(5), \
         @OrdNo integer output, @OrdDate datetime output)\
         as \
         set @OrdDate = GETDATE();\
         insert into TVPOrd (OrdDate, CustCode) values (@OrdDate, @CustCode); \
         select @OrdNo = SCOPE_IDENTITY()"), SQL_NTS);

   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Create procedure for TVPItem insert 
   r = SQLExecDirect(hstmt, 
      (SQLTCHAR *) _T("create procedure TVPItemInsert(@OrdNo integer, \
                      @ProdCode integer, @Qty integer)\
                      as \
                      insert into TVPItem (OrdNo, ProdCode, Qty) \
                      values (@OrdNo, @ProdCode, @Qty)"), SQL_NTS);

   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Create procedure with TVP parameters 
   r = SQLExecDirect(hstmt, 
      (SQLTCHAR *) _T("create procedure TVPOrderEntry(@CustCode varchar(5), @Items TVPParam READONLY, \
                      @OrdNo integer output, @OrdDate datetime output)\
                      as \
                      set @OrdDate = GETDATE();\
                      insert into TVPOrd (OrdDate, CustCode) values (@OrdDate, @CustCode); \
                      select @OrdNo = SCOPE_IDENTITY(); \
                      insert into TVPItem (OrdNo, ProdCode, Qty) \
                      select @OrdNo, ProdCode, Qty from @Items"), SQL_NTS);

   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   r = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }
}

void OrdEntry_Simple (OrdEntryData& order) {
   // Simple order entry
   SQLRETURN r;

   SQLINTEGER ProdCode, Qty;

   // Bind parameters for the Order
   // 1 - Custcode input
   r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,SQL_C_TCHAR, SQL_VARCHAR, 5, 0, &order.CustCode, sizeof(order.CustCode), NULL);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // 2 - OrdNo output
   r = SQLBindParameter(hstmt, 2, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &order.OrdNo, sizeof(SQLINTEGER), NULL);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // 3- OrdDate output
   r = SQLBindParameter(hstmt, 3, SQL_PARAM_OUTPUT, SQL_C_TCHAR, SQL_TYPE_TIMESTAMP, 23, 3, order.OrdDate, sizeof(order.OrdDate), NULL);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Insert the order
   r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("{call TVPOrderInsert(?, ?, ?)}"),SQL_NTS);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Flush results & reset hstmt
   r = SQLMoreResults(hstmt);
   if (r != SQL_NO_DATA) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   r = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Bind parameters for the Items
   // 1 - OrdNo 
   r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 10, 0, &order.OrdNo, sizeof(SQLINTEGER), NULL);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // 2 - ProdCode
   r = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 10, 0, &ProdCode, sizeof(SQLINTEGER), NULL);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // 3 - Qty
   r = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 10, 0, &Qty, sizeof(SQLINTEGER), NULL);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Insert items one at a time
   r = SQLPrepare(hstmt, (SQLTCHAR *) _T("{call TVPItemInsert(?, ?, ?)}"),SQL_NTS);

   for (unsigned int i = 0; i < order.ItemCount; i++) {
      ProdCode = order.ProdCode[i];
      Qty = order.Qty[i];
      r = SQLExecute(hstmt);
      if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
         ODBCError(henv, hdbc, hstmt, NULL, true); 
         exit(-1);
      }
   }

   // Flush results & reset hstmt
   r = SQLMoreResults(hstmt);
   if (r != SQL_NO_DATA) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   r = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Commit the transaction
   r = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }
}

void OrdEntry_PA (OrdEntryData& order){
   // best practice (not using TVPs) using a parameter array
   SQLRETURN r;

   // Array if OrdNo for use with array insert of Items
   SQLINTEGER OrdNo[ITEM_ARRAY_SIZE];

   // Bind parameters for the Order
   // 1 - Custcode input
   r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,SQL_C_TCHAR, SQL_VARCHAR, 5, 0, &order.CustCode, sizeof(order.CustCode), NULL);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // 2 - OrdNo output
   r = SQLBindParameter(hstmt, 2, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &order.OrdNo, sizeof(SQLINTEGER), NULL);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // 3- OrdDate output
   r = SQLBindParameter(hstmt, 3, SQL_PARAM_OUTPUT, SQL_C_TCHAR, SQL_TYPE_TIMESTAMP, 23, 3, order.OrdDate, sizeof(order.OrdDate), NULL);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true);
      exit(-1);
   }

   // Insert the order
   r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("{call TVPOrderInsert(?, ?, ?)}"),SQL_NTS);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Flush results & reset hstmt
   r = SQLMoreResults(hstmt);
   if (r != SQL_NO_DATA) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   r = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Bind parameters for the Items
   // 1 - OrdNo 
   r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, OrdNo, sizeof(SQLINTEGER), NULL);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // 2 - ProdCode
   r = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, order.ProdCode, sizeof(SQLINTEGER), NULL);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // 3 - Qty
   r = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, order.Qty, sizeof(SQLINTEGER), NULL);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Set param array size for items
   SQLULEN arraySize;
   arraySize = order.ItemCount;
   r = SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, reinterpret_cast<SQLPOINTER> (arraySize), SQL_IS_UINTEGER);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Initialize OrdNo array 
   for (int i = 0; i < ITEM_ARRAY_SIZE; i++)
      OrdNo[i] = order.OrdNo;

   // Insert the Items
   r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("{call TVPItemInsert(?, ?, ?)}"),SQL_NTS);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Flush results & reset hstmt
   r = SQLMoreResults(hstmt);
   if (r != SQL_NO_DATA) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   r = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   r = SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER) 1, SQL_IS_UINTEGER);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Commit the transaction
   r = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }
}

void OrdEntry_TVP (OrdEntryData& order){
   // Order entry using a TVP
   SQLRETURN r;

   // Variable for TVP row count
   SQLLEN cbTVP;

   // Bind parameters for call to TVPOrderEntryDirect
   // 1 - Custcode input
   r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,SQL_C_TCHAR, SQL_VARCHAR, 5, 0, &order.CustCode, sizeof(order.CustCode), NULL);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // 2 - Items TVP
   r = SQLBindParameter(hstmt, 
      2,// ParameterNumber
      SQL_PARAM_INPUT,// InputOutputType
      SQL_C_DEFAULT,// ValueType 
      SQL_SS_TABLE,// Parametertype
      ITEM_ARRAY_SIZE,// ColumnSize - for a TVP this the row array size
      0,// DecimalDigits - for a TVP this is the number of columns in the TVP 
      NULL,// ParameterValuePtr - for a TVP this is the type name of the TVP
                        // (not needed with stored proc)
      NULL,// BufferLength - for a TVP this is the length of the type name or SQL_NTS
                        // (not needed with stored proc)
      &cbTVP);// StrLen_or_IndPtr - for a TVP this is the number of rows available

   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // 3 - OrdNo output
   r = SQLBindParameter(hstmt, 3, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &order.OrdNo, sizeof(SQLINTEGER), NULL);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // 4- OrdDate output
   r = SQLBindParameter(hstmt, 4, SQL_PARAM_OUTPUT, SQL_C_TCHAR, SQL_TYPE_TIMESTAMP, 23, 3, order.OrdDate, sizeof(order.OrdDate), NULL);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Bind columns for the TVP (param 2)
   // First set focus on param 2
   r = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 2, SQL_IS_INTEGER);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Col 1 - ProdCode
   r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, order.ProdCode, sizeof(SQLINTEGER), NULL);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Col 2 - Qty
   r = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, order.Qty, sizeof(SQLINTEGER), NULL);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Reset param focus
   r = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 0, SQL_IS_INTEGER);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Initialize TVP row count
   cbTVP = order.ItemCount; // Number of rows available for input

   // Call one procedure which inserts both the order and items
   r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("{call TVPOrderEntry(?, ?, ?, ?)}"),SQL_NTS);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Flush results & reset hstmt
   r = SQLMoreResults(hstmt);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO && r!= SQL_NO_DATA) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   r = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Commit the transaction
   r = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }
}


void demo_fixed_TVP_binding (SQLHANDLE hstmt){
   // Bind a TVP using program arrays
   SQLRETURN r;

   // Variables for SQL parameters
   SQLTCHAR CustCode[6];
   SQLWCHAR *TVP = (SQLWCHAR *) L"TVPParam";
   SQLINTEGER ProdCode[ITEM_ARRAY_SIZE], Qty[ITEM_ARRAY_SIZE];
   SQLINTEGER OrdNo;
   SQLTCHAR OrdDate[24];

   // Variables for indicator/length variables associated with parameters
   SQLLEN cbCustCode, cbTVP, cbProdCode[ITEM_ARRAY_SIZE], cbQty[ITEM_ARRAY_SIZE], cbOrdNo, cbOrdDate;

   // Bind parameters for call to TVPOrderEntryDirect
   // 1 - Custcode input
   r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_TCHAR, SQL_VARCHAR, 5, 0, CustCode, sizeof(CustCode), &cbCustCode);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

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

   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // 3 - OrdNo output
   r = SQLBindParameter(hstmt, 3, SQL_PARAM_OUTPUT,SQL_C_LONG, SQL_INTEGER, 0, 0, &OrdNo, sizeof(SQLINTEGER), &cbOrdNo);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // 4- OrdDate output
   r = SQLBindParameter(hstmt, 4, SQL_PARAM_OUTPUT,SQL_C_TCHAR, SQL_TYPE_TIMESTAMP, 23, 3, OrdDate, sizeof(OrdDate), &cbOrdDate);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Bind columns for the TVP (param 2)
   //First set focus on param 2
   r = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 2, SQL_IS_INTEGER);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Col 1 - ProdCode
   r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,SQL_C_LONG, SQL_INTEGER, 0, 0, ProdCode, sizeof(SQLINTEGER), cbProdCode);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Col 2 - Qty
   r = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,SQL_C_LONG, SQL_INTEGER, 0, 0, Qty, sizeof(SQLINTEGER), cbQty);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Reset param focus
   r = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 0, SQL_IS_INTEGER);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }


   // Populate parameters
   cbTVP = 0;   // Number of rows available for input
   strcpy_s((char *) CustCode, sizeof(CustCode), "CUST1"); cbCustCode = SQL_NTS;

   ProdCode[cbTVP] = 1215;
   cbProdCode[cbTVP] = sizeof(SQLINTEGER); 
   Qty[cbTVP] = 5;
   cbQty[cbTVP] = sizeof(SQLINTEGER); 
   cbTVP++;   // Number of rows available for input

   ProdCode[cbTVP] = 1017;
   cbProdCode[cbTVP] = sizeof(SQLINTEGER); 
   Qty[cbTVP] = 2;
   cbQty[cbTVP] = sizeof(SQLINTEGER); 
   cbTVP++;   // Number of rows available for input

   // Call the procedure
   r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("{call TVPOrderEntry(?, ?, ?, ?)}"),SQL_NTS);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Flush rowcounts
   do {
      r = SQLMoreResults(hstmt);
      if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
         ODBCError(henv, hdbc, hstmt, NULL, true); 
         exit(-1);
      }

   } while (r != SQL_NO_DATA);

   // Commit the transaction
   r = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }
}


void demo_variable_TVP_binding (SQLHANDLE hstmt) {
   // Bind a TVP using program data at exec and output row streaming
   SQLRETURN r;

   // Variables for SQL parameters
   SQLCHAR CustCode[6];
   SQLWCHAR *TVP = (SQLWCHAR *) L"TVPParam";
   SQLINTEGER ProdCode, Qty;
   SQLINTEGER OrdNo;
   char *OrdDate[23];

   // Variables for indicator/length variables associated with parameters
   SQLLEN cbCustCode, cbTVP, cbProdCode, cbQty, cbOrdNo, cbOrdDate;

   // Token returned by SQLParamData to indicate which param data is needed for
   SQLPOINTER ParamId;

   // Bind parameters for call to TVPOrderEntry
   r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_TCHAR, SQL_VARCHAR, 5, 0, CustCode, sizeof(CustCode), &cbCustCode);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }


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

   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // 3 - OrdNo output
   r = SQLBindParameter(hstmt, 3, SQL_PARAM_OUTPUT,SQL_C_LONG, SQL_INTEGER, 0, 0, &OrdNo, sizeof(SQLINTEGER), &cbOrdNo);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // 4 - OrdDate output
   r = SQLBindParameter(hstmt, 4, SQL_PARAM_OUTPUT,SQL_C_CHAR, SQL_TYPE_TIMESTAMP, 23, 3, &OrdDate, sizeof(OrdDate), &cbOrdDate);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Bind the TVP columns
   // First set focus on param 2
   r = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 2, SQL_IS_INTEGER);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // ProdCode
   r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,SQL_C_LONG, SQL_INTEGER, 0, 0, &ProdCode, sizeof(SQLINTEGER), &cbProdCode);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Qty
   r = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,SQL_C_LONG, SQL_INTEGER, 0, 0, &Qty, sizeof(SQLINTEGER), &cbQty);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   // Reset param focus
   r = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 0, SQL_IS_INTEGER);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

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

   // Initialize output parameters for normal binding
   cbOrdNo = sizeof(SQLINTEGER);
   cbOrdDate = sizeof(OrdDate);

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

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

   // Check if para data needed and get 1st param id token
   if (r == SQL_NEED_DATA)
      r = SQLParamData(hstmt, &ParamId);

   // Supply param row data
   int rowNum = 0;
   while (r == SQL_NEED_DATA) {
      if (ParamId == TVP) {
         switch (rowNum) {
                case 0:   // Supply data for 1st row
                   // Populate input TVP row constituent columns
                   ProdCode = 1215;
                   cbProdCode = sizeof(SQLINTEGER); 
                   Qty = 5;
                   cbQty = sizeof(SQLINTEGER);

                   // Returning 1 for Str_Len_or_Ind indicates a row is available
                   r = SQLPutData(hstmt, SQLPOINTER(1), 1);
                   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
                      ODBCError(henv, hdbc, hstmt, NULL, true); 
                      exit(-1);
                   }

                   rowNum++;
                   break;

                case 1:   // Supply data for 2nd row
                   // Populate another TVP row as above
                   ProdCode = 1017;
                   cbProdCode = sizeof(SQLINTEGER); 

                   // This time supply Qty via SQLPutData ...
                   Qty = 0;
                   cbQty = SQL_DATA_AT_EXEC;

                   r = SQLPutData(hstmt, SQLPOINTER(1), 1);
                   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
                      ODBCError(henv, hdbc, hstmt, NULL, true); 
                      exit(-1);
                   }

                   rowNum++;
                   break;

                default:
                   // Sending 0 indicates that now more TVP rows are available
                   r = SQLPutData(hstmt, SQLPOINTER(1), 0);
                   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
                      ODBCError(henv, hdbc, hstmt, NULL, true); 
                      exit(-1);
                   }

                   break;
         }
      }
      else {
         if (ParamId == &Qty) {
            Qty = 2;
            // For a character or binary parameter, SQLPutData can be called multiple times to pass the value in pieces
            SQLPutData(hstmt, &Qty, sizeof(SQLINTEGER));
            if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
               ODBCError(henv, hdbc, hstmt, NULL, true); 
               exit(-1);
            }
         }
      }

      // Signal that param data is available, get token for next param
      r = SQLParamData(hstmt, &ParamId);
   }

   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      _tprintf_s(_T("Error streaming input rows\n"));
      exit(-1);
   }

   // Commit the transaction
   r = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }
}

// Getting metadata for columns of a table type used for TVPs
// is just the same as getting column metadata for a regular table
void demo_metadata_for_table_type_columns(SQLTCHAR *TableTypeName) {
   SQLHANDLE chstmt;
   SQLRETURN r;

   r = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &chstmt);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   r = SQLSetStmtAttr(chstmt, SQL_SOPT_SS_NAME_SCOPE, (SQLPOINTER) SQL_SS_NAME_SCOPE_TABLE_TYPE, SQL_IS_UINTEGER);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   r = SQLColumns(chstmt, NULL, 0, NULL, 0, TableTypeName, SQL_NTS, NULL, 0);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   if (r == SQL_SUCCESS) {
      int colNo = 0; 
      SQLTCHAR columnType[256];
      for ( ; ; ) {
         r = SQLFetch(chstmt);
         if (r != SQL_SUCCESS) 
            break;
         SQLGetData(chstmt, 6, SQL_C_TCHAR, columnType, sizeof(columnType), NULL);
         _tprintf(_T("\tColumn %i has type %s\n"), ++colNo, columnType);
      }
   }

   r = SQLCloseCursor(chstmt);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   r = SQLFreeHandle(SQL_HANDLE_STMT, chstmt);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }
}

// To get metadata for a TVP from a prepared statement, an application calls
// SQLDescribeParam. SQLGetDescField can be used with SQL_DESC_TYPE_NAME in 
// the IPD to get the underlying table type name and its catalog and schema.
// For a TVP, DecimalDigits returns the number of columns in the TVP.
void demo_metadata_from_prepared_statement(SQLHANDLE hstmt) {
   SQLRETURN r;
   SQLUSMALLINT paramNo=1;
   SQLSMALLINT DataType;
   SQLULEN ParameterSize;
   SQLSMALLINT DecimalDigits, Nullable, NumParams;
   SQLHANDLE IPD;
   SQLINTEGER StringLength;
   SQLTCHAR parameterTypeName[256];

   r = SQLPrepare(hstmt, (SQLTCHAR *) _T("{call TVPOrderEntry(?, ?, ?, ?)}"), SQL_NTS);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   r = SQLNumParams(hstmt, &NumParams);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   r = SQLGetStmtAttr(hstmt, SQL_ATTR_IMP_PARAM_DESC, &IPD, SQL_IS_POINTER, &StringLength);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   for (paramNo = 1; paramNo <= NumParams; paramNo++) {
      r = SQLDescribeParam(hstmt, paramNo, &DataType, &ParameterSize, &DecimalDigits, &Nullable);
      if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
         ODBCError(henv, hdbc, hstmt, NULL, true); 
         exit(-1);
      }

      if (r == SQL_SUCCESS) {
         r = SQLGetDescField(IPD, paramNo, SQL_DESC_TYPE_NAME, parameterTypeName, sizeof(parameterTypeName), &StringLength);
         if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
            ODBCError(henv, hdbc, hstmt, NULL, true); 
            exit(-1);
         }

         _tprintf(_T("Parameter %i has type %s\n"), paramNo, parameterTypeName);
      }

      if (DataType == SQL_SS_TABLE) {
         r = SQLCancel(hstmt);
         demo_metadata_for_table_type_columns(parameterTypeName);
      }
   }
}

// An application uses SQLProcedureColumns in the usual way to get parameter
// information for procedures which use TVPs. However, this does not return
// column metadata for TVPs. Instead an application uses the data type name for 
// the TVP with SQLColumns to get column metadata
void demo_metadata_from_catalog_APIs(SQLTCHAR *procName) {
   SQLHANDLE chstmt;
   SQLRETURN r;

   r = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &chstmt);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, NULL, NULL, true); 
      exit(-1);
   }

   r = SQLProcedureColumns(chstmt, NULL, 0, NULL, 0, procName, SQL_NTS, NULL, 0);

   if (r == SQL_SUCCESS) {
      SQLSMALLINT paramType;
      SQLLEN colNameInd;
      SQLTCHAR colName[256];
      SQLTCHAR colTypeName[256];
      SQLSMALLINT colDataType;
      SQLINTEGER colOrdinal;
      int pNum = 0;
      TCHAR *preamble;

      for ( ; ; ) {
         r = SQLFetch(chstmt);
         pNum++;
         if (r != SQL_SUCCESS) 
            break;
         r = SQLGetData(chstmt, 4, SQL_C_TCHAR, colName, sizeof(colTypeName), &colNameInd);
         if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
            ODBCError(henv, hdbc, hstmt, NULL, true); 
            exit(-1);
         }

         if (colNameInd < 0)
            colName[0] = 0;

         r = SQLGetData(chstmt, 5, SQL_C_SHORT, &paramType, sizeof(SQL_C_SHORT), NULL);
         if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
            ODBCError(henv, hdbc, hstmt, NULL, true); 
            exit(-1);
         }

         switch(paramType) {
                case SQL_PARAM_INPUT:
                   preamble = _T("(Input)        Parameter"); 
                   break;

                case SQL_PARAM_INPUT_OUTPUT:
                   preamble = _T("(Input/Output) Parameter"); 
                   break;

                case SQL_PARAM_OUTPUT:
                   preamble = _T("(Output)       Parameter"); 
                   break;

                case SQL_RETURN_VALUE:
                   preamble = _T("(Return)       Parameter"); 
                   break;

                case SQL_RESULT_COL:
                   preamble = _T("Result Column"); 
                   break;

                case SQL_PARAM_TYPE_UNKNOWN:

                default:
                   preamble = _T("(Unknown) Parameter"); 
                   break;
         }

         r = SQLGetData(chstmt, 6, SQL_C_SHORT, &colDataType, sizeof(SQL_C_SHORT), NULL);
         if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
            ODBCError(henv, hdbc, chstmt, NULL, true); 
            exit(-1);
         }

         r = SQLGetData(chstmt, 7, SQL_C_TCHAR, colTypeName, sizeof(colTypeName), NULL);
         if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
            ODBCError(henv, hdbc, chstmt, NULL, true); 
            exit(-1);
         }

         r = SQLGetData(chstmt, 18, SQL_C_LONG, &colOrdinal, sizeof(SQL_C_LONG), NULL);
         if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
            ODBCError(henv, hdbc, chstmt, NULL, true);
            exit(-1);
         }

         _tprintf(_T("%s %i has type %s\n"), preamble, colOrdinal, colTypeName);

         if (colDataType == SQL_SS_TABLE) {
            r = SQLCancel(chstmt);
            if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
               ODBCError(henv, hdbc, chstmt, NULL, true);
               exit(-1);
            }

            demo_metadata_for_table_type_columns(colTypeName);

            r = SQLProcedureColumns(chstmt, NULL, 0, NULL, 0, procName, SQL_NTS, NULL, 0);
            if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
               ODBCError(henv, hdbc, chstmt, NULL, true); 
               exit(-1);
            }

            for (int x = 0; x < pNum; x++)
               r = SQLFetch(chstmt);

         }
      }
   }

   r = SQLCloseCursor(chstmt);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   r = SQLFreeHandle(SQL_HANDLE_STMT, chstmt);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }
}

void printOrder(SQLINTEGER OrdNo) {
   SQLRETURN r;
   SQLTCHAR OrdDate[24], CustCode[6];
   SQLINTEGER ProdCode, Qty;
   SQLLEN cbOrdNo, cbCustCode, cbOrdDate, cbProdCode, cbQty; 

   r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER,10, 0, &OrdNo, sizeof(SQLINTEGER), &cbOrdNo);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   cbOrdNo = 0;

   r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("select CustCode, OrdDate from TVPOrd where OrdNo=?"), SQL_NTS);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   r = SQLFetch(hstmt);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true);
      exit(-1);
   }

   r = SQLGetData(hstmt,1, SQL_C_TCHAR, CustCode, sizeof(CustCode), &cbCustCode);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true);
      exit(-1);
   }

   r = SQLGetData(hstmt,2, SQL_C_TCHAR, OrdDate, sizeof(OrdDate), &cbOrdDate);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true);
      exit(-1);
   }

   _tprintf(_T("OrderNo %i - Date %s - Cust %s\n"), OrdNo, OrdDate, CustCode);

   r = SQLCloseCursor(hstmt);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true);
      exit(-1);
   }

   r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("select ProdCode, Qty from TVPItem where OrdNo=?"), SQL_NTS);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true);
      exit(-1);
   }

   r = SQLFetch(hstmt);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO && r != SQL_NO_DATA) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   while (r == SQL_SUCCESS || r == SQL_SUCCESS_WITH_INFO) { 
      r = SQLGetData(hstmt,1, SQL_C_LONG, &ProdCode, sizeof(ProdCode), &cbProdCode);
      if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
         ODBCError(henv, hdbc, hstmt, NULL, true);
         exit(-1);
      }

      r = SQLGetData(hstmt,2, SQL_C_LONG, &Qty, sizeof(Qty), &cbQty);
      if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
         ODBCError(henv, hdbc, hstmt, NULL, true);
         exit(-1);
      }

      _tprintf(_T("        Product %i - Quantity %i\n"), ProdCode, Qty);

      r = SQLFetch(hstmt);
      if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO && r != SQL_NO_DATA) {
         ODBCError(henv, hdbc, hstmt, NULL, true);
         exit(-1);
      }
   }

   r = SQLCloseCursor(hstmt);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }

   r = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
   if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
      ODBCError(henv, hdbc, hstmt, NULL, true); 
      exit(-1);
   }
}

void testSimpleOrderEntry() {

   OrdEntryData order;

   order.OrdNo = 0;
   order.OrdDate[0] = _T('\0');
   _tcscpy_s((TCHAR *) order.CustCode, _countof(order.CustCode), _T("CUST1"));
   order.ProdCode[0] = 10;
   order.Qty[0] = 1;
   order.ProdCode[1] = 20;
   order.Qty[1] = 2;
   order.ProdCode[2] = 30;
   order.Qty[2] = 3;
   order.ProdCode[3] = 40;
   order.Qty[3] = 4;
   order.ItemCount = 4;

   OrdEntry_Simple(order);
   printOrder(order.OrdNo);
}


void testPAOrderEntry() {

   OrdEntryData order;

   order.OrdNo = 0;
   order.OrdDate[0] = _T('\0');
   _tcscpy_s ((TCHAR *) order.CustCode, _countof(order.CustCode), _T("CUST2"));
   order.ProdCode[0] = 100;
   order.Qty[0] = 10;
   order.ProdCode[1] = 200;
   order.Qty[1] = 20;
   order.ProdCode[2] = 300;
   order.Qty[2] = 30;
   order.ProdCode[3] = 400;
   order.Qty[3] = 40;
   order.ItemCount = 4;

   OrdEntry_PA(order);
   printOrder(order.OrdNo);
}

void testTVPOrderEntry() {
   OrdEntryData order;

   order.OrdNo = 0;
   order.OrdDate[0] = _T('\0');
   _tcscpy_s((TCHAR *) order.CustCode, _countof(order.CustCode), _T("CUST3"));
   order.ProdCode[0] = 1000;
   order.Qty[0] = 100;
   order.ProdCode[1] = 2000;
   order.Qty[1] = 200;
   order.ProdCode[2] = 3000;
   order.Qty[2] = 300;
   order.ProdCode[3] = 4000;
   order.Qty[3] = 400;
   order.ItemCount = 4;

   OrdEntry_TVP(order);

   printOrder(order.OrdNo);
}

int _tmain() {
   connect();
   setup_ODBC_basics();
   setup_TVP_demo();

   testSimpleOrderEntry();
   testPAOrderEntry ();
   testTVPOrderEntry();
   demo_metadata_from_catalog_APIs((SQLTCHAR *)_T("TVPOrderEntry"));
}
共用方式為

使用資料表值參數 (ODBC)

範例

其他資源
