用COM自動服務擴充SQL Server的功能

server     SQL Server的T-SQL程式設計語言在資料存放區和恢複方面功能強大，但在與SQL Server資料庫之外的系統互動方面則功能較弱。然而，我們可以通過SQL Server內建的COM自動作業環境來克服這個限制，SQL Server內建的COM自動作業環境可以使使用者在預存程序中自動操作COM對象。

　　在SQL Server 7.0和SQL Server 6.5中提供了7個擴充的預存程序，可以通過自己開發的或Office等現成的COM對象擴充SQL Server的功能。SQL Server還提供了一種錯誤處理機制，可以把出錯資訊寫到SQL代理日誌中。利用COM自動化操作服務，還可以把SQL Server與微軟的Exchange Server、Index Server和其他可以通過COM自動化操作服務控制其他軟體進行集SQL Server 6.5引進了對象自動作業環境，它最初被稱作OLE。隨著時間的變遷對象操作的名稱也有所變化，然而與SQL Server 6.5相比，SQL Server 7.0中的自動作業環境沒有改變，因此微軟的文檔中仍然把這一功能稱作OLE操作而不是COM操作，在查閱SQL Server線上手冊（BOL）時尤其需要注意這一點。下面我們來討論如何使用SQL Server的COM自動操作預存程序以及COM自動操作如何協助我們解決現實的編程問題。

　　COM操作的細節

　　表1列出了SQL Server中的7個用於COM操作的擴充預存程序。當自動操作一個COM對象時，需要首先通過調用sp_OACreate建立一個COM對象的執行個體，然後通過一系列的sp_OAGetProperty、sp_OASetProperty和sp_OAMethod調用完成需要完成的任務，在完成對COM對象的操作後，還需要調用sp_OADestroy釋放該對象。在詳細地研究每個儲存過程時，請注意二個很重要的問題。

　　第一，必須提供調用的所有參數，因為自動操作功能不支援有名參數，如果不能使用一個詳細的參數，需要向它傳遞一個NULL作為預留位置；第二，每個調用返回一個整數類型的HRESULT，如果調用成功則該值為0。在後面，我們將討論如何處理傳回值為非。

　　預存程序 描述

　　sp_OACreate 建立自動操作對象的一個執行個體

　　sp_OADestroy 釋放一個對象的執行個體

　　sp_OAGetErrorInfo 從其他過程返回的HRESULT中獲得錯誤描述資訊

　　sp_OAGetProperty 把一個對象的屬性儲存區在結果集或局部變數中

　　sp_OASetProperty 改變一個對象屬性的值

　　sp_OAMethod 執行對象的方法，向方法傳遞參數，並得到傳回值

　　sp_OAStop 關閉SQL Server的自動作業環境

　　表 1: SQL Server的COM自動操作預存程序

　　COM操作必須以調用sp_OACreate預存程序開始，文法格式如下所示：

　　sp_OACreate progid | clsid, objecttoken OUT.PUT,

第一個參數是程式ID（ProgID━━一個應用程式名稱.類名形式的字串，例如：

　　Excel.Application，）或者一個類ID（CLSID━━一個nnnnnnnn-nnnn-nnnn-nnnn-nnnnnnnnnnnn形式的全球唯一的ID），它標明你希望建立執行個體的COM對象。

　　在可能的情況下，我建議使用ProgID參數，因為它易於輸入和記憶。你會發現，只有很少的希望自動操作的對象沒蠵rogID，如果偶爾碰上這樣的對象，就只有使用CLSID了。第二個變數objecttoken也是一個整型變數，一個對象的標記是指向SQL Server建立的對象的控制代碼和指標，我們需要在隨後的對對象的自動操作中使用這個返回的對象標記來確定這個對象。最後的context變數是可選的，可以強迫建立的對象使用某種自動操作的機制。如果其值為1，則要求對象在一個ActiveX DLL檔案中；值為4，則要求對象在ActiveX EXE伺服器中；如果是預設的值5，則可以使用任一自動操作。在這裡我們建議使用預設的選項，而無須為context參數提供一個恰當的值。下面調用op_OACreate 的命令將建立一個微軟的Excel程式的執行個體：

Declare @Object int

Declare @RetVal int

Exec @RetVal=sp_OACreate 'Excel.Application',

@Object OUTPUT

　　在建立一個對象後，需要擷取其一些屬性。要得到這些屬性，可以通過下面的文法調

　　sp_OAGetProperty：

　　sp_OAGetProperty objecttoken, propertyname[, propertyvalue OUTPUT] [,第一個參數objecttoken的值就是由sp_OACreate返回的值，參數Propertyname是我們希望擷取的屬性。

　　在擷取這個值是有幾種選擇，如果該屬性是一個單一的值，可以把它儲存在一個變數中，或者把它作為一個單行、單欄位的結果集；如果屬性值是一個一維或二維的數組，則必須把它作為一個結果集；如果如果該屬性的值是一個多於二維的數組，sp_OAGetProperty就不能返回它的值，會出現一個錯誤。要返回一個結果集，只須簡單地不指定propertyvalue參數的值即可（如果需要它有一個值以便使用index參數，就把NULL賦給它好了。

　　否則的話，應該賦給propertyvalue一個適當的類型的值，並且一定要把該參數標記為OUTPUT。如果你訪問的屬性是一個集合，就需要使用index參數指定這個集合中一個特定的數字。如果一個對象的屬性是另一個對象，就應該把這個對象存入一個整數型變數中，sp_OAGetProperty返回的也是一個對象標記，不過與sp_OACreate返回的並不相同。我們可以使用這個對象標記對預存程序返回的任何對象進行自動化操作。下面的命令調用sp_OAGetProperty把一個名字為DefaultFilePath的屬性值存入變數@DFP中：

　　Exec sp_OAGetProperty @Object, 'DefaultFilePath',

　　@DFP OUTPUT

　　可以通過如下格式使用sp_OASetProperty預存程序改變一個對象的屬性值：

　　sp_OASetProperty objecttoken, propertyname,newvalue [, index]

第一個參數objecttoken是由sp_OACreate返回的，參數Propertyname是要改變的對象的屬性名稱字，Newvalue參數是想賦給屬性的新變數，可以是一個變數或一個文字值。如果設定的屬性值是作為一個集合的一個對象，可以使用可選的index參數來指定這個集合的一個特定的位置。下面的命令調用sp_OASetProperty把名字為FixedDecimalPlaces的屬性設定為6：Exec sp_OASetProperty @Object, 'FixedDecimalPlaces', 6

　　可以用下面的文法調用sp_OAMethod預存程序執行一個對象的方法：

　　sp_OAMethod objecttoken, methodname [, returnvalue OUTPUT] [, 

　　Sp_OAMethod是最靈活的，因而也是最複雜的自動操作預存程序，我們甚至可以用它象調用一個方法那樣調用一個屬性，而且還能得到一個傳回值，當然，我們也能使用sp_OAGetProperty來完成這一任務。該預存程序的第一個參數objecttoken是由sp_OACreate返回的對象標記，參數methodname是希望執行的方法的名字，如果該方法有傳回值，則下一個參數returnvalue應當是一個包含該方法傳回值的適當類型的變數；如果傳回值是一個一維或二維的數組，則用NULL作為一個預留位置，該過程將返回一個結果集。該預存程序不能返回一個超過二維的數組作為結果集合，在這種情況下，SQL Server就會出錯。如果該方法沒有傳回型別。

　　如果調用的方法需要參數，就需要在調用sp_OAMethod時提供這些參數。如果方法允許按順序提供參數，則按要求的順序列出每個參數，並用逗號分隔每個參數，還可以用變數或文字變數作為參數。如果需要使用有名參數，SQL Server也提供了相應的機制，只需使用：@變數名=變數值 的形式列出所需的變數即可。需要注意的是不要因為有@首碼而把變數名當作局部變數，當調用預存程序sp_OAMethod時，SQL Server就會解析出@，因此，即使在調用的方法中有名字為HostName的參數時，仍然可以使用名字為@HostName的局部變數。

　　下面是二個調用sp_OAMethod的例子。第一個例子調用一個名字為CentimetersToPoints的方法，它只接受在@CMVal變數中提供的一個參數，返回的值儲存在變數@RetVal中。第二個例子調用一個名字為MailLogon的方法，它接受三個可選的變數，這個例子中根據名字接受二個變數，把Name設定為字串"MyUserName"，把 Password設定為字串：

　　Exec sp_OAMethod @Object, 'CentimetersToPoints',@RetVal OUTPUT, @CMVal

　　Exec sp_OAMethod @Object, 'MailLogon', NULL,@Name='MyUserName',

　　不再使用一個對象後，需要通過下面的文法調用預存程序sp_OADestroy釋放對該對象的引sp_OADestroy objecttoken

調用sp_OADestroy預存程序可以釋放由參數objecttoken指定的對象，同時還釋放這個對象所使用的記憶體和其他資源。下面是一個調用sp_OADestroy的命令：

　　Exec sp_OADestroy @Object

　　需要注意的是，T-SQL中的資料類型與其他的程式設計語言並非是一一對應的，在調用一個需要特定的資料類型的方法時就可能出錯。"資料類型轉換"工具條可以將SQL Server的資料。

　　錯誤處理

　　象在前面提到的那樣，如果對預存程序的調用成功了，則會返回一個為0的HRESULT值，其他的HRESULT值則意味著發生了錯誤。要判斷一個非零的HRESULT值，可以把HRESULT值傳：

　　sp_OAGetErrorInfo [objecttoken] [, source OUTPUT] [, description OUTPUT]

　　第一個參數objecttoken是由sp_OACreate返回的對象標記。

　　下面的四個參數返回錯誤資訊。Source是產生這一錯誤資訊的應用程式或庫，Description是該錯誤的描述，如果有協助檔案的話，則該Helpfile是協助檔案的路徑。這三個參數都是有符號或無符號字元型資料，sp_OAGetErrorInfo會根據定義的變數的大小截取返回的值。最後一個參數helpid是特定錯誤在協助檔案中的索引號。下面的命令調用sp_OAGetErrorInfo以獲得某一個錯誤的更詳細的資訊：

Declare @Source varchar(100), @Description varchar(255), @HelpFile

Exec sp_OAGetErrorInfo @Object, @Source OUTPUT, @Description OUTPUT,

　　SQL Server線上手冊還提供了一個有關sp_DisplayOAErrorInfo預存程序的例子，該預存程序可以調用sp_OAGetErrorInfo把返回的值組織成格式化的字串，以便把該資訊寫入記錄檔中。

　　關於sp_DisplayOAErrorInfo的更詳細的資訊，請參閱工具條， 另外，調用sp_OAStop儲存過程可以關閉SQL Server的COM自動作業環境，它無需任何參數。關閉自動作業環境在大多數情況下並非是必需的，第一次調用sp_OACreate時自動作業環境會自動開啟，SQL Server關閉時自動作業環境也會自動關閉。如果一個預存程序正在對一個對象進行自動操作，而另一個程序呼叫sp_OAStop時就會出現錯誤，因此我們不建議在程式中調用sp_OAStop，只有在調試一個沒有啟動並執行過程時，才可以通過一個查詢時段調用它。

在實際工作中使用COM自動操作

　　至此，我們已經學習了如何使用每一個COM自動操作預存程序，我們現在來討論一下一個綜合應用它們的例子。程式清單1是一個名字為sp_OpenWordIfCoProcAvailable的過程，在這個過程中，我們用sp_OACreate建立了一個Microsoft Word的執行個體，然後使用sp_OAGetProperty來擷取Word的MathCoProcessorAvailable屬性，如果sp_OAGetProperty返回1，則sp_OpenWordIfCoProcAvailable向調用過程返回Word對象的對象標記；否則，

　　sp_OpenWordIfCoProcAvailable關閉Word，並返回0。為了節省版面，我們只調用了出錯處理過程一次，在實際應用中，應該在每次調用自動操作預存程序後都調用出錯處理過程。注意，為對Word進行自動操作，應該在安裝SQL Server的機器上安裝Word。

　　程式清單 1：自動操作Word的方法的例子

Create Procedure sp_OpenWordIfCoProcAvailable As

Declare @Object int, @hr int, @RetVal int 
Exec @hr = sp_OACreate 'Word.Application', @Object OUTPUT 
BEGIN 

Exec sp_DisplayOAErrorInfo @Object, @hr 
Return 0

END

Exec @hr = sp_OAGetProperty @Object, 'MathCoProcessorAvailable', @RetVal

If @hr=0 

BEGIN 

Exec @hr = sp_OAMethod @Object, 'Quit', 0 

Exec @hr = sp_OADestroy @Object 

Return 0

END

Exec @hr = sp_OAMethod @Object, 'Activate' 
Return @Object

　　如果需要對一個使用Visual Basic編寫的COM對象進行自動操作，調試它與SQL Server之間的互通性是一件相當容易的事。我們需要在運行SQL Server的機器上安裝有Visual Basic，在Visual Basic的編輯器中載入COM項目，設定一些斷點，然後編譯並運行該COM對象。在有預存程序對該對象進行自動操作時，在運行到一個斷點時，編輯器就會自動切換到偵錯模式，我們就可以象調試其他的Visual Basic程式那樣調試這個COM對象。如果要對調試過程實施更多的控制，可以使用T-SQL Debugger for VB外掛程式，它能採用步進方式執行存。

　　此外，在SQL Server中應用COM自動操作我們還能作什麼呢？下面是我曾使用SQL Server強大的COM自動操作功能的實際例子。前不久，我需要從一個SQL Server預存程序中使用一個通過具名管道進行通訊，而SQL Server中沒有提供通過編程方式開啟和使用具名管道的機制，我正好有一個可以使用具名管道通訊的VB例和庫，因此就把這個庫檔案作成一個類，並建立了一個ActiveX DLL檔案，然後從預存程序中對DLL進行自動操作。

　　另一次，我需要複製一些檔案和資料庫表。使用SQL Server的複製功能可以很方便地複製這些資料，但複製檔案則要難得多，NT的目錄同步功能很弱，不能滿足要求。儘管我還可以把拷貝命令存到字元變數中，然後把變數傳遞給xp_cmdshell，但會遇到命令列長度的限制。更不方便的是，如果在拷貝過程中發生了錯誤，我不能很方便地判斷錯誤發生在什麼地方，因此，我就編寫了一個ActiveX DLL，並通過自動操作它來處理檔案的拷貝工作。

　　還有一次，我需要在SQL Server 6.5和Index Server 2.0之間先執行連結後再完成查詢任務，如果使用帶ADO的Windows 2000 Indexing Services和SQL Server 7.0，完成這樣的工作非常簡單，但如果不是使用這些產品，則要困難得多。

　　首先，需要編寫一個可以執行Index Server查詢對象ixsso.dll的ActiveX DLL，對它進行自動操作，從Index Server目錄中獲得資訊，並通過一個方法將資訊返回到預存程序中。

　　然後把這些資料儲存到一個暫存資料表中，再對它進行連接操作。COM自動操作再一次幫我解決了問題。在預存程序中執行COM自動操作幾乎可以使我們完成任何想完成的操作。SQL Server 2000中的COM自動操作沒有什麼變化，因此採用這種方法編寫的代碼在將來仍然可以使用下去。