Calling methods, functions threw Extended SP in a DLL

[Matik]

Hi,

I alredy tried to search this problem in last posts, but I couldn't
find the answer.
I try to access via Extended SP the method in a dll.

I registered the dll as a ExSP, with a name of method. But after
calling it in T-SQL, I became such a error message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the
function SendGeneralNotify_FromA in the library [LibraryName.dll].
Reason: 127(error not found).

In this dll I have only one class an it has events, properities and
methods.
I will to call one of these methods.

aha ... very importand. Please don't say that this dll should be
written in c++ because it is made like that (no VB).

Maybe somebody of you have an example how I should call it, to became
an access on this dll?

Sorry for my not well english.

With best regards, looking forward for reply

-----------------------------
Matik
ma****@sauron.xo.pl

[Craig Kelly]

Matik,

In this dll I have only one class an it has events, properities and
methods.
I will to call one of these methods.

aha ... very importand. Please don't say that this dll should be
written in c++ because it is made like that (no VB).

If I understand you correctly, you've written a DLL in VB 6 (or 5) and you
want to call it as an extended stored procedure. Unfortunately, VB 6 can't
export a function, which is what an extended stored procedure must do. You
have three choices:

1. Use a different language (C, C++, Delphi, etc): I understand that you
don't want to do this. I don't know if VB.NET will let you export a
function, but if it will that might be an option.

2. Buy a tool from Desaware that enables you to export a function from VB 6.
I don't remember the name and I've never used it, but you should be able to
find it at Desaware.

3. Instead of using the extended stored procedure functionality, use the
sp_OA.... stored procedures to instantiate your class and call the method
you want. Check books online or google for examples: sp_OACreate is a good
one to start with an google.

My standard extended sp warning: extended stored procedure are dangerous. I
refuse to allow them where I work because a single errant pointer can cause
untold damage. I know people use them to great effect, but it gives me the
shivers. I feel the same way (but not as strongly) about the sp_OA...
procedures. My real advice is to find another way to do it. And... if
writing an extended sp doesn't make you nervous, you probably shouldn't
write one :)

Craig

[Matik]

Hej Craig,

If I understand you correctly, you've written a DLL in VB 6 (or 5) and you
want to call it as an extended stored procedure. Unfortunately, VB 6 can't
export a function, which is what an extended stored procedure must do. You
have three choices:

Maybe I explained it a little nfortunately. One more time:
- this is a object (in dll) which has properities and methodes.
- I must call one of these methods,
- it is written in C++,

If I try to do that via ExSP, it do not works (I have this error as
above alredy written).
If I do that with sp_OA, then It works ... but not like I expected:-)

First, I create the object:

exec @iRetVal = sp_OACreate '{034188F2-8DBC-4613-829A-76D5279C35A3}',
@iObject OUTPUT,1
EXEC sp_OAGetErrorInfo @iObject, @sSource OUT, @sDescription OUTPUT
IF @iRetVal <> 0
begin
set @sLog = 'No object created. Source: ' + @sSource + '
Description: ' + @sDescription
print @sLog
end

This is without problems.
Then I try to call this methode:

exec @iRetVal = sp_OAMethod @iObject,'SendNotification_FromA',
@sProperty OUT, @nMessageNr = 555, @bstrDateTime = @dDateEVT,
@textFromA1 = @sText1 OUTPUT, @textFromA2 = @sText2 OUT
EXEC sp_OAGetErrorInfo @iObject, @sSource OUT, @sDescription OUT

IF @iRetVal <> 0
begin
set @sLog = 'Source: ' + @sSource + ' Description: ' +
@sDescription
print @sLog
end

IF @iRetVal <> 0
begin
set @sLog = 'Source: ' + @sSource + ' Description: ' +
@sDescription
print @sLog
end
print @iRetVal

PRINT @sProperty
PRINT @sText1
PRINT @sText2

.... and I became no errors, but the values are the same I set at the
beginning.
On my pc works also the small software, which should capture this
values.
This software recieves the data sended with this method.
I wrote the small VB programm, where I call the same method, with the
same values, and then the second programm reives the data ...

Hopeless?

Thank You for Your cindly reply.

Matik

[Craig Kelly]

Matik,

Please see inline

Maybe I explained it a little nfortunately. One more time:
- this is a object (in dll) which has properities and methodes.
- I must call one of these methods,
- it is written in C++,
Ah... I see. I apologize for the misunderstanding.
If I try to do that via ExSP, it do not works (I have this error as
above alredy written).
If I do that with sp_OA, then It works ... but not like I expected:-)
If you already have C++ and you really want an ExSP then you can look in SQL
Server Books Online for the topic "Extended Stored Procedure Programming"
under the main heading "Build SQL Server Applications". Basically you would
need to #include a special header or two and export a very specific function
type. But since you already have a COM object...
First, I create the object:
Remember that the object identifier you're getting is basically a handle to
a COM interface pointer, so you need to treat it like you would any other
resource: if sp_OACreate succeeds, then you need to call sp_OADestroy.

Another gotcha that seems simple but people seem to forget: the COM object
needs to be registered on the SQL Server (if you're instantiating a local
COM Server, which you are here). I watched people nearly strangle a
laughing coworker when they realized that registering a COM component on the
computer running Query Analyzer isn't the same :) I know this isn't your
problem, but if it hasn't bitten you yet, it will...

<whitespace edited> exec @iRetVal = sp_OAMethod
@iObject,
'SendNotification_FromA',
@sProperty OUT,
@nMessageNr = 555,
@bstrDateTime = @dDateEVT,
@textFromA1 = @sText1 OUTPUT,
@textFromA2 = @sText2 OUTPUT

OK, I couldn't see what was wrong, so I created a quick COM object (I
cheated and used VB :) that matched what I *think* your COM object looks
like. Here's the IDL:

HRESULT SendNotification_FromA(
[in] long nMessageNr,
[in] BSTR bstrDateTime,
[in, out] BSTR* textFromA1,
[in, out] BSTR* textFromA2,
[out, retval] BSTR* );

This is the important part: you need to make sure that the parameter names
match up in the call to sp_OAMethod and your IDL. Since you're trying to
get output parameters, you also need to make sure that textFromA1 and
textFromA2 are [in,out] and are a datatype that allows that (such as BSTR*).

Also, you need to make sure that you've correctly implemented IDispatch and
that all your parameters are OLE compatible. A good test is to re-write
your VB program to use late-binding. For example (untested and coded in my
newsreader):

Dim obj As Object
Dim s, a1, a2
Set obj = CreateObject("SomeLib.SomeClass")

a1 = "in 1"
a2 = "in 2"
s = obj.SendNotification_FromA(555, "SomeDateTime", a1, a2)

MsgBox s & vbcrlf & a1 & vbcrlf & a2

Set obj = Nothing

Regardless, I got it to work, so I imagine there's something wrong with the
way your COM code is working. After checking your IDL and matching T-SQL
code, I would try stripping the COM method down to as little as possible
(maybe just log out input parms and set the output parms to constants) and
see if you can get that to work.

Good Luck,

Craig