Geting event from ActiveX into database

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 @.iRetValint
declare @.iObjectint
declare @.sPropertyvarchar(2560)
declare @.sSource varchar(1000)
declare @.sDescription varchar(1000)
declare @.sLog varchar(1000)
declare @.dDateEVT datetime
declare @.sText1varchar(10)
declare @.sText2varchar(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.pidMess', @.iObject OUTPUT,1
EXEC sp_OAGetErrorInfo @.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_OAGetErrorInfo @.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

Mateuszmarzec@.sauron.xo.pl (Matik) wrote in message news:<8b6e9bf5.0408190717.705a3dec@.posting.google.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 @.iRetValint
> declare @.iObjectint
> declare @.sPropertyvarchar(2560)
> declare @.sSource varchar(1000)
> declare @.sDescription varchar(1000)
> declare @.sLog varchar(1000)
> declare @.dDateEVT datetime
> declare @.sText1varchar(10)
> declare @.sText2varchar(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.pidMess', @.iObject OUTPUT,1
> EXEC sp_OAGetErrorInfo @.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_OAGetErrorInfo @.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|||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!|||Mateusz Marzec (marzec@.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, esquel@.sommarskog.se

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