Calling a VB DLL from a Stored Procedure
-
Hi Guys,
I have a question and hope you can help me. I need to call a VB dll from a stored procedure
(passing it input parameters, and getting a some return values).
Is this possible? Please help.
Thanks.
-
Depends on version of VB. If it's VB6 and an in-process COM DLL, then you can probably use the sp_OA_Create and related stored procedures to instantiate a COM object and call some of its methods.
If it's VB.Net, you'll need to generate the COM Interop wrappers before using sp_OA_Create etc.
In both cases, it will depend upon the type of input parameters. If they are of primitive types, it should be OK, but you'll run into issues if the VB code expects complex types (like other COM classes, or user-defined types) as input.
-
The dll is written using VB 6.0 and all the input/output parameters are primitive types.
Are there any examples I can use? I appreciate your help.
Thanks.
-
Dear Yasir,
I post the following with some caveats. I personallly did not write the following stored procedure, but the programming team that I manage did. If you are experienced with VB dll calls, and just need the glue from the SQL Server side, then hopefully there is enough code below that you can use and make this work in your situation. It involved activating some 'extended stored procedures' in master that you otherwise ignore, so you might have to tweek permissions. I can tell you this was a time-consuming task to get this wrapper created. We had very little help to get this going, so I also post it here in the spirit of "sharing my development" that so many others in this forum have done before me.
This is based upon using SQL Server 2000 and VB.net modules. We originally developed the VB.net in the hopes of using SQL Server 2005 (which more directly supports .net function calls) but due to circumstances we had to use SQL Server 2000. Thus, this wrapper had to be created in order to utilize the dll functions.
All my best.
Mike Prascak
CREATE PROCEDURE ProcessTrNo(@TrNo as varchar(30), @ConfigFile as varchar(100), @CarrierCode as char(1) = '') AS
DECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DECLARE @return varchar(255)
DECLARE @src varchar(255), @desc varchar(255)
-- Create an object.
EXEC @hr = sp_OACreate 'ComSMTED.ComTED', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source='10: ' + @src, <A href="mailto
escription=@desc">Description=@descRETURN
END
-- Call a method.
EXEC @hr = sp_OAMethod @object, 'LoadSettings', @return OUT, @ConfigFile
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source='20: ' + @src, <A href="mailto
escription=@desc">Description=@descRETURN
END
/* ==subroutine for testing
EXEC @hr = sp_OAMethod @object, 'OpenNewLogFile', @return OUT, @LogFile
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source='30: ' + @src, <A href="mailto
escription=@desc">Description=@descRETURN
END
*/
IF (@CarrierCode = 'U')
BEGIN
EXEC @hr = sp_OAMethod @object, 'GetUPSDetails', @return OUT, @TrNo
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source='40: ' + @src, <A href="mailto
escription=@desc">Description=@descRETURN
END
EXEC @hr = sp_OAMethod @object, 'ParseUPSDetails', @return OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source='50: ' + @src, <A href="mailto
escription=@desc">Description=@descRETURN
END
END
IF (@CarrierCode = 'F')
BEGIN
EXEC @hr = sp_OAMethod @object, 'GetFedExDetails', @return OUT, @TrNo
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source='40: ' + @src, <A href="mailto
escription=@desc">Description=@descRETURN
END
EXEC @hr = sp_OAMethod @object, 'ParseFedExDetails', @return OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source='50: ' + @src, <A href="mailto
escription=@desc">Description=@descRETURN
END
END
-- Destroy the object.
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source='100: ' + @src, <A href="mailto
escription=@desc">Description=@descRETURN
END
GO
-
Mike,
Thank you for such a detailed explaination. I will try this and see if it works with my dll.
Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply