How to use COM DLL (Visual Basic DLL) 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.

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 WithoutParamsCall() As String

WithoutParamCall = "Without Parameter"

End Function

Public Function WithparamsCall(ByVal par As String) As String

WithparamsCall = "You passed : " & Trim(par)

End Function

Create a class module TestClass and include above functions. Save and Compile the application and create DLL as TestDLL
We have two functions in this DLL called WithoutParamsCall and WithparamsCall. 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 TestClass.TestDLL, @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, ' WithoutParamsCall', @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, ' WithparamsCall', @retString OUTPUT, @param='Hello World'
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.
--------------------------------------------------------------------------------
(no Parameters passed)

(1 row(s) affected)

--------------------------------------------------------------------------------
You passed : Hello World
(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.