사용자 정의 함수 실행 및 반환 코드 처리(OLE DB)

적용 대상: SQL Server Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System(PDW)

OLE DB 드라이버 다운로드

이 예에서는 사용자 정의 함수를 실행하고 반환 코드를 인쇄합니다. 이 예제는 IA64에서 지원되지 않습니다.

이 샘플에서는 oledbtest 데이터베이스를 예제로 사용합니다. 보유하고 있는 SQL Server 데이터베이스로 대체하세요.

중요

가능하면 Windows 인증을 사용하세요. Windows 인증을 사용할 수 없으면 런타임에 사용자에게 자격 증명을 입력하라는 메시지를 표시합니다. 자격 증명은 파일에 저장하지 않는 것이 좋습니다. 자격 증명을 유지하려면 Win32 crypto API를 사용하여 자격 증명을 암호화해야 합니다.

예제

첫 번째(Transact-SQL) 코드 목록을 실행하여 애플리케이션에서 사용하는 저장 프로시저를 만듭니다.

ole32.lib oleaut32.lib를 사용하여 컴파일하고 두 번째(C++) 코드 목록을 실행합니다. 이 애플리케이션은 컴퓨터의 기본 SQL Server 인스턴스에 연결됩니다. 일부 Windows 운영 체제에서는 (localhost) 또는 (local)을 해당 SQL Server 인스턴스의 이름으로 변경해야 합니다. 명명된 인스턴스에 연결하려면 연결 문자열을 L"(local)"에서 L"(local)\\name"으로 변경합니다. 여기서 name은 명명된 인스턴스입니다. 기본적으로 SQL Server Express는 명명된 인스턴스에 설치됩니다. INCLUDE 환경 변수에 msoledbsql.h가 들어 있는 디렉터리를 포함해야 합니다.

세 번째(Transact-SQL) 코드 목록을 실행하여 애플리케이션에서 사용하는 저장 프로시저를 삭제합니다.

