Use Table-Valued Parameters (ODBC)

Article
06/13/2017
This sample shows how to use table-valued parameters to insert multiple rows, with multiple columns, with one call to the server.
For more information about table-valued parameters, see Table-Valued Parameters (ODBC). For more samples using table-valued parameters, see ODBC Table-Valued Parameter Programming Examples.
Example

You will need an ODBC data source called TVPDemo. The default database for TVPDemo can be any test database on your computer. This data source must be based on the ODBC driver for SQL Server Native Client.
If you will build and run this sample as a 32-bit application on a 64-bit operating system, you must create the ODBC data source with the ODBC Administrator in %windir%\SysWOW64\odbcad32.exe.
This sample connects to your computer's default SQL Server instance. To connect to a named instance, change the definition of the ODBC data source to specify the instance using the following format: server\namedinstance. By default, SQL Server Express installs to a named instance.
Compile the (C++) code listing with odbc32.lib and user32.lib. Make sure your INCLUDE environment variable includes the directory that contains 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"));  
}
Share via

Use Table-Valued Parameters (ODBC)

Example

Additional resources
