Firebird developers can use Firebird events to notify clients about changes in database record data. In the previous post, I showed and discussed some limitations of Firebird events. This post presents a solution based on message-oriented middleware.
The solutions in the first post used server-side logic in the Firebird database metadata, implemented with Firebird’s procedural language (PSQL). Information which can not be passed within Firebird events, such as the record ID of the new purchase order, can be stored in helper tables which then must be read by the database clients. So from a technical view, these solutions only replace the timer-triggered client-pull with event-triggered client-pull. This is a small step forward, at the price of increased complexity of database metadata.
A new strategy: implementing the event notification on the client side
Obviously, the client application which inserts a new purchase order could also send notifications to the other clients. The advantage would be that server-side PSQL code can be replaced by code written in your client-side programming language (Delphi/Free Pascal, C#, …). The PSQL code in the server-side solution looked like this:
CREATE TRIGGER PURCHASE_ORDER_INSERT FOR PURCHASE_ORDER AFTER INSERT
If we implement notifications in the client side application, similar code has to be be executed when a new order record has been inserted in the order table, using an inter-process communication method. The event notification code could look like this:
procedure TAppDataModule.PurchaseOrderAfterPost(DataSet: TDataSet);
Notification := NotificationService.CreateNotification(PURCHASE_ORDER_TABLE_UPDATED);
But advanced features like load balancing, or for notifying clients which currently are not listening, would need a high amount of custom code. Another difficult task would be the integration of clients or other systems written in an other programming languages (a PHP based order entry web app for example) – their integration with your application would only be possible after porting the notification system.