if exists (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[fn_RectangleArea]'))  
   drop function fn_RectangleArea  
go  
  
CREATE FUNCTION fn_RectangleArea  
   (@Width int,   
@Height int )  
RETURNS int  
AS  
BEGIN  
   RETURN ( @Width * @Height )  
END  
GO  

// compile with: ole32.lib oleaut32.lib
#include <iostream>
#include <atlbase.h>    // CComPtr
#include "msoledbsql.h"

HRESULT InitializeAndEstablishConnection(CComPtr<IDBInitialize>& pIDBInitialize);
HRESULT ExecuteFunction(const CComPtr<IDBInitialize>& pIDBInitialize);

int main()
{
    HRESULT hr = S_OK;

    // Initialize the COM library.
    CoInitialize(nullptr);

    // All interfaces must be freed before CoUninitialize is called,
    // thus limiting the scope of pIDBInitialize
    {
        CComPtr<IDBInitialize> pIDBInitialize;

        // All the initialization stuff in a separate function.
        hr = InitializeAndEstablishConnection(pIDBInitialize);
        if (FAILED(hr))
        {
            std::cout << "Failed to connect\n";
            goto EXIT;
        }

        hr = ExecuteFunction(pIDBInitialize);
        if (FAILED(hr))
        {
            std::cout << "Failed in executing function\n";
            goto EXIT;
        }

        if (FAILED(pIDBInitialize->Uninitialize()))
        {
            // Uninitialize is not required, but it fails if an interface
            // has not been released.  This can be used for debugging.
            std::cout << "Problem uninitializing\n";
        }
    }
EXIT:
    CoUninitialize();
    return (FAILED(hr));
}

HRESULT InitializeAndEstablishConnection(CComPtr<IDBInitialize>& pIDBInitialize)
{
    HRESULT hr = S_OK;

    // Obtain access to the OLE DB Driver for SQL Server.
    hr = CoCreateInstance(CLSID_MSOLEDBSQL,
                          nullptr,
                          CLSCTX_INPROC_SERVER,
                          IID_IDBInitialize,
                          reinterpret_cast<LPVOID *>(&pIDBInitialize));
    if (FAILED(hr))
    {
        std::cout << "Failed in CoCreateInstance()\n";
        return hr;
    }

    const ULONG nInitProps1 = 3;
    const ULONG nInitProps2 = 1;
    const ULONG nPropSets = 2;
    CComBSTR server(L"(local)");
    CComBSTR database(L"oledbtest");
    CComBSTR auth(L"SSPI");
    CComBSTR encrypt(L"Mandatory");
    DBPROP InitProperties1[nInitProps1] = {};
    DBPROP InitProperties2[nInitProps2] = {};
    DBPROPSET rgInitPropSet[nPropSets] = {};

    // Initialize the property values needed to establish the connection.
    for (ULONG i = 0; i < nInitProps1; i++)
        VariantInit(&InitProperties1[i].vValue);

    // Specify server name.
    InitProperties1[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
    InitProperties1[0].vValue.vt = VT_BSTR;

    // Replace "MySqlServer" with proper value.
    InitProperties1[0].vValue.bstrVal = server;
    InitProperties1[0].dwOptions = DBPROPOPTIONS_REQUIRED;
    InitProperties1[0].colid = DB_NULLID;

    // Specify database name.
    InitProperties1[1].dwPropertyID = DBPROP_INIT_CATALOG;
    InitProperties1[1].vValue.vt = VT_BSTR;
    InitProperties1[1].vValue.bstrVal = database;
    InitProperties1[1].dwOptions = DBPROPOPTIONS_REQUIRED;
    InitProperties1[1].colid = DB_NULLID;

    InitProperties1[2].dwPropertyID = DBPROP_AUTH_INTEGRATED;
    InitProperties1[2].vValue.vt = VT_BSTR;
    InitProperties1[2].vValue.bstrVal = auth;
    InitProperties1[2].dwOptions = DBPROPOPTIONS_REQUIRED;
    InitProperties1[2].colid = DB_NULLID;

    // Data should be encrypted before sending it over the network
    VariantInit(&InitProperties2[0].vValue);
    InitProperties2[0].dwPropertyID = SSPROP_INIT_ENCRYPT;
    InitProperties2[0].vValue.vt = VT_BSTR;
    InitProperties2[0].vValue.bstrVal = encrypt;
    InitProperties2[0].dwOptions = DBPROPOPTIONS_REQUIRED;
    InitProperties2[0].colid = DB_NULLID;

    // Now that properties are set, construct the DBPROPSET structure
    // (rgInitPropSet).  The DBPROPSET structure is used to pass an array
    // of DBPROP structures (InitProperties) to SetProperties method.
    rgInitPropSet[0].guidPropertySet = DBPROPSET_DBINIT;
    rgInitPropSet[0].cProperties = nInitProps1;
    rgInitPropSet[0].rgProperties = InitProperties1;

    rgInitPropSet[1].guidPropertySet = DBPROPSET_SQLSERVERDBINIT;
    rgInitPropSet[1].cProperties = nInitProps2;
    rgInitPropSet[1].rgProperties = InitProperties2;

    // Set initialization properties.
    CComPtr<IDBProperties> pIDBProperties;
    hr = pIDBInitialize->QueryInterface(IID_IDBProperties,
                                        reinterpret_cast<LPVOID *>(&pIDBProperties));
    if (FAILED(hr))
    {
        std::cout << "Failed to obtain IDBProperties interface.\n";
        return hr;
    }

    hr = pIDBProperties->SetProperties(nPropSets, rgInitPropSet);
    if (FAILED(hr)) {
        std::cout << "Failed to set initialization properties\n";
        return hr;
    }

    // Now we establish connection to the data source.
    if (FAILED(hr = pIDBInitialize->Initialize())) {
        std::cout << "Problem in initializing\n";
    }

    return hr;
}

HRESULT ExecuteFunction(const CComPtr<IDBInitialize>& pIDBInitialize)
{
    HRESULT hr = S_OK;

    CComPtr<IDBCreateSession> pIDBCreateSession;
    // Let us create a new session from the data source object.
    if (FAILED(hr = pIDBInitialize->QueryInterface(IID_IDBCreateSession,
                                                   reinterpret_cast<LPVOID *>(&pIDBCreateSession))))
    {
        std::cout << "Failed to access IDBCreateSession interface\n";
        return hr;
    }

    CComPtr<IDBCreateCommand> pIDBCreateCommand;
    if (FAILED(hr = pIDBCreateSession->CreateSession(NULL,
                                                     IID_IDBCreateCommand,
                                                     reinterpret_cast<IUnknown **>(&pIDBCreateCommand))))
    {
        std::cout << "Failed to obtain IDBCreateCommand interface\n";
        return hr;
    }

    // Create a Command
    CComPtr<ICommandText> pICommandText;
    if (FAILED(hr = pIDBCreateCommand->CreateCommand(NULL,
                                                     IID_ICommandText,
                                                     reinterpret_cast<IUnknown **>(&pICommandText))))
    {
        std::cout << "Failed to access ICommand interface\n";
        return hr;
    }

    // The following buffer is used to store parameter values.
    typedef struct tagSPROCPARAMS
    {
        long lReturnValue;
        long inParam1;
        long inParam2;
    } SPROCPARAMS;

    // Set the command text.
    if (FAILED(hr = pICommandText->SetCommandText(DBGUID_DBSQL, L"{? = CALL fn_RectangleArea(?, ?) }")))
    {
        std::cout << "Failed to set command text\n";
        return hr;
    }

    // Set the parameters information.
    CComPtr<ICommandWithParameters> pICommandWithParams;
    if (FAILED(hr = pICommandText->QueryInterface(IID_ICommandWithParameters,
                                                  reinterpret_cast<LPVOID *>(&pICommandWithParams))))
    {
        std::cout << "Failed to obtain ICommandWithParameters\n";
        return hr;
    }

    const ULONG nParams = 3;   // No. of parameters in the command
    DBPARAMBINDINFO ParamBindInfo[nParams] = {};
    DB_UPARAMS ParamOrdinals[nParams] = {};
    DBROWCOUNT cNumRows = 0;
    
    // Describe the command parameters (parameter name, provider specific name
    // of the parameter's data type etc.) in an array of DBPARAMBINDINFO
    // structures.  This information is then used by SetParameterInfo().
    ParamBindInfo[0].pwszDataSourceType = const_cast<LPOLESTR>(L"DBTYPE_I4");
    ParamBindInfo[0].pwszName = NULL;
    ParamBindInfo[0].ulParamSize = sizeof(long);
    ParamBindInfo[0].dwFlags = DBPARAMFLAGS_ISOUTPUT;
    ParamBindInfo[0].bPrecision = 11;
    ParamBindInfo[0].bScale = 0;
    ParamOrdinals[0] = 1;

    ParamBindInfo[1].pwszDataSourceType = const_cast<LPOLESTR>(L"DBTYPE_I4");
    ParamBindInfo[1].pwszName = NULL;   // L"@inparam1";
    ParamBindInfo[1].ulParamSize = sizeof(long);
    ParamBindInfo[1].dwFlags = DBPARAMFLAGS_ISINPUT;
    ParamBindInfo[1].bPrecision = 11;
    ParamBindInfo[1].bScale = 0;
    ParamOrdinals[1] = 2;

    ParamBindInfo[2].pwszDataSourceType = const_cast<LPOLESTR>(L"DBTYPE_I4");
    ParamBindInfo[2].pwszName = NULL;   // L"@inparam2";
    ParamBindInfo[2].ulParamSize = sizeof(long);
    ParamBindInfo[2].dwFlags = DBPARAMFLAGS_ISINPUT;
    ParamBindInfo[2].bPrecision = 11;
    ParamBindInfo[2].bScale = 0;
    ParamOrdinals[2] = 3;

    if (FAILED(hr = pICommandWithParams->SetParameterInfo(nParams,
                                                          ParamOrdinals,
                                                          ParamBindInfo)))
    {
        std::cout << "Failed in setting parameter info.(SetParameterInfo)\n";
        return hr;
    }

    HACCESSOR hAccessor = 0;
    SPROCPARAMS sprocparams = {0,5,10};

    // Declare array of DBBINDING structures, one for each parameter in the command
    DBBINDING acDBBinding[nParams] = {};
    
    // Describe the consumer buffer; initialize the array of DBBINDING structures.
    // Each binding associates a single parameter to the consumer's buffer.
    for (ULONG i = 0; i < nParams; i++)
    {
        acDBBinding[i].obLength = 0;
        acDBBinding[i].obStatus = 0;
        acDBBinding[i].pTypeInfo = NULL;
        acDBBinding[i].pObject = NULL;
        acDBBinding[i].pBindExt = NULL;
        acDBBinding[i].dwPart = DBPART_VALUE;
        acDBBinding[i].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
        acDBBinding[i].dwFlags = 0;
        acDBBinding[i].bScale = 0;
    }   // for

    acDBBinding[0].iOrdinal = 1;
    acDBBinding[0].obValue = offsetof(SPROCPARAMS, lReturnValue);
    acDBBinding[0].eParamIO = DBPARAMIO_OUTPUT;
    acDBBinding[0].cbMaxLen = sizeof(long);
    acDBBinding[0].wType = DBTYPE_I4;
    acDBBinding[0].bPrecision = 11;

    acDBBinding[1].iOrdinal = 2;
    acDBBinding[1].obValue = offsetof(SPROCPARAMS, inParam1);
    acDBBinding[1].eParamIO = DBPARAMIO_INPUT;
    acDBBinding[1].cbMaxLen = sizeof(long);
    acDBBinding[1].wType = DBTYPE_I4;
    acDBBinding[1].bPrecision = 11;

    acDBBinding[2].iOrdinal = 3;
    acDBBinding[2].obValue = offsetof(SPROCPARAMS, inParam2);
    acDBBinding[2].eParamIO = DBPARAMIO_INPUT;
    acDBBinding[2].cbMaxLen = sizeof(long);
    acDBBinding[2].wType = DBTYPE_I4;
    acDBBinding[2].bPrecision = 11;

    // Let us create an accessor from the above set of bindings.
    CComPtr<IAccessor> pIAccessor;
    hr = pICommandWithParams->QueryInterface(IID_IAccessor,
                                             reinterpret_cast<LPVOID *>(&pIAccessor));
    if (FAILED(hr))
    {
        std::cout << "Failed to get IAccessor interface\n";
        return hr;
    }

    DBBINDSTATUS acDBBindStatus[nParams] = {};
    hr = pIAccessor->CreateAccessor(DBACCESSOR_PARAMETERDATA,
                                    nParams,
                                    acDBBinding,
                                    sizeof(SPROCPARAMS),
                                    &hAccessor,
                                    acDBBindStatus);
    if (FAILED(hr))
    {
        std::cout << "Failed to create accessor for the defined parameters\n";
        return hr;
    }

    // Initialize DBPARAMS structure for command execution. DBPARAMS specifies the
    // parameter values in the command.  DBPARAMS is then passed to Execute.
    DBPARAMS Params = {nullptr, 0, 0};
    Params.pData = &sprocparams;
    Params.cParamSets = 1;
    Params.hAccessor = hAccessor;

    // Execute the command.
    if (SUCCEEDED(hr = pICommandText->Execute(nullptr,
                                        IID_NULL,
                                        &Params,
                                        &cNumRows,
                                        nullptr)))
    {
        printf("Return value = %d\n", sprocparams.lReturnValue);
    }
    else 
    {
        std::cout << "Failed to execute command\n";
    }

    // Release memory.
    pIAccessor->ReleaseAccessor(hAccessor, nullptr); 
    return hr;
}

drop function fn_RectangleArea  
go  

참고 항목

결과 처리 방법 도움말 항목(OLE DB)