Solved

Network wide notification of database changes

Posted on 2002-04-09
9
171 Views
Last Modified: 2010-04-04
Hello.

I would like to know how to send out a notification to all the instances of my application of an update/addition/deletion in the database, so they can process the updates as they happen.

The aforementioned instances will be running on a network of unknown size.

I don't want to use a TTimer component. I don't like them. I don't like polling. But if I must then I must.

All suggestions are tax-deductible.
No, they aren't. Not Really.

TheTaxMan
0
Comment
Question by:TheTaxMan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6927491
what database?
0
 

Expert Comment

by:classics
ID: 6927519
If you are able to listen on all the local adapters use the broadcast address of each adapter.

Have the clients listen for broadcast packets on any local adapters and you should be fine.
0
 
LVL 10

Expert Comment

by:Jacco
ID: 6928798
Interbase has a mechanism for this. You can define events in your database and fire in triggers (insert/update/delete). From the application side you can subscribe top receiving certain messages. The component used for receiving the messages is the TIBEventAlerter.

Other database systems do not support this. At least not the systems I have worked with.

Possible solutions:

1) When a client updates a record send a TCPIP message to a broadcast server (positioned near or on the database PC). This broadcast server then broadcasts the message to all connected TCPIP clients. (Currently I am using this method).

Disadvantage: Events by other clients (who do not send the message to the broadcastserver) updating the data are not handled. (Think about your OSQL tool).

2) If you RDBMS supports calling COM objects or even EXE from triggers then you could post a TCPIP message to the broadcastserver from there. The broadcast server then broadcasts the message to all connected clients.

Disadvantage: Calling an EXE from a trigger can be dangerous. It will be executed very often, so it must not have memory leaks, and a very fast execution time (and maybe very small in EXE size).

In MSSQL it is all very possible through extended stored procedures. I don't know about other RDBMS's.

!! There are two "hidden" dangers which I found out by experience:

1) If the client gets notified of a change often it will go and query the database. No say there are 100 clients who all get notified the will all try to fetch the data simultaniously. This will bloat your network. Solution for this in to only mark your data as dirty when responing to the event and only fetching when necessary.

2) If in response to a notification from the broadcast server a client does an update to the database which again causes an event to fire you get a infinite loop of events. Solution is to never do update/delete/insert actions in response to a event notification.

Tips:

1) Try and put as much information in the message as possible. This makes it probable that your clients do not have to query the database in response to an event.
2) I used the Indy components TIdTCPServer / TIdTCPClient for the broadcaster. The components are very easy to understand.
3) Your client application will become dependant on the broadcaster very easily. If the client application does not receive messages it might be unwise to continue running certain tasks.
4) Program the broadcast server in a way that it doesn't broadcast the message back to the client it came from.

Advantages:

I have implemented this model several times now. And every new assiugnment I, get I implement it again. I do not only use it for database change notifications any more but as a base for inter client communication and for all my distributed computing needs. I created lost of other services that also connect to the broadcaster that respond to certain messages. Last week I even implemented loadbalancing (the broadcaster selectively passes messages to different instance of the same app).

Hope you can do something with this information.

I cannot give the complete sources of the broadcaster since I work for a company... But if you get stuck with something you can always ask EE.

Good luck,

Jacco
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:TheTaxMan
ID: 6930226
Wow! Thanks Jacco.

I am using Interbase, so I will have a go with your first suggestion.

Your "Broadcast Server" sounds significantly cool, though. Even if using an IBEventAlerter works, I'd like to try sending these "TCP/IP messages". Do the TIdTCPServer / TIdTCPClient components come with Delphi 5?

TheTaxMan
0
 
LVL 10

Expert Comment

by:Jacco
ID: 6930488
No nut you can download them from

www.nevrona.com

They are free. (They are included with D6 version 8 is stable version 9 is in beta (I use version 8 for most things))

You have to get into some thread programming though.

To give you a hint on how to implement the stuff:

I use a TIdTCPClient and TIdTCPServer on both sides of the communication. The client initiates the communication through the TIdTCPClient connecting to the broadcasters TIdTCPServer asking the server to "backconnect" with a new TIdTCPClient to a specified port on the TIdTCPServer of the client.

This may sound rather complex but if you get it up and running you have established a dedicated full duplex connection on which you can implement any protocol.

Regards Jacco
0
 
LVL 10

Accepted Solution

by:
Jacco earned 150 total points
ID: 6930489
The TIBEventAlerter was with D2. With D5 (or is itr D6) you have the IBObjects stuff (also free) and IBObjects will probably have a replacement for the alerter.

Check your IB docs on how to fire event from the DB. Check IBObjects on how to catch them

Regards Jacco
0
 
LVL 7

Expert Comment

by:God_Ares
ID: 6930807
sounds like you have to do CORBA and make an event service.
0
 

Author Comment

by:TheTaxMan
ID: 6943442
Thanks for your help Jacco.
0
 
LVL 10

Expert Comment

by:Jacco
ID: 6943930
You are welcome !
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Suggested Courses
Course of the Month9 days, 18 hours left to enroll

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question