Solved

Network wide notification of database changes

Posted on 2002-04-09
9
165 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
9 Comments
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
what database?
0
 

Expert Comment

by:classics
Comment Utility
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
Comment Utility
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
 

Author Comment

by:TheTaxMan
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 10

Expert Comment

by:Jacco
Comment Utility
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
Comment Utility
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
Comment Utility
sounds like you have to do CORBA and make an event service.
0
 

Author Comment

by:TheTaxMan
Comment Utility
Thanks for your help Jacco.
0
 
LVL 10

Expert Comment

by:Jacco
Comment Utility
You are welcome !
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now