使用資料表值參數 (ODBC)

適用于:SQL Server (Azure SQL Database Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System ( PDW) 的所有支援) 版本

此範例示範如何使用資料表值參數插入多個資料列、搭配多個資料行使用資料表值參數、搭配一個伺服器的呼叫使用資料表值參數。

如需資料表值參數的詳細資訊,請參閱 ODBC) (資料表值參數 。 如需使用資料表值參數的詳細資訊,請參閱 ODBC Table-Valued參數程式設計範例

範例

您將需要稱為 TVPDemo 的 ODBC 資料來源。 TVPDemo 的預設資料庫可以是您電腦上的任何測試資料庫。 此資料來源必須以 ODBC 驅動程式為基礎,才能SQL Server Native Client。

如果您要建立並執行此範例,當做 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"));  
}