Execution of OLE AutomationCOM Objects

Execution of OLE Automation/COM Objects

Microsoft has developed several unmanaged technologies that enable developers to encapsulate unmanaged code and custom objects into executable components. These components can then be invoked by other applications developed in the same (or any other) programming language that supports these kinds of components. This is an older alternative to achieving the same task using managed (.NET) components. Through the years, this technology has been known by different names: OLE, OLE Automation, COM, DCOM, Automation, ActiveX, and COM+.

SQL Server can initiate managed code components and access the properties and methods exposed by them. A set of system stored procedures (with the prefix sp_OA) has been designed and implemented in SQL Server to help you accomplish such tasks.

Note 

When Microsoft first unveiled this feature in SQL Server, code components were known as "OLE Automation objects." For this reason, Microsoft attached the OA prefix to these stored procedure names, and I used OLE Automation Objects in the title of the section.

By default, usage of OLE Automation/COM objects is disabled in SQL Server 2005. It can be enabled using the Surface Area Configuration tool at the end or after the installation. Alternatively, you can enable it using the following script:

 Exec sp_configure 'Ole Automation Procedures', 1 GO RECONFIGURE GO 

To demonstrate the use of OLE Automation on a simple Visual Basic function in Visual Basic 6:

1. Create the DjnToolkit ActiveX DLL project in Visual Basic 6 and then create a DjnTools class.

2. Create a method called SpellNumber, which ignores the input value (currency amount) and returns a constant string (see Figure 21-2).

Figure 21-2: A COM object created in Visual Basic 6

Note 

Even if you run the object from the Visual Basic IDE (instead of compiling and installing it), you will still he ahle to access it from Transact-SQL code. This is an important feature for debugging the object.

The stored procedure shown in the code on the following page first initiates the COM object using the sp_OACreate system stored procedure. It obtains a token @intObject, which is used from that point to access the class.

The sp_OAMethod stored procedure is used to execute class methods. The return value and input parameter of the method are placed at the end of the stored procedure's parameter list.

Before the stored procedure is complete, the COM object must be destroyed using sp_OADestroy.

If an automation error occurs at any point, sp_OAGetErrorInfo can be used to obtain the source and description of the most recent error:

 Alter Procedure dbo.ap_SpellNumber -- demo of use of Automation objects      @mnsAmount money,      @chvAmount varchar(500) output,      @debug int = 0 As set nocount on Declare @intErrorCode int,         @intObject int,  -- hold object token         @bitObjectCreated bit,         @chvSource varchar(255),         @chvDesc varchar(255) Select @intErrorCode = @@Error If @intErrorCode = 0      exec @intErrorCode = sp_OACreate 'DjnToolkit.DjnTools',                                       @intObject OUTPUT If @intErrorCode = 0      Set @bitObjectCreated = 1 else      Set ObitObjectCreated = 0 If @intErrorCode = 0      exec @intErrorCode = sp_OAMethod @intObject,                                       'SpellNumber',                                       @chvAmount OUTPUT,                                       @mnsAmount If @intErrorCode <> 0 begin      Raiserror ('Unable to obtain spelling of number', 16, 1)      exec sp_OAGetError!nfo @intObject,                             @chvSource OUTPUT,                             @chvDesc OUTPUT       Set OchvDesc = 'Error ('                     + Convert(varchar, @intErrorCode)                     + ', ' + @chvSource + ') : ' + @chvDesc      Raiserror (@chvDesc, 16, 1) end if @bitObjectCreated = 1      exec  sp_OADestroy @intObject return @intErrorCode 

Once you are sure that communication between Transact-SQL and Visual Basic code is working, you can write code in Visual Basic that converts numbers to text. Since this is not a book about Visual Basic, I will not go into detail on that subject.

There is an even better example on how to use these stored procedures in Chapter 18.

Note 

You should avoid using OLE Automation/COM objects if possible. Managed components developed in .NET are mucb more stable and secure.

Data Type Conversion

Keep in mind that COM code components and Transact-SQL code use different data types. You have to set compatible data types on both sides to allow the OLE Automation system stored procedures to automatically convert data between them. You can identify most of the compatible data types using common sense (for example, varchar, char, and text types in SQL Server translate to the String data type in Visual Basic, and the int SQL Server data type translates to the Long data type). However, some data types deserve special attention.

When values are passed from SQL Server to Visual Basic, binary, varbinary, and image are converted to a one-dimensional Byte array. Any Transact-SQL value set to null is converted to a Variant set to null. Decimal and numeric are converted to String (not currency).

When values are passed from Visual Basic to SQL Server, Long, Integer, Byte, Boolean, and Object are converted to the int data type. Both Double and Single data types are converted to float. Strings shorter than 255 characters are converted to varchar, and strings longer than 255 characters are converted to the text data type. One-dimensional Byte () arrays shorter than 255 characters become varbinary values, and those longer than 255 become image values.