How to replicate Oracle DBMS_Alert functionality

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
mark@mprw.net
Posts: 5
Joined: Tue 23 Sep 2008 16:04

How to replicate Oracle DBMS_Alert functionality

Post by mark@mprw.net » Tue 23 Sep 2008 16:09

I have an application that runs against Oracle that we are porting to MS SQL Server 2005. In the oracle version we have a trigger when a particular table is updated that calls the DBMS_Alert package, which broadcasts the Record ID to all oracle connections so that the individual clients can check whether they are current displaying that record and update as necessary. What would be the best way of replicating that functionality in SDAC?
Thanks in advance,
Mark.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 24 Sep 2008 12:58

SDAC includes the TMSServiceBroker component similar to Alerter in Oracle.
You can find more detailed information about this component in the SDAC Help.

mark@mprw.net
Posts: 5
Joined: Tue 23 Sep 2008 16:04

Post by mark@mprw.net » Wed 24 Sep 2008 21:29

Thank you. The help is somewhat less than illuminating. I can get the demo to run, and it creates a bunch of new tables in the database, but do you have any samle code as to how to use a database trigger to create the 'message'?
Presumably I need to have each client act as an initiator when then are started up, but really they all need to be a part of the same 'conversation', so that an update to the bookings table calls the trigger which pushes the message. In the demo, I have to explicitly retrieve the message from the queue, whereas really i just want to trigger an event when my client app receives a message.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Mon 29 Sep 2008 09:23

There is an example in SDACDemo, that demonstrates work with the TMSServiceBroker component.
You can find more information about SQL Server Service Broker by the link:
http://msdn.microsoft.com/en-us/library/bb522893.aspx

To send message with SQL you should use the SEND ON statement. For more information refer to:
http://msdn.microsoft.com/en-us/library/ms188407.aspx

Post Reply