Using COM Objects in SQL Server
 
SQL Server 2000 allows a T-SQL object (like a trigger or stored procedure) to call into COM objects (like a DLL). The facility to call automation objects opens a lot of capabilities to SQL Server, since you can tap into many system provided DLL's to extend the power of SQL Server for applications. In this article we will explore this feature of SQL Server and see how we can effectively extend the SQL Server environment.
 
In order to access an automation object (read DLL), we have to follow the following steps:
  • Create the DLL using any language of your choice. For example, in this article we will use VB 6 to create a DLL.
  • Initialize the DLL from SQL Server using the sp_OACreate method call.
  • Call into the various methods or properties of the DLL using sp_OAMethod, sp_OAGetProperty and sp_OASetProperty calls.
  • Trap all errors using the sp_OAGetErrorInfo call.
  • Destroy the automation object using sp_OADestroy.

If you look at the above steps, they are very similar to how you would work with an automation object in a development environment like Visual Basic. You add a reference to the DLL that you want to work with, call its methods and finally destroy the object. One thing to remember is: you cannot call into .NET objects directly from SQL Server using the above method. Read this article for more information. The next version of SQL Server (code named Yukon) offers this capability and more.

OK, let's see all this in action. First, let's create a simple DLL in Visual Basic 6. Here is the source for this DLL.


Public Function noParamsCall() As String
    noParamsCall = "(no Parameters passed)"
End Function

Public Function paramsCall(ByVal param As String) As String
    paramsCall = "You passed : " & Trim(param)
End Function

 
We have two functions in this DLL called noParamsCall and paramsCall. The first function takes no parameters but returns a simple string. The second function takes a single string parameter and then returns a string comprised of the parameter passed. You can now compile this into a DLL. Here is how we can use this DLL from SQL Server.

-- Scratch variables used in the script
DECLARE @retVal INT
DECLARE @comHandle INT
DECLARE @errorSource VARCHAR(8000)
DECLARE @errorDescription VARCHAR(8000)
DECLARE @retString VARCHAR(100)

-- Initialize the COM component.
EXEC @retVal = sp_OACreate 'Project1.Class1', @comHandle OUTPUT
IF (@retVal <> 0)
BEGIN
	-- Trap errors if any
	EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, @errorDescription OUTPUT
	SELECT [Error Source] = @errorSource, [Description] = @errorDescription
	RETURN
END

-- Call a method into the component
EXEC @retVal = sp_OAMethod @comHandle, 'noParamsCall', @retString OUTPUT
IF (@retVal <> 0)
BEGIN
	-- Trap errors if any
	EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, @errorDescription OUTPUT
	SELECT [Error Source] = @errorSource, [Description] = @errorDescription
	RETURN
END

-- Print the value returned from the method call
SELECT @retString

-- Call a method into the component
EXEC @retVal = sp_OAMethod @comHandle, 'paramsCall', @retString OUTPUT, @param='Test Value'
IF (@retVal <> 0)
BEGIN
	-- Trap errors if any
	EXEC sp_OAGetErrorInfo @comHandle, @errorSource OUTPUT, @errorDescription OUTPUT
	SELECT [Error Source] = @errorSource, [Description] = @errorDescription
	RETURN
END

-- Print the value returned from the method call
SELECT @retString

-- Release the reference to the COM object
EXEC sp_OADestroy @comHandle


 
OK, let's now dissect this program and see the various calls that we have made.
  • The first call is to sp_OACreate. This procedure takes as parameter the ProgID or the ClassID of the automation object and then returns a handle to the object. This handle is stored in a variable of type INT. The procedure also returns a value that indicates whether the call was successful. The handle that is returned is used for all subsequent calls to the sp_OA* methods. You can optionally pass a context parameter that specifies the execution context in which the created object will run. You can specify the following values:
    • In-process (value of 1), which indicates that the object can access SQL Server memory and other resources. Normally DLL's are loaded in-process. Use this with care, since a badly writeen DLL can crash SQL Server.
    • Out of process (value of 4), which indicates that the object cannot access SQL Server memory or resources. Normally EXE's are loaded out of process.
    • Both in-process and out of process (value of 5), which is the default.
  • If the return value is not 0, we then get the error details using the sp_OAGetErrorInfo procedure. This procedure takes as parameter the handle to the object and then returns as output the source and description of the error.
  • Once the object is initialized, we can call into the various methods of the object using the sp_OAMethod procedure. This procedure takes as parameter the handle to the object, the name of the method and an optional return value. Parameters can also be passed into the method if required.
  • Once again we can get the error details using the sp_OAGetErrorInfo procedure.
  • Once we are done with the object, we can (and should) use the sp_OADestroy procedure to release the reference to the object. This procedure takes as parameter the handle to the COM object to destroy.

Now, when you execute this program, you get the following as the output.


-------------------------------------------------------------------------------- 
(no Parameters passed)

(1 row(s) affected)

-------------------------------------------------------------------------------- 
You passed : Test Value

(1 row(s) affected)

 
One thing to remember is: when you call "sp_OACreate", SQL Server starts the OLE automation execution environment. This environment keeps running till SQL Server is stopped (or you call "sp_OAStop"). Thus, if you want to make changes to a DLL that SQL Server is using, you need to stop SQL Server, make the changes and then re-start the service. If you call "sp_OAStop", other clients who are using automation procedures will receive an error, unless someone calls "sp_OACreate" again.
 
OK, in this article, we saw the basics of calling a COM DLL from SQL Server. The DLL that we used for quite simple and was not very complex. But, remember that this very feature of SQL Server being able to access COM objects can open the door to extending SQL Server features.

Home