在sql server的存储过程中调用com组件

有时候，出于sql server本身的功能不足，我们需要用外部的程序来实现，而com由于其语言的无关性和强大的功能就成为与sql server接口的首选，并且sql server本身也提供了这方面的系统存储过程用以实现与com的接口。这几个相关的系统存储过程为：  
         sp_oacreate　在 microsoft® sql server™ 执行个体建立 ole 对象的执行个体。  
         sp_oamethod　呼叫 ole 对象的方法  
         sp_oagetproperty　取得 ole 对象的属性值  
         sp_oasetproperty　将 ole 对象属性设定为新值。  
         sp_oageterrorinfo　获得 ole automation 错误信息  
         sp_oastop　停止整个服务器 ole automation 预存程序执行环境。  
         sp_oadestroy　销毁建立的 ole 对象。 
         知道了实现的可能性，那么到底该如何实现呢？下面请跟我来：
一、 其它编程语言建立com程序（以delphi为例）  
         1、 关闭所有项目，新建一个工程，将默认的单元文件unit1.pas保存为umain.pas，将默认的工程文件project1保存为ptestcom。当然你也可以保存为你想要的文件名，但要记住这里设定的工程名就是后面需调用的com名称；  
         2、 点击菜单file->new->other…，打开新建项目窗口，选择activex页，在其中找到automation object（因为sql server中要求被它调用的ole automation server须支持idispatch接口，所以选择它），然后点ok，在弹出的响导框中输入class name（这里输入testobject），其它保持不变，按ok完成；  
         3、 在以上步骤完成后，就会弹出一个名叫testcom的类型库窗口，在窗口的左侧我们就会看到上面所创建的类testobject和一个叫itestobject的接口类，选中itestobject，点击工具栏上的new method创建一个新的方法，命名为testfunc，然后在右边的parameters页上添加两个输入参数param1和param2（type为bstr，modifier为[in]）、一个输出参数paramout（type为variant *，modifier为[out, retval]），return type保持不变，为hresult。点一下工具栏上的刷新按钮，按保存，将它保存为utestcom.pas和utestcom.tlb，然后关闭窗口；  
         4、 此时，在utestcom单元文件中你将会看到已经产生了一个函数：  
         function ttestobject.testfunc(const param1,param2: widestring): olevariant;  
         begin  

         end;  
         在其中加上如下代码（当然你可以在此函数中使用你自己的方法来实现）：  
                 result := param1 + param2; //将传入两个字符串相加，然后返回  
         按保存，运行它，确认无误即可，如果有误，请检查以上步骤是否正确完成。

二、在sql server中的调用
         当创建好了com程序并确定无误后，我们就可以用上面提到的几个相关系统存储过程来调用了，下面请看实例，具体说明已包含在代码中，不再重述。


create procedure check_userathor         --定义一个存储过程，接受两个参数
@c_id varchar(20),
@c_name varchar(20)
as
declare @object int
declare @err int
declare @return_value varchar(255)
declare @src varchar(255), @desc varchar(255)

/**//*創建com實例,调用如上创建的ptestcom.testobject*/
exec @err = master..sp_oacreate "ptestcom.testobject", @object out

if @err <> 0      /**//* 創建失敗*/
begin
     
     exec sp_oageterrorinfo @object, @src out, @desc out  
     select hr=convert(varbinary(4),@err), source=@src, description=@desc
         return
end

/**//*調用如上声明的com方法testfunc,@return_value为返回值,@c_id与@c_name为输入参数*/
exec @err = sp_oamethod @object,‘testfunc‘,@return_value out,@c_id,@c_name
if @err <> 0
begin
     exec sp_oageterrorinfo @object, @src out, @desc out  
     select hr=convert(varbinary(4),@err), source=@src, description=@desc
     exec sp_oadestroy @object
     return
end

print ‘返回結果為：‘ +(@return_value)      --这里显示返回值

/**//*釋放com實例*/
exec sp_oadestroy @object

go

三、调用
         如上存储过程创建无误后，我们就可以直接拿来调用了，在如上的示例中，如果我们输入‘123‘,‘company‘，返回的结果就应该是123company这个字符串了。

四、推论
         以上的com程序其实适合于任何能与com进行通信的其它程序调用，比如在asp中，也可以直接调用com组件，方法一样简单，不再详述，如有兴趣可与我探讨。  

 --SQLServer的存储过程调用Com组件
/*--下面的部分在VB中完成 首先我们先用VB作一个最简单的组件 工程名称:testSQLCOM
类名:TestMath '函数,计算两个整数相加的结果
PublicFunctionAddMe(aAsLong,bAsLong)AsLong
AddMe=a b
EndFunction 编译生成后，我们就可以在SqlServer中对这个Com组件进行调用了
--*/ /*--下面是SQL中对上面DLL的调用--*/ --定义用到的变量
declare@errint,@srcvarchar(255),@descvarchar(255)
declare@objint,@reint --创建调用实例
exec@err=sp_OACreate'testSQLCOM.TestMath',@objout
if@err<>0gotolberr --如果创建失败,则进行错误处理 --调用DLL中的函数
exec@err=sp_OAMethod@obj,'AddMe',@reout,100,200
if@err<>0gotolberr --如果调用错误,则进行错误处理 print'返回的结果是:' str(@re) --完成后释放
execsp_OADestroy@obj return lberr:
execsp_oageterrorinfo0,@srcout,@descout
selectcast(@errasvarbinary(4))as错误号
,@srcas错误源,@descas错误描述

 

 

Sql-server里可以调用基于IDispatch的COM组件

有兴趣的可以自己去查SQL帮助里的sp_OACreate、sp_OAMethod、sp_OADestroy等存储过程的用法。

下面是我在一个短信报警的小项目里的一些sql代码,报警信息通过各类软件插入到sql-server里，然后通过触发器调用组件，并发送短信到指定手机上去，实现自动报警功能。

//测试数据库的触发器

ALTER TRIGGER message_Trigger1

ON dbo.message

FOR INSERT /*, UPDATE, DELETE */

AS

       /* IF UPDATE (column_name) ...*/

begin

       declare @PhoneNum nvarchar(50)

       declare @Content nvarchar(140)

       declare @MessageId nvarchar(70)

       declare @index int

       declare @hr int

       declare @object int

       select @PhoneNum = phone_num, @Content = Content, @MessageId = message_id from inserted

       select @index = 1

  &nbsp;    /*调用COM发送短信*/

 

       begin

              EXEC @hr = sp_OACreate '{26850DDA-862C-44FF-9232-282937F2CA4B}',@object OUT

              if @hr = 0

              begin

                     exec @hr=sp_OAMethod @object,'SendMsg',NULL,@Content,@PhoneNum,@index,@MessageId

                     exec sp_OADestroy @object

              end

       end

end