Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 176
  • Last Modified:

Network wide notification of database changes

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
TheTaxMan
Asked:
TheTaxMan
1 Solution
 
kretzschmarCommented:
what database?
0
 
classicsCommented:
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
 
JaccoCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
TheTaxManAuthor Commented:
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
 
JaccoCommented:
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
 
JaccoCommented:
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
 
God_AresCommented:
sounds like you have to do CORBA and make an event service.
0
 
TheTaxManAuthor Commented:
Thanks for your help Jacco.
0
 
JaccoCommented:
You are welcome !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now