用COM自动服务扩展SQL 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。在后面，我们将讨论如何处理返回值为非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, 　　 　　　　[context:] 　　 　　　　　第一个参数是程序ID（ProgID━━一个应用程序名.类名形式的字符串，例如Excel.Application，）或者一个类ID（CLSID━━一个nnnnnnnn-nnnn-nnnn-nnnn-nnnnnnnnnnnn形式的全球唯一的ID），它标明你希望创建实例的COM对象。在可能的情况下，我建议使用ProgID参数，因为它易于输入和记忆。你会发现，只有很少的希望自动操作的对象没有ProgID，如果偶尔碰上这样的对象，就只有使用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] [, index] 　　 　　　　第一个参数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] [, [@parametername =] parameter [OUTPUT]...] 　　 　　Sp_OAMethod是最灵活的，因而也是最复杂的自动操作存储过程，我们甚至可以用它象调用一个方法那样调用一个属性，而且还能得到一个返回值，当然，我们也能使用sp_OAGetProperty来完成这一任务。该存储过程的第一个参数objecttoken是由sp_OACreate返回的对象标记，参数methodname是希望执行的方法的名字，如果该方法有返回值，则下一个参数returnvalue应当是一个包含该方法返回值的适当类型的变量；如果返回值是一个一维或二维的数组，则用NULL作为一个占位符，该过程将返回一个结果集。该存储过程不能返回一个超过二维的数组作为结果集合，在这种情况下，SQL Server就会出错。如果该方法没有返回类型，则使用NULL作为一个占位符。 　　 　　　　如果调用的方法需要参数，就需要在调用sp_OAMethod时提供这些参数。如果方法允许按顺序提供参数，则按要求的顺序列出每个参数，并用逗号分隔每个参数，还可以用变量或文字变量作为参数。如果需要使用有名参数，SQL Server也提供了相应的机制，只需使用 　　 　　　　@变量名=变量值 　　 　　的形式列出所需的变量即可。需要注意的是不要因为有@前缀而把变量名当作局部变量，当调用存储过程sp_OAMethod时，SQL Server就会解析出@，因此，即使在调用的方法中有名字为HostName的参数时，仍然可以使用名字为@HostName的局部变量。 　　　　下面是二个调用sp_OAMethod的例子。第一个例子调用一个名字为CentimetersToPoints的方法，它只接受在@CMVal变量中提供的一个参数，返回的值存储在变量@RetVal中。第二个例子调用一个名字为MailLogon的方法，它接受三个可选的变量，这个例子中根据名字接受二个变量，把Name设置为字符串"MyUserName"，把 Password设置为字符串"pwd"。 　　 　　　　Exec sp_OAMethod @Object, 'CentimetersToPoints',@RetVal OUTPUT, @CMVal 　　 　　　　Exec sp_OAMethod @Object, 'MailLogon', NULL,@Name='MyUserName', @Password='pwd' 　　 　　不再使用一个对象后，需要通过下面的语法调用存储过程sp_OADestroy释放对该对象的引用： 　　 　　　　sp_OADestroy objecttoken 　　 　　　　调用sp_OADestroy存储过程可以释放由参数objecttoken指定的对象，同时还释放这个对象所使用的内存和其他资源。下面是一个调用sp_OADestroy的命令： 　　 　　　　Exec sp_OADestroy @Object 　　 　　　　需要注意的是，T-SQL中的数据类型与其他的编程语言并非是一一对应的，在调用一个需要特定的数据类型的方法时就可能出错。"数据类型转换"工具条可以将SQL Server的数据类型转换为Visual Basic的数据类型。 　　 　　　　错误处理 　　 　　　　象在前面提到的那样，如果对存储过程的调用成功了，则会返回一个为0的HRESULT值，其他的HRESULT值则意味着发生了错误。要判断一个非零的HRESULT值，可以把HRESULT值传递给sp_OAGetErrorInfo存储过程，其语法如下： 　　 　　　　sp_OAGetErrorInfo [objecttoken] [, source OUTPUT] [, description OUTPUT] [, helpfile OUTPUT] [, helpid OUTPUT] 　　 　　　　第一个参数objecttoken是由sp_OACreate返回的对象标记。下面的四个参数返回错误信息。Sou <