Solved

Network wide notification of database changes

Posted on 2002-04-09
9
169 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
find a node in VST 2 73
Find and Replace Stream with 0s 8 69
RESTRequest Parameter 4 43
MS Access from Delphi 31 61
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…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

820 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