Solved

Network wide notification of database changes

Posted on 2002-04-09
9
166 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
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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
indy tidtcpclient threading issue 8 63
Mydac connection data base issue 3 135
Multiple image collision 13 69
Delphi...Split view - idea? 1 39
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

937 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

15 Experts available now in Live!

Get 1:1 Help Now