Geting event from ActiveX into database

[Matik]

Hello everyone,

I was wondering. Is it possible, to recive an event from ActiveX into
database?
I was looking alredy with notification services, but I think that's
the wrong way.

Lets say, that there is a ActiveX which realize some tasks.
The database trigers the ActiveX like below:

=============== =============== =============== =============== ===

declare @iRetVal int
declare @iObject int
declare @sProperty varchar(2560)
declare @sSource varchar(1000)
declare @sDescription varchar(1000)
declare @sLog varchar(1000)
declare @dDateEVT datetime
declare @sText1 varchar(10)
declare @sText2 varchar(10)
declare @iProperty int

set @iObject = 0
set @dDateEVT = getdate()
set @iRetVal = 0
set @sText1 = '0000000000'
set @sText2 = '0000000000'
set @iProperty = 7

exec @iRetVal = sp_OACreate 'MComponent.pid Mess', @iObject OUTPUT,1
EXEC sp_OAGetErrorIn fo @iObject, @sSource OUT, @sDescription OUT
IF @iRetVal <> 0
begin
set @sLog = 'LOG1: No object created. Source: ' + @sSource + '
Description: ' + @sDescription
print @sLog
end

-- Method
exec @iRetVal = sp_OAMethod @iObject,'Send_ FromA', @iProperty
OUT,@nMessageNr = 5, @bstrDateTime = @dDateEVT, @textFromA1 = @sText1,
@textFromA2 = @sText2
EXEC sp_OAGetErrorIn fo @iObject, @sSource OUT, @sDescription OUT

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

PRINT 'Property from method:'
PRINT @iProperty

=============== =============== =============== =============== ===

This function works properly. I recive the data into ActiveX.
After the ActiveX process the data, it returns the event, wit a
response for this call of method.

Now ... how do I can get, this event into SQL server?
Is it possible to do that enyhow, without acctions like:
- do, that the ActiveX writes the data in to a interface table, where
from it will be readed.

Any help would be appreciated

Mateusz

[Simon Hayes]

ma****@sauron.x o.pl (Matik) wrote in message news:<8b******* *************** ****@posting.go ogle.com>...

Hello everyone,

I was wondering. Is it possible, to recive an event from ActiveX into
database?
I was looking alredy with notification services, but I think that's
the wrong way.

Lets say, that there is a ActiveX which realize some tasks.
The database trigers the ActiveX like below:

=============== =============== =============== =============== ===

declare @iRetVal int
declare @iObject int
declare @sProperty varchar(2560)
declare @sSource varchar(1000)
declare @sDescription varchar(1000)
declare @sLog varchar(1000)
declare @dDateEVT datetime
declare @sText1 varchar(10)
declare @sText2 varchar(10)
declare @iProperty int

set @iObject = 0
set @dDateEVT = getdate()
set @iRetVal = 0
set @sText1 = '0000000000'
set @sText2 = '0000000000'
set @iProperty = 7

exec @iRetVal = sp_OACreate 'MComponent.pid Mess', @iObject OUTPUT,1
EXEC sp_OAGetErrorIn fo @iObject, @sSource OUT, @sDescription OUT
IF @iRetVal <> 0
begin
set @sLog = 'LOG1: No object created. Source: ' + @sSource + '
Description: ' + @sDescription
print @sLog
end

-- Method
exec @iRetVal = sp_OAMethod @iObject,'Send_ FromA', @iProperty
OUT,@nMessageNr = 5, @bstrDateTime = @dDateEVT, @textFromA1 = @sText1,
@textFromA2 = @sText2
EXEC sp_OAGetErrorIn fo @iObject, @sSource OUT, @sDescription OUT

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

PRINT 'Property from method:'
PRINT @iProperty

=============== =============== =============== =============== ===

This function works properly. I recive the data into ActiveX.
After the ActiveX process the data, it returns the event, wit a
response for this call of method.

Now ... how do I can get, this event into SQL server?
Is it possible to do that enyhow, without acctions like:
- do, that the ActiveX writes the data in to a interface table, where
from it will be readed.

Any help would be appreciated

Mateusz

I suspect this is not possible, since Books Online has no information
on event handling with the sp_OA% procedures. You should probably
consider using an external program or script to do what you need,
perhaps scheduled to poll a table on the server at intervals. I know
you mentioned that you're trying to avoid this, so if you need an
alternative approach you might want to give some more details of
exactly what you're trying to do, and why an "interface table" isn't a
good solution in your situation.

Simon

[Mateusz Marzec]

Thank you for reply.

Well... that's the problem. The ActiveX shouldn't be changed any more,
and that's why I have no possibility to implement a function in it,
which writes the data in to interface table. That's why I do not want
such a solution.

I do some algorithm in the database, and in one step, I must 'ask'
ActiveX, for data. This data are the condition for the next steps in the
algorithm from DB.
Now, via sp_OAx procedures, it is possible, to interact with this
ActiveX to give it the data. But the ActiveX do not respond immiedetly,
only via event. It is immposible, to change the ActiveX, that on event,
it writes the data back into DB. I have the solution, that I can have
another program, which catches the event from ActiveX and then writes it
back into DB (or a program which will be called from sp_OAx, and waits
internaly for the ActiveX so long, that as return value can give the
values recived from ActiveX). But these are solutions, which I use
reluctantly.

But anyway ... The solution with table, want be bad, but then I must
have a while loop, til I become answer. I would like not to implement
such a solution.

The best way, as I said, will be, to get the event directly (anyhow) in
to DB, with mechanics and solutions of SQL DB.

Mateusz

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Mateusz Marzec (ma****@sauron. xo.pl) writes:

Well... that's the problem. The ActiveX shouldn't be changed any more,
and that's why I have no possibility to implement a function in it,
which writes the data in to interface table. That's why I do not want
such a solution.

I do some algorithm in the database, and in one step, I must 'ask'
ActiveX, for data. This data are the condition for the next steps in the
algorithm from DB.
Now, via sp_OAx procedures, it is possible, to interact with this
ActiveX to give it the data. But the ActiveX do not respond immiedetly,
only via event. It is immposible, to change the ActiveX, that on event,
it writes the data back into DB. I have the solution, that I can have
another program, which catches the event from ActiveX and then writes it
back into DB (or a program which will be called from sp_OAx, and waits
internaly for the ActiveX so long, that as return value can give the
values recived from ActiveX). But these are solutions, which I use
reluctantly.

An event is essentially a callback, and T-SQL has no mechanism for this.
So if you can't change the COM module, you would have to write a wrapper
that reacts on the callback.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp