Tablo Valued Parametreler (odbc) kullan
Bu örnek, birden çok sütunlu bir arama sunucusu ile birden çok satır eklemek için tablo değerli parametreleri kullanın gösterilmiştir.
Tablo değerli parametreleri hakkında daha fazla bilgi için bkz: Tablo Valued Parametreler (odbc). Tablo değerli parametreleri kullanarak daha fazla örnek için bkz: odbc tablo Valued parametre programlama örnekleri.
Örnek
TVPDemo adlı bir odbc veri kaynağı gerekir. TVPDemo için varsayılan veritabanı bilgisayarınızda herhangi bir sınama veritabanı olabilir. Bu veri kaynağı için odbc sürücüsü olarak dayalı gerekir SQL ServerNative Client.
Eğer kurmak ve bu örnek 32 bitlik bir uygulama bir 64-bit işletim sisteminde çalışan, odbc Yöneticisi'nde % windir%\SysWOW64\odbcad32.exe ile odbc veri kaynağı oluşturmanız gerekir.
Bu örnek için bilgisayarınızın varsayılan bağlar SQL Serverörneği. Adlandırılmış bir örneğine bağlanmak için aşağıdaki biçimi kullanarak belirtmek için odbc veri kaynağı tanımını değiştirin: server\namedinstance. Varsayılan olarak, SQL Server Expressadlandırılmış bir örneğine yüklenir.
Odbc32.lib ve user32.lib ile listeleme (c++) kodu derleyin. Sqlncli.h içeren dizinin, INCLUDE çevre değişkeni içerdiğinden emin olun.
// 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, ¶mType, 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"));
}
// 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, ¶mType, 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"));
}