VC++ ADODB SP 호출

USP_TEST : SP 샘플

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE  PROCEDURE [dbo].[USP_TEST]

(

@param1 INT,

@param2 VARCHAR(20),

@param3 BIGINT,

@param4 INT OUTPUT  

)

AS

SET NOCOUNT ON;

-- OUTPUT 값

SET @param4 = 22222

-- SELECT 값

DECLARE @Message NVARCHAR(20)

DECLARE @Number VARCHAR(20)

SET @Message = 'hi man~~'

SET @Number = NULL

SELECT @Message MSG, @Number NUM

-- RETURN 값

RETURN 22

SET NOCOUNT OFF

SP 호출 소스

#include "stdafx.h"

#include <oledb.h>

#include <oledberr.h>

#include <icrsint.h>

#include <iostream>

#include <strsafe.h>

#include <atlconv.h>

#import "D:\Program Files\Common Files\System\ado\msado15.dll" rename("EOF", "EndOfFile")

std::string ConvertSafeSQLString(std::string str)

{

std::string strSafe = str.c_str();

std::string::size_type Index = 0;

while(true)

{

Index = strSafe.find("'", Index);

if(std::wstring::npos == Index) break;

strSafe.replace(Index, 1, "''");

Index += 2;

}

return strSafe;

}

void RunTest()

{

setlocale(LC_ALL, "");

::CoInitialize(NULL);

ADODB::_ConnectionPtr m_pConn;

std::string strConn = "Provider=sqloledb; Data Source=192.167.56.101,1433; Initial Catalog=데이터베이스이름; User ID=*****; Password=*****;";

 

HRESULT hr = 0;

try

{

// Connection

hr = m_pConn.CreateInstance (__uuidof (ADODB::Connection));

if (FAILED (hr))

{

printf("Can't create intance of Connection\n");

}

if (FAILED (m_pConn->Open (_bstr_t (strConn.c_str()),_bstr_t (""), _bstr_t (""), ADODB::adConnectUnspecified))) //or ADODB::adModeUnknown

{

printf("Can't open datasource\n");

}

// SP 호출

if (true) {

ADODB::_CommandPtr pCommand;

hr = pCommand.CreateInstance (__uuidof (ADODB::Command));

if (FAILED (hr))

{

printf ("Can't create an instance of Command\n");

return;

}

pCommand->ActiveConnection = m_pConn;

pCommand->CommandText = "USP_TEST"; // SP 명

pCommand->CommandType = ADODB::adCmdStoredProc; // SP 호출

pCommand->CommandTimeout = 5;

// RETURN 값 가져오기

ADODB::_ParameterPtr pParamReturn = pCommand->CreateParameter( _bstr_t ("Ret"), ADODB::adInteger, ADODB::adParamReturnValue, sizeof(int));

pCommand->Parameters->Append(pParamReturn);

// @param1 INT

_variant_t varParam1 = 11;

ADODB::_ParameterPtr pParam1 = pCommand->CreateParameter( _bstr_t ("param1"), ADODB::adInteger, ADODB::adParamInput, sizeof(int), &varParam1);

pCommand->Parameters->Append(pParam1);

// @param2 VARCHAR(20)

std::string strParam2 = "STRING..";

_variant_t varParam2 = ConvertSafeSQLString(strParam2).c_str();

DWORD lenParam2 = (DWORD)strParam2.length();

ADODB::_ParameterPtr pParam2 = pCommand->CreateParameter( _bstr_t ("param2"), ADODB::adVarChar, ADODB::adParamInput, sizeof(char) * lenParam2, &varParam2);

pCommand->Parameters->Append(pParam2);

// @param3 BIGINT

INT64 param3 = 1234;

_variant_t varParam3 = param3;

ADODB::_ParameterPtr pParam3 = pCommand->CreateParameter( _bstr_t ("param3"), ADODB::adBigInt, ADODB::adParamInput, sizeof(__int64), &varParam3);

pCommand->Parameters->Append(pParam3);

// OUTPUT 값 가져오기

// @param4 INT OUTPUT 

_variant_t param4;

ADODB::_ParameterPtr pParam4 = pCommand->CreateParameter( _bstr_t ("param4"), ADODB::adInteger, ADODB::adParamOutput, sizeof(int), NULL);

pCommand->Parameters->Append(pParam4);

ADODB::_RecordsetPtr pRecordset;

hr = pRecordset.CreateInstance (__uuidof (ADODB::Recordset));

if (FAILED (hr))

{

printf ("Can't create an instance of Recordset\n");

return;

}

// SP 실행

pRecordset = pCommand->Execute(NULL, NULL, ADODB::adCmdStoredProc);

// SP 결과값

{

// SP SELECT 값 : MSG

WCHAR MSG[21] = {0,};

StringCchCopyW(MSG, 21, pRecordset->Fields->GetItem("MSG")->Value.bstrVal);

USES_CONVERSION;

printf("MSG : %s\n", W2A(MSG));

// SP SELECT 값 : NUM

int Num = 0;

if (pRecordset->Fields->GetItem("NUM")->Value.vt == VT_NULL)

{

printf("NUM : is NULL\n");

}

else

{

Num = pRecordset->Fields->GetItem("NUM")->Value;

printf("NUM : is NULL\n");

}

// SP OUTPUT 변수 값

int OutputValue = pCommand->GetParameters()->GetItem("param4")->Value;

printf("OutputValue : %d\n", OutputValue);

// SP 리턴 값

int ReturnValue = pParamReturn->Value;

//VARTYPE type = pParamReturn->Value.vt;

printf("ReturnValue : %d\n", ReturnValue);

}

}

}

catch ( _com_error &e )

{

_bstr_t bstrSource (e.Source());

_bstr_t bstrDescription (e.Description());

printf ( "Exception thrown for classes generated by #import\n" );

printf ( "\tCode = %08lx\n", e.Error ());

printf ( "\tCode = %ld\n", e.Error ());

printf ( "\tCode meaning = %s\n", e.ErrorMessage ());

printf ( "\tSource = %s\n", (LPCTSTR) bstrSource);

printf ( "\tDescription = %s\n", (LPCTSTR) bstrDescription);

}

catch (...)

{

printf ( "*** Unhandled Exception ***" );

}

if ( (m_pConn->State & ADODB::adStateOpen) == ADODB::adStateOpen)

m_pConn->Close();

::CoUninitialize();

}

int _tmain(int argc, _TCHAR* argv[])

{

printf("Start..\n");

RunTest();

printf("End..\n");

return 0;

}

code project에서 구한 ADODB를 이용한 쿼리 및 SP 실행 샘플 (출처를 까먹음)

ADOTest.zip

참고
ADO API Reference : http://msdn.microsoft.com/en-us/library/ms678086(v=vs.85).aspx
ADO Code Examples in Visual C++ : http://msdn.microsoft.com/en-us/library/ms677563(VS.85).aspx
ADO run-time error : http://msdn.microsoft.com/en-us/library/ms677004(VS.85).aspx