使用表值参数 (ODBC)

项目
11/24/2024
适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Analytics Platform System (PDW)
此示例演示了如何使用表值参数通过调用一次服务器插入多个行和多个列。
有关表值参数的详细信息，请参阅表值参数（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 - DecimalDigits: For a table-valued parameter this must always be 0.
      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 && r != SQL_NO_DATA) {  
         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:  
                   // Set StrLenOrIndPtr and DataPtr to 0, and send to indicate no more TVP rows are available
                   r = SQLPutData(hstmt, 0, 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  
            r = 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)

示例

反馈

其他资源
