执行存储过程（使用 ODBC CALL 语法）以及处理返回代码和输出参数 (OLE DB)

项目
05/08/2013

SQL Server 存储过程可具有整数返回代码和输出参数。返回代码和输出参数位于从服务器发送的最后一个数据包中，因此直到行集完全释放时它们才可供应用程序使用。如果命令返回多个结果，则输出参数数据在 IMultipleResults::GetResult 返回 DB_S_NORESULT 时或 IMultipleResults 接口完全释放时（以二者中最先发生的为准）可用。

安全说明
请尽可能使用 Windows 身份验证。如果 Windows 身份验证不可用，请在运行时提示用户输入其凭据。不要将凭据存储在一个文件中。如果必须保存凭据，则应当用 Win32 Crypto API（Win32 加密 API）进行加密。

处理返回代码和输出参数

构造使用 ODBC CALL 转义序列的 SQL 语句。该语句应当对每个输入/输出和输出参数以及过程返回值（如果有）使用参数标记。对于输入参数值，可以使用参数标记或对值进行硬编码。
通过使用 DBBINDING 结构数组创建一组绑定（每个参数标记一个）。
通过使用 IAccessor::CreateAccessor 方法为定义的参数创建取值函数。 CreateAccessor 将从一组绑定创建取值函数。
填写 DBPARAMS 结构。
调用 Execute 命令（在这种情况下是调用存储过程）。
通过使用 IRowset::Release 方法处理行集并释放它。
处理从存储过程接收的返回代码和输出参数值。

示例

此示例说明了如何处理行集、返回代码和输出参数。不处理结果集。 IA64 平台不支持此示例。

此示例要求使用 AdventureWorks 示例数据库，可以从 Microsoft SQL Server Samples and Community Projects（Microsoft SQL Server 示例和社区项目）主页下载该数据库。

执行第一个 (Transact-SQL) 代码列表，以创建该应用程序要使用的存储过程。

使用 ole32.lib 和 oleaut32.lib 编译并执行第二个 (C++) 代码列表。此应用程序连接到您的计算机上默认的 SQL Server 实例。在某些 Windows 操作系统上，您需要将 (localhost) 或 (local) 更改为您的 SQL Server 实例的名称。若要连接到命名实例，请将连接字符串从 L"(local)" 更改为 L"(local)\\name"，其中 name 是命名实例。默认情况下，SQL Server Express 安装在命名实例中。请确保您的 INCLUDE 环境变量包括含有 sqlncli.h 的目录。

执行第三个 (Transact-SQL) 代码列表，以删除该应用程序使用的存储过程。

USE AdventureWorks
if exists (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[myProc]'))
   DROP PROCEDURE myProc
GO

CREATE PROCEDURE myProc 
    @inparam nvarchar(5),,
    @outparam int OUTPUT

AS
SELECT Color, ListPrice 
FROM Production.Product WHERE Size > @inparam
SELECT @outparam = 100

IF  (@outparam > 0)
    RETURN 999
ELSE
    RETURN 888
GO

// compile with: ole32.lib oleaut32.lib
void InitializeAndEstablishConnection();

#define UNICODE
#define DBINITCONSTANTS
#define INITGUID
#define OLEDBVER 0x0250   // to include correct interfaces

#include <windows.h>
#include <stdio.h>
#include <stddef.h>
#include <iostream>
#include <oledb.h>
#include <oledberr.h>
#include <SQLNCLI.h>

using namespace std;

IDBInitialize* pIDBInitialize = NULL;
IDBCreateSession* pIDBCreateSession = NULL;
IDBCreateCommand* pIDBCreateCommand = NULL;
ICommandText* pICommandText = NULL;

IRowset* pIRowset = NULL;
ICommandWithParameters* pICommandWithParams = NULL;
IAccessor* pIAccessor = NULL;
IDBProperties* pIDBProperties = NULL;

WCHAR* pStringsBuffer;
DBBINDING* pBindings;
const ULONG nInitProps = 4;
DBPROP InitProperties[nInitProps];

const ULONG nPropSet = 1;
DBPROPSET rgInitPropSet[nPropSet];
HRESULT hr;
HACCESSOR hAccessor;

const ULONG nParams = 3;   // Number of parameters in the command
DBPARAMBINDINFO ParamBindInfo[nParams];
ULONG i;
ULONG cbColOffset = 0;

ULONG ParamOrdinals[nParams];
DBROWCOUNT cNumRows = 0;
DBPARAMS Params;

// Declare an array of DBBINDING structures, one for each parameter in the command.
DBBINDING acDBBinding[nParams];
DBBINDSTATUS acDBBindStatus[nParams];

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

int main() {
   // The command to execute.
   WCHAR* wCmdString = L"{? = call myProc(?,?)}";

   SPROCPARAMS sprocparams = {0,0,14};

   // All the initialization activities in a separate function.
   InitializeAndEstablishConnection();

   // Create a new activity from the data source object.
   if ( FAILED(pIDBInitialize->QueryInterface( IID_IDBCreateSession, 
      (void**) &pIDBCreateSession))) {
         cout << "Failed to access IDBCreateSession interface.\n";
         goto EXIT;
   }
   if (FAILED(pIDBCreateSession->CreateSession( NULL, IID_IDBCreateCommand, 
      (IUnknown**) &pIDBCreateCommand))) {
         cout << "pIDBCreateSession->CreateSession failed.\n";
      goto EXIT;
   }

   // Create a Command object.
   if (FAILED(pIDBCreateCommand->CreateCommand(NULL, IID_ICommandText, 
      (IUnknown**) &pICommandText))) {
         cout << "Failed to access ICommand interface.\n";
         goto EXIT;
   }

   // Set the command text.
   if (FAILED(pICommandText->SetCommandText(DBGUID_DBSQL, wCmdString))) {
      cout << "Failed to set command text.\n";
      goto EXIT;
   }
   // No need to describe command parameters (parameter name, data type
   // etc) in DBPARAMBINDINFO structure and then SetParameterInfo(). The
   // provider obtains this information by calling appropriate helper
   // function.

   // Describe the consumer buffer by filling in the array of DBBINDING structures.  
   // Each binding associates a single parameter to the consumer's buffer.
   for ( 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;
   }   // end 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, inParam);
   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, outParam);
   acDBBinding[2].eParamIO = DBPARAMIO_OUTPUT;
   acDBBinding[2].cbMaxLen = sizeof(long);
   acDBBinding[2].wType = DBTYPE_I4;
   acDBBinding[2].bPrecision = 11;

   // Create an accessor from the above set of bindings.
   hr = pICommandText->QueryInterface( IID_IAccessor, (void**)&pIAccessor);
   if (FAILED(hr))
      cout << "Failed to get IAccessor interface.\n";

   hr = pIAccessor->CreateAccessor( DBACCESSOR_PARAMETERDATA, 
                                    nParams,       
                                    acDBBinding, 
                                    sizeof(SPROCPARAMS), 
                                    &hAccessor,
                                    acDBBindStatus);
   if (FAILED(hr))
      cout << "Failed to create accessor for the defined parameters.\n";

   // Fill in DBPARAMS structure for the command execution. This structure 
   // specifies the parameter values in the command and is then passed to Execute.
   Params.pData = &sprocparams;
   Params.cParamSets = 1;
   Params.hAccessor = hAccessor;

   // Execute the command.
   if ( FAILED(hr = pICommandText->Execute( NULL, 
                                            IID_IRowset, 
                                            &Params, 
                                            &cNumRows, 
                                            (IUnknown **) &pIRowset))) {
      cout << "Failed to execute command.\n";
      goto EXIT;
   }

   printf("After command execution but before rowset processing.\n\n");
   printf("  Return value = %d\n", sprocparams.lReturnValue);
   printf("  Output parameter value = %d\n", sprocparams.outParam);
   printf("  These are the same default values set in the application.\n\n\n");

   // Result set is not important in this example; release it without processing.
   pIRowset->Release();

   printf("After processing the result set...\n");
   printf("  Return value = %d\n", sprocparams.lReturnValue);
   printf("  Output parameter value = %d\n\n", sprocparams.outParam);

   // Release memory.
   pIAccessor->ReleaseAccessor(hAccessor, NULL);
   pIAccessor->Release();
   pICommandText->Release();
   pIDBCreateCommand->Release();
   pIDBCreateSession->Release();    
   if (FAILED(pIDBInitialize->Uninitialize()))
      // Uninitialize is not required, but it fails if an interface
      // has not been released.  This can be used for debugging.
      cout << "Problem uninitializing.\n";

   pIDBInitialize->Release();

   CoUninitialize();
   return 0;

EXIT:
   if (pIAccessor != NULL)
      pIAccessor->Release();
   if (pICommandText != NULL)
      pICommandText->Release();
   if (pIDBCreateCommand != NULL)
      pIDBCreateCommand->Release();
   if (pIDBCreateSession != NULL)
      pIDBCreateSession->Release();
   if (pIDBInitialize != NULL)
      if (FAILED(pIDBInitialize->Uninitialize()))
         // Uninitialize is not required, but it fails if an
         // interface has not been released.  This can be used for debugging.
         cout << "Problem in uninitializing.\n";
      pIDBInitialize->Release();

   CoUninitialize();
};

void InitializeAndEstablishConnection() {    
   // Initialize the COM library.
   CoInitialize(NULL);

   // Obtain access to the SQL Server Native Client OLE DB provider.    
   hr = CoCreateInstance( CLSID_SQLNCLI11, 
                          NULL, 
                          CLSCTX_INPROC_SERVER,
                          IID_IDBInitialize, 
                          (void **) &pIDBInitialize);
   if (FAILED(hr))
      cout << "Failed in CoCreateInstance().\n";

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

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

   // Replace "MySqlServer" with proper value.
   InitProperties[0].vValue.bstrVal = SysAllocString(L"(local)");
   InitProperties[0].dwOptions = DBPROPOPTIONS_REQUIRED;
   InitProperties[0].colid = DB_NULLID;

   // Specify database name.
   InitProperties[1].dwPropertyID = DBPROP_INIT_CATALOG;
   InitProperties[1].vValue.vt = VT_BSTR;
   InitProperties[1].vValue.bstrVal = SysAllocString(L"AdventureWorks");
   InitProperties[1].dwOptions = DBPROPOPTIONS_REQUIRED;
   InitProperties[1].colid = DB_NULLID;

   InitProperties[2].dwPropertyID = DBPROP_AUTH_INTEGRATED;
   InitProperties[2].vValue.vt = VT_BSTR;
   InitProperties[2].vValue.bstrVal = SysAllocString(L"SSPI");
   InitProperties[2].dwOptions = DBPROPOPTIONS_REQUIRED;
   InitProperties[2].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 the SetProperties method.
   rgInitPropSet[0].guidPropertySet = DBPROPSET_DBINIT;
   rgInitPropSet[0].cProperties = 4;
   rgInitPropSet[0].rgProperties = InitProperties;

   // Set initialization properties.
   hr = pIDBInitialize->QueryInterface(IID_IDBProperties, (void **)&pIDBProperties);
   if (FAILED(hr))
      cout << "Failed to obtain IDBProperties interface.\n";

   hr = pIDBProperties->SetProperties(nPropSet, rgInitPropSet);
   if (FAILED(hr))
      cout << "Failed to set initialization properties.\n";

   pIDBProperties->Release();

   // Now establish a connection to the data source.
   if (FAILED(pIDBInitialize->Initialize()))
      cout << "Problem in initializing.\n";
}

USE AdventureWorks
DROP PROCEDURE myProc
GO

请参阅

概念

处理结果操作指南主题 (OLE DB)

通过

执行存储过程（使用 ODBC CALL 语法）以及处理返回代码和输出参数 (OLE DB)

处理返回代码和输出参数

示例

请参阅

概念

其他资源