執行預存程序 (使用 ODBC CALL 語法) 及處理傳回碼和輸出參數 (OLE DB)

  • 發行項

SQL Server 預存程序可以有整數傳回碼和輸出參數。 傳回碼和輸出參數會在來自伺服器的最後一個封包中傳送，因此要等到完全釋放資料列集之後才可供應用程式使用。 如果此命令傳回多個結果，則當 IMultipleResults::GetResult 傳回 DB_S_NORESULT 或是當 IMultipleResults 介面完全釋放時 (以先發生者為準)，便可使用輸出參數資料。

安全性注意事項安全性注意事項

盡可能使用 Windows 驗證。 如果無法使用 Windows 驗證，請提示使用者在執行階段輸入認證。 請避免將認證儲存在檔案中。 如果您必須保存認證，則應該用 Win32 Crypto API 加密這些認證。

若要處理傳回碼和輸出參數

  1. 建構使用 ODBC CALL 逸出序列的 SQL 陳述式。 此陳述式應該會針對每個輸入/輸出和輸出參數，以及程序傳回值 (若有) 使用參數標記。 對於輸入參數，您可以使用參數標記，或將程式碼寫入值。

  2. 使用 DBBINDING 結構的陣列來建立一組繫結 (每一個參數標記各一個)。

  3. 使用 IAccessor::CreateAccessor 方法來建立已定義之參數的存取子。 CreateAccessor 會從一組繫結建立存取子。

  4. 填入 DBPARAMS 結構。

  5. 呼叫 Execute 命令 (在此情況下，為預存程序的呼叫)。

  6. 處理資料列集，並使用 IRowset::Release 方法將它釋放。

  7. 處理從預存程序收到的傳回碼和輸出參數值。

範例

此範例示範如何處理資料列集、傳回碼和輸出參數。 並不會處理結果集。 IA64 不支援此範例。

此範例需要 AdventureWorks 範例資料庫，您可以從 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)