테이블 반환 매개 변수 사용(ODBC)
이 예제에서는 테이블 반환 매개 변수를 사용하여 여러 열과 함께 서버에 대한 한 번의 호출로 여러 행을 삽입하는 방법을 보여 줍니다.
테이블 반환 매개 변수에 대한 자세한 내용은 테이블 반환 매개 변수(ODBC)를 참조하십시오. 테이블 반환 매개 변수를 사용하는 예제에 대한 자세한 내용은 ODBC 테이블 반환 매개 변수 프로그래밍 예제를 참조하십시오.
예
TVPDemo라는 ODBC 데이터 원본이 필요합니다. 컴퓨터의 모든 테스트 데이터베이스가 TVPDemo의 기본 데이터베이스가 될 수 있습니다. 이 데이터 원본은 SQL Server Native Client에 대한 ODBC 드라이버를 기반으로 해야 합니다.
이 예제를 64비트 운영 체제에서 32비트 응용 프로그램으로 작성하여 실행하려는 경우 %windir%\SysWOW64\odbcad32.exe에서 ODBC 관리자를 사용하여 ODBC 데이터 원본을 만들어야 합니다.
이 예제는 컴퓨터의 기본 SQL Server 인스턴스에 연결됩니다. 명명된 인스턴스에 연결하려면 ODBC 데이터 원본의 정의를 변경하여 server\namedinstance 형식으로 인스턴스를 지정합니다. 기본적으로 SQL Server Express는 명명된 인스턴스에 설치됩니다.
odbc32.lib 및 user32.lib를 사용하여 C++ 코드 목록을 컴파일합니다. INCLUDE 환경 변수에 sqlncli.h가 들어 있는 디렉터리를 포함해야 합니다.
// compile with: odbc32.lib user32.lib
#pragma once
#define WIN32_LEAN_AND_MEAN // Exclude rarely-used stuff from Windows headers
#include <stdio.h>
#include <stdlib.h>
#include <tchar.h>
#include <windows.h>
#include "sql.h"
#include "sqlext.h"
#include "sqlncli.h"
// cardinality of order item related array variables
#define ITEM_ARRAY_SIZE 20
// struct to pass order entry data
typedef struct OrdEntry_struct {
SQLINTEGER OrdNo;
SQLTCHAR OrdDate[24];
SQLTCHAR CustCode[6];
SQLUINTEGER ItemCount;
SQLINTEGER ProdCode[ITEM_ARRAY_SIZE];
SQLINTEGER Qty[ITEM_ARRAY_SIZE];
} OrdEntryData;
SQLHANDLE henv, hdbc, hstmt;
void ODBCError(SQLHANDLE henv, SQLHANDLE hdbc, SQLHANDLE hstmt, SQLHANDLE hdesc, bool ShowError) {
SQLRETURN r = 0;
SQLTCHAR szSqlState[6] = {0};
SQLINTEGER fNativeError = 0;
SQLTCHAR szErrorMsg[256] = {0};
SQLSMALLINT cbErrorMsgMax = sizeof(szErrorMsg) - 1;
SQLSMALLINT cbErrorMsg = 0;
TCHAR text[1024] = {0}, title[256] = {0};
if (hdesc != NULL)
r = SQLGetDiagRec(SQL_HANDLE_DESC, hdesc, 1, szSqlState, &fNativeError, szErrorMsg, cbErrorMsgMax, &cbErrorMsg);
else {
if (hstmt != NULL)
r = SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, szSqlState, &fNativeError, szErrorMsg, cbErrorMsgMax, &cbErrorMsg);
else {
if (hdbc != NULL)
r = SQLGetDiagRec(SQL_HANDLE_DBC, hdbc, 1, szSqlState, &fNativeError, szErrorMsg, cbErrorMsgMax, &cbErrorMsg);
else
r = SQLGetDiagRec(SQL_HANDLE_ENV, henv, 1, szSqlState, &fNativeError, szErrorMsg, cbErrorMsgMax, &cbErrorMsg);
}
}
if (ShowError) {
_sntprintf_s(title, _countof(title), _TRUNCATE, _T("ODBC Error %i"), fNativeError);
_sntprintf_s(text, _countof(text), _TRUNCATE, _T("[%s] - %s"), szSqlState, szErrorMsg);
MessageBox(NULL, (LPCTSTR) text, (LPCTSTR) _T("ODBC Error"), MB_OK);
}
}
void connect() {
SQLRETURN r;
r = SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);
// This is an ODBC v3 application
r = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, NULL, NULL, NULL, true);
exit(-1);
}
r = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
// Run in ANSI/implicit transaction mode
r = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) SQL_AUTOCOMMIT_OFF, SQL_IS_INTEGER);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, NULL, NULL, NULL, true);
exit(-1);
}
TCHAR szConnStrIn[256] = _T("DSN=TVPDemo");
r = SQLDriverConnect(hdbc, NULL, (SQLTCHAR *) szConnStrIn, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, NULL, NULL, true);
exit(-1);
}
}
void setup_ODBC_basics() {
SQLRETURN r;
r = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
}
void setup_TVP_demo () {
SQLRETURN r;
// Drop prior versions of table and procedure
r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("drop procedure TVPOrderEntry"), SQL_NTS);
r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("drop procedure TVPOrderInsert"), SQL_NTS);
r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("drop procedure TVPItemInsert"), SQL_NTS);
r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("drop type TVPParam"), SQL_NTS);
r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("drop table TVPOrd"), SQL_NTS);
r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("drop table TVPItem"), SQL_NTS);
// Create tables
r = SQLExecDirect(hstmt,
(SQLTCHAR *) _T("create table TVPOrd( OrdNo integer identity(1,1), OrdDate datetime, CustCode varchar(5))"), SQL_NTS);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
r = SQLExecDirect(hstmt,
(SQLTCHAR *) _T("create table TVPItem( OrdNo integer, ItemNo integer identity(1,1), ProdCode integer, Qty integer)"), SQL_NTS);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Create TABLE type for use as a TVP
r = SQLExecDirect(hstmt,
(SQLTCHAR *) _T("create type TVPParam as table(ProdCode integer, Qty integer)"), SQL_NTS);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Create procedure for TVPOrd insert
r = SQLExecDirect(hstmt, (SQLTCHAR *)
_T("create procedure TVPOrderInsert(@CustCode varchar(5), \
@OrdNo integer output, @OrdDate datetime output)\
as \
set @OrdDate = GETDATE();\
insert into TVPOrd (OrdDate, CustCode) values (@OrdDate, @CustCode); \
select @OrdNo = SCOPE_IDENTITY()"), SQL_NTS);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Create procedure for TVPItem insert
r = SQLExecDirect(hstmt,
(SQLTCHAR *) _T("create procedure TVPItemInsert(@OrdNo integer, \
@ProdCode integer, @Qty integer)\
as \
insert into TVPItem (OrdNo, ProdCode, Qty) \
values (@OrdNo, @ProdCode, @Qty)"), SQL_NTS);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Create procedure with TVP parameters
r = SQLExecDirect(hstmt,
(SQLTCHAR *) _T("create procedure TVPOrderEntry(@CustCode varchar(5), @Items TVPParam READONLY, \
@OrdNo integer output, @OrdDate datetime output)\
as \
set @OrdDate = GETDATE();\
insert into TVPOrd (OrdDate, CustCode) values (@OrdDate, @CustCode); \
select @OrdNo = SCOPE_IDENTITY(); \
insert into TVPItem (OrdNo, ProdCode, Qty) \
select @OrdNo, ProdCode, Qty from @Items"), SQL_NTS);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
r = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
}
void OrdEntry_Simple (OrdEntryData& order) {
// Simple order entry
SQLRETURN r;
SQLINTEGER ProdCode, Qty;
// Bind parameters for the Order
// 1 - Custcode input
r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,SQL_C_TCHAR, SQL_VARCHAR, 5, 0, &order.CustCode, sizeof(order.CustCode), NULL);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// 2 - OrdNo output
r = SQLBindParameter(hstmt, 2, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &order.OrdNo, sizeof(SQLINTEGER), NULL);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// 3- OrdDate output
r = SQLBindParameter(hstmt, 3, SQL_PARAM_OUTPUT, SQL_C_TCHAR, SQL_TYPE_TIMESTAMP, 23, 3, order.OrdDate, sizeof(order.OrdDate), NULL);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Insert the order
r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("{call TVPOrderInsert(?, ?, ?)}"),SQL_NTS);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Flush results & reset hstmt
r = SQLMoreResults(hstmt);
if (r != SQL_NO_DATA) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
r = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Bind parameters for the Items
// 1 - OrdNo
r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 10, 0, &order.OrdNo, sizeof(SQLINTEGER), NULL);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// 2 - ProdCode
r = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 10, 0, &ProdCode, sizeof(SQLINTEGER), NULL);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// 3 - Qty
r = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 10, 0, &Qty, sizeof(SQLINTEGER), NULL);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Insert items one at a time
r = SQLPrepare(hstmt, (SQLTCHAR *) _T("{call TVPItemInsert(?, ?, ?)}"),SQL_NTS);
for (unsigned int i = 0; i < order.ItemCount; i++) {
ProdCode = order.ProdCode[i];
Qty = order.Qty[i];
r = SQLExecute(hstmt);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
}
// Flush results & reset hstmt
r = SQLMoreResults(hstmt);
if (r != SQL_NO_DATA) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
r = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Commit the transaction
r = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
}
void OrdEntry_PA (OrdEntryData& order){
// best practice (not using TVPs) using a parameter array
SQLRETURN r;
// Array if OrdNo for use with array insert of Items
SQLINTEGER OrdNo[ITEM_ARRAY_SIZE];
// Bind parameters for the Order
// 1 - Custcode input
r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,SQL_C_TCHAR, SQL_VARCHAR, 5, 0, &order.CustCode, sizeof(order.CustCode), NULL);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// 2 - OrdNo output
r = SQLBindParameter(hstmt, 2, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &order.OrdNo, sizeof(SQLINTEGER), NULL);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// 3- OrdDate output
r = SQLBindParameter(hstmt, 3, SQL_PARAM_OUTPUT, SQL_C_TCHAR, SQL_TYPE_TIMESTAMP, 23, 3, order.OrdDate, sizeof(order.OrdDate), NULL);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Insert the order
r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("{call TVPOrderInsert(?, ?, ?)}"),SQL_NTS);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Flush results & reset hstmt
r = SQLMoreResults(hstmt);
if (r != SQL_NO_DATA) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
r = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Bind parameters for the Items
// 1 - OrdNo
r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, OrdNo, sizeof(SQLINTEGER), NULL);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// 2 - ProdCode
r = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, order.ProdCode, sizeof(SQLINTEGER), NULL);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// 3 - Qty
r = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, order.Qty, sizeof(SQLINTEGER), NULL);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Set param array size for items
SQLULEN arraySize;
arraySize = order.ItemCount;
r = SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, reinterpret_cast<SQLPOINTER> (arraySize), SQL_IS_UINTEGER);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Initialize OrdNo array
for (int i = 0; i < ITEM_ARRAY_SIZE; i++)
OrdNo[i] = order.OrdNo;
// Insert the Items
r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("{call TVPItemInsert(?, ?, ?)}"),SQL_NTS);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Flush results & reset hstmt
r = SQLMoreResults(hstmt);
if (r != SQL_NO_DATA) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
r = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
r = SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER) 1, SQL_IS_UINTEGER);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Commit the transaction
r = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
}
void OrdEntry_TVP (OrdEntryData& order){
// Order entry using a TVP
SQLRETURN r;
// Variable for TVP row count
SQLLEN cbTVP;
// Bind parameters for call to TVPOrderEntryDirect
// 1 - Custcode input
r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,SQL_C_TCHAR, SQL_VARCHAR, 5, 0, &order.CustCode, sizeof(order.CustCode), NULL);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// 2 - Items TVP
r = SQLBindParameter(hstmt,
2,// ParameterNumber
SQL_PARAM_INPUT,// InputOutputType
SQL_C_DEFAULT,// ValueType
SQL_SS_TABLE,// Parametertype
ITEM_ARRAY_SIZE,// ColumnSize - for a TVP this the row array size
0,// DecimalDigits - for a TVP this is the number of columns in the TVP
NULL,// ParameterValuePtr - for a TVP this is the type name of the TVP
// (not needed with stored proc)
NULL,// BufferLength - for a TVP this is the length of the type name or SQL_NTS
// (not needed with stored proc)
&cbTVP);// StrLen_or_IndPtr - for a TVP this is the number of rows available
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// 3 - OrdNo output
r = SQLBindParameter(hstmt, 3, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &order.OrdNo, sizeof(SQLINTEGER), NULL);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// 4- OrdDate output
r = SQLBindParameter(hstmt, 4, SQL_PARAM_OUTPUT, SQL_C_TCHAR, SQL_TYPE_TIMESTAMP, 23, 3, order.OrdDate, sizeof(order.OrdDate), NULL);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Bind columns for the TVP (param 2)
// First set focus on param 2
r = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 2, SQL_IS_INTEGER);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Col 1 - ProdCode
r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, order.ProdCode, sizeof(SQLINTEGER), NULL);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Col 2 - Qty
r = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, order.Qty, sizeof(SQLINTEGER), NULL);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Reset param focus
r = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 0, SQL_IS_INTEGER);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Initialize TVP row count
cbTVP = order.ItemCount; // Number of rows available for input
// Call one procedure which inserts both the order and items
r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("{call TVPOrderEntry(?, ?, ?, ?)}"),SQL_NTS);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Flush results & reset hstmt
r = SQLMoreResults(hstmt);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO && r!= SQL_NO_DATA) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
r = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Commit the transaction
r = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
}
void demo_fixed_TVP_binding (SQLHANDLE hstmt){
// Bind a TVP using program arrays
SQLRETURN r;
// Variables for SQL parameters
SQLTCHAR CustCode[6];
SQLWCHAR *TVP = (SQLWCHAR *) L"TVPParam";
SQLINTEGER ProdCode[ITEM_ARRAY_SIZE], Qty[ITEM_ARRAY_SIZE];
SQLINTEGER OrdNo;
SQLTCHAR OrdDate[24];
// Variables for indicator/length variables associated with parameters
SQLLEN cbCustCode, cbTVP, cbProdCode[ITEM_ARRAY_SIZE], cbQty[ITEM_ARRAY_SIZE], cbOrdNo, cbOrdDate;
// Bind parameters for call to TVPOrderEntryDirect
// 1 - Custcode input
r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_TCHAR, SQL_VARCHAR, 5, 0, CustCode, sizeof(CustCode), &cbCustCode);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// 2 - Items TVP
r = SQLBindParameter(hstmt,
2,// ParameterNumber
SQL_PARAM_INPUT,// InputOutputType
SQL_C_DEFAULT,// ValueType
SQL_SS_TABLE,// Parametertype
ITEM_ARRAY_SIZE,// ColumnSize - for a TVP this the row array size
0,// DecimalDigits - for a TVP this is the number of columns in the TVP
TVP,// ParameterValuePtr - for a TVP this is the type name of the TVP
// and also a token returned by SQLParamData
SQL_NTS,// BufferLength - for a TVP this is the length of the type name or SQL_NTS
&cbTVP);// StrLen_or_IndPtr - for a TVP this is the number of rows input and output
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// 3 - OrdNo output
r = SQLBindParameter(hstmt, 3, SQL_PARAM_OUTPUT,SQL_C_LONG, SQL_INTEGER, 0, 0, &OrdNo, sizeof(SQLINTEGER), &cbOrdNo);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// 4- OrdDate output
r = SQLBindParameter(hstmt, 4, SQL_PARAM_OUTPUT,SQL_C_TCHAR, SQL_TYPE_TIMESTAMP, 23, 3, OrdDate, sizeof(OrdDate), &cbOrdDate);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Bind columns for the TVP (param 2)
//First set focus on param 2
r = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 2, SQL_IS_INTEGER);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Col 1 - ProdCode
r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,SQL_C_LONG, SQL_INTEGER, 0, 0, ProdCode, sizeof(SQLINTEGER), cbProdCode);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Col 2 - Qty
r = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,SQL_C_LONG, SQL_INTEGER, 0, 0, Qty, sizeof(SQLINTEGER), cbQty);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Reset param focus
r = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 0, SQL_IS_INTEGER);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Populate parameters
cbTVP = 0; // Number of rows available for input
strcpy_s((char *) CustCode, sizeof(CustCode), "CUST1"); cbCustCode = SQL_NTS;
ProdCode[cbTVP] = 1215;
cbProdCode[cbTVP] = sizeof(SQLINTEGER);
Qty[cbTVP] = 5;
cbQty[cbTVP] = sizeof(SQLINTEGER);
cbTVP++; // Number of rows available for input
ProdCode[cbTVP] = 1017;
cbProdCode[cbTVP] = sizeof(SQLINTEGER);
Qty[cbTVP] = 2;
cbQty[cbTVP] = sizeof(SQLINTEGER);
cbTVP++; // Number of rows available for input
// Call the procedure
r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("{call TVPOrderEntry(?, ?, ?, ?)}"),SQL_NTS);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Flush rowcounts
do {
r = SQLMoreResults(hstmt);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
} while (r != SQL_NO_DATA);
// Commit the transaction
r = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
}
void demo_variable_TVP_binding (SQLHANDLE hstmt) {
// Bind a TVP using program data at exec and output row streaming
SQLRETURN r;
// Variables for SQL parameters
SQLCHAR CustCode[6];
SQLWCHAR *TVP = (SQLWCHAR *) L"TVPParam";
SQLINTEGER ProdCode, Qty;
SQLINTEGER OrdNo;
char *OrdDate[23];
// Variables for indicator/length variables associated with parameters
SQLLEN cbCustCode, cbTVP, cbProdCode, cbQty, cbOrdNo, cbOrdDate;
// Token returned by SQLParamData to indicate which param data is needed for
SQLPOINTER ParamId;
// Bind parameters for call to TVPOrderEntry
r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_TCHAR, SQL_VARCHAR, 5, 0, CustCode, sizeof(CustCode), &cbCustCode);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// 2 - Items TVP
r = SQLBindParameter(hstmt,
2,// ParameterNumber
SQL_PARAM_INPUT,// InputOutputType
SQL_C_DEFAULT,// ValueType
SQL_SS_TABLE,// Parametertype
ITEM_ARRAY_SIZE,// ColumnSize - for a TVP this the row array size
0,// DecimalDigits - for a TVP this is the number of columns in the TVP
TVP,// ParameterValuePtr - for a TVP this is the type name of the TVP
// and also a token returned by SQLParamData
SQL_NTS,// BufferLength - for a TVP this is the length of the type name or SQL_NTS
&cbTVP);// StrLen_or_IndPtr - for a TVP this is the number of rows input and output
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// 3 - OrdNo output
r = SQLBindParameter(hstmt, 3, SQL_PARAM_OUTPUT,SQL_C_LONG, SQL_INTEGER, 0, 0, &OrdNo, sizeof(SQLINTEGER), &cbOrdNo);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// 4 - OrdDate output
r = SQLBindParameter(hstmt, 4, SQL_PARAM_OUTPUT,SQL_C_CHAR, SQL_TYPE_TIMESTAMP, 23, 3, &OrdDate, sizeof(OrdDate), &cbOrdDate);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Bind the TVP columns
// First set focus on param 2
r = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 2, SQL_IS_INTEGER);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// ProdCode
r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,SQL_C_LONG, SQL_INTEGER, 0, 0, &ProdCode, sizeof(SQLINTEGER), &cbProdCode);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Qty
r = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,SQL_C_LONG, SQL_INTEGER, 0, 0, &Qty, sizeof(SQLINTEGER), &cbQty);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Reset param focus
r = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 0, SQL_IS_INTEGER);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
// Initialze the TVP for row streaming
cbTVP = SQL_DATA_AT_EXEC;
// Initialize output parameters for normal binding
cbOrdNo = sizeof(SQLINTEGER);
cbOrdDate = sizeof(OrdDate);
// Populate non-data-at-exec parameters
strcpy_s((char *) CustCode , sizeof(CustCode), "CUST1");
cbCustCode = SQL_NTS;
// Call the procedure
r = SQLExecDirect(hstmt, (SQLTCHAR *) _T("{call TVPOrderEntry(?, ?, ?, ?)}"), SQL_NTS);
// Check if para data needed and get 1st param id token
if (r == SQL_NEED_DATA)
r = SQLParamData(hstmt, &ParamId);
// Supply param row data
int rowNum = 0;
while (r == SQL_NEED_DATA) {
if (ParamId == TVP) {
switch (rowNum) {
case 0: // Supply data for 1st row
// Populate input TVP row constituent columns
ProdCode = 1215;
cbProdCode = sizeof(SQLINTEGER);
Qty = 5;
cbQty = sizeof(SQLINTEGER);
// Returning 1 for Str_Len_or_Ind indicates a row is available
r = SQLPutData(hstmt, SQLPOINTER(1), 1);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
rowNum++;
break;
case 1: // Supply data for 2nd row
// Populate another TVP row as above
ProdCode = 1017;
cbProdCode = sizeof(SQLINTEGER);
// This time supply Qty via SQLPutData ...
Qty = 0;
cbQty = SQL_DATA_AT_EXEC;
r = SQLPutData(hstmt, SQLPOINTER(1), 1);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
rowNum++;
break;
default:
// Sending 0 indicates that now more TVP rows are available
r = SQLPutData(hstmt, SQLPOINTER(1), 0);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
break;
}
}
else {
if (ParamId == &Qty) {
Qty = 2;
// For a character or binary parameter, SQLPutData can be called multiple times to pass the value in pieces
SQLPutData(hstmt, &Qty, sizeof(SQLINTEGER));
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
}
}
// Signal that param data is available, get token for next param
r = SQLParamData(hstmt, &ParamId);
}
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
_tprintf_s(_T("Error streaming input rows\n"));
exit(-1);
}
// Commit the transaction
r = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
}
// Getting metadata for columns of a table type used for TVPs
// is just the same as getting column metadata for a regular table
void demo_metadata_for_table_type_columns(SQLTCHAR *TableTypeName) {
SQLHANDLE chstmt;
SQLRETURN r;
r = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &chstmt);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
r = SQLSetStmtAttr(chstmt, SQL_SOPT_SS_NAME_SCOPE, (SQLPOINTER) SQL_SS_NAME_SCOPE_TABLE_TYPE, SQL_IS_UINTEGER);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
r = SQLColumns(chstmt, NULL, 0, NULL, 0, TableTypeName, SQL_NTS, NULL, 0);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
if (r == SQL_SUCCESS) {
int colNo = 0;
SQLTCHAR columnType[256];
for ( ; ; ) {
r = SQLFetch(chstmt);
if (r != SQL_SUCCESS)
break;
SQLGetData(chstmt, 6, SQL_C_TCHAR, columnType, sizeof(columnType), NULL);
_tprintf(_T("\tColumn %i has type %s\n"), ++colNo, columnType);
}
}
r = SQLCloseCursor(chstmt);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
r = SQLFreeHandle(SQL_HANDLE_STMT, chstmt);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
}
// To get metadata for a TVP from a prepared statement, an application calls
// SQLDescribeParam. SQLGetDescField can be used with SQL_DESC_TYPE_NAME in
// the IPD to get the underlying table type name and its catalog and schema.
// For a TVP, DecimalDigits returns the number of columns in the TVP.
void demo_metadata_from_prepared_statement(SQLHANDLE hstmt) {
SQLRETURN r;
SQLUSMALLINT paramNo=1;
SQLSMALLINT DataType;
SQLULEN ParameterSize;
SQLSMALLINT DecimalDigits, Nullable, NumParams;
SQLHANDLE IPD;
SQLINTEGER StringLength;
SQLTCHAR parameterTypeName[256];
r = SQLPrepare(hstmt, (SQLTCHAR *) _T("{call TVPOrderEntry(?, ?, ?, ?)}"), SQL_NTS);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
r = SQLNumParams(hstmt, &NumParams);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
r = SQLGetStmtAttr(hstmt, SQL_ATTR_IMP_PARAM_DESC, &IPD, SQL_IS_POINTER, &StringLength);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
for (paramNo = 1; paramNo <= NumParams; paramNo++) {
r = SQLDescribeParam(hstmt, paramNo, &DataType, &ParameterSize, &DecimalDigits, &Nullable);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
if (r == SQL_SUCCESS) {
r = SQLGetDescField(IPD, paramNo, SQL_DESC_TYPE_NAME, parameterTypeName, sizeof(parameterTypeName), &StringLength);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
_tprintf(_T("Parameter %i has type %s\n"), paramNo, parameterTypeName);
}
if (DataType == SQL_SS_TABLE) {
r = SQLCancel(hstmt);
demo_metadata_for_table_type_columns(parameterTypeName);
}
}
}
// An application uses SQLProcedureColumns in the usual way to get parameter
// information for procedures which use TVPs. However, this does not return
// column metadata for TVPs. Instead an application uses the data type name for
// the TVP with SQLColumns to get column metadata
void demo_metadata_from_catalog_APIs(SQLTCHAR *procName) {
SQLHANDLE chstmt;
SQLRETURN r;
r = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &chstmt);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, NULL, NULL, true);
exit(-1);
}
r = SQLProcedureColumns(chstmt, NULL, 0, NULL, 0, procName, SQL_NTS, NULL, 0);
if (r == SQL_SUCCESS) {
SQLSMALLINT paramType;
SQLLEN colNameInd;
SQLTCHAR colName[256];
SQLTCHAR colTypeName[256];
SQLSMALLINT colDataType;
SQLINTEGER colOrdinal;
int pNum = 0;
TCHAR *preamble;
for ( ; ; ) {
r = SQLFetch(chstmt);
pNum++;
if (r != SQL_SUCCESS)
break;
r = SQLGetData(chstmt, 4, SQL_C_TCHAR, colName, sizeof(colTypeName), &colNameInd);
if (r != SQL_SUCCESS && r != SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true);
exit(-1);
}
if (colNameInd < 0)
colName[0] = 0;
r = SQLGetData(chstmt, 5, SQL_C_SHORT, ¶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"));
}