How to create a notification server which informs Delphi application when database changes?
Question: We need to be able to inform a Delphi application in case there are changes to
some of our tables in MySQL.

Delphi clients are in the Internet behind a firewall, and they have to be
authenticated before connecting to the notification server we need to
implement. The server can be programmed using for example Java, PHP or Python,
and it has to support thousands of clients.

Typically one change in the database needs to be informed only to a single
client, and I don't believe performance will be a bottleneck. It just has to
be possible to inform any of those thousands of clients when a change
affecting the specific client occurs.

I have been thinking of a solution where:

  1. MySQL trigger would inform to notification server
  2. Delphi client connects to a messaging queue and gets the notification using it

My questions:

  1. What would be the best to way from the trigger to inform the external server of the change
  2. Which message queue solution to pick?


Answer: **Answer to the First Question:**

check this question and answers on Stack Overflow:

[When a new row in database is added, an external command line program must be
invoked](http://stackoverflow.com/questions/668666/when-a-new-row-in-database-
is-added-an-external-command-line-program-must-be-inv)

In theory, a simple user-defined function could be used to fire a 'row
changed' message to a message broker / queue. But this involves external
systems (at least a network subsystem) which can fail - and bad things can
happen.

A different solution which does not require dangerous modifications to the
database system would be a multi-tiered design for the application. The server
application which hosts the business logic then needs to generate 'database
content changed' events, post them to a publish and subscribe message channel
(aka 'topic') on the message broker so that every client will receive a copy
of this message immediately or if the client reconnects (using '[durable
subscriptions](http://docs.oracle.com/javaee/1.4/api/javax/jms/Session.html#createDurableSubscriber%28javax.jms.Topic,%20java.lang.String%29)').

* * *

I wrote a related blog article about this topic here: [Firebird Database
Events and Message-oriented
Middleware](http://mikejustin.wordpress.com/2012/11/06/firebird-database-
events-and-message-oriented-middleware/)

**Answer to the Second Question:**

The creators of [Second Life](http://www.secondlife.com/) have evaluated a
couple of message brokers and published their results - for some of the
products, Delphi client libraries exist or can be implemented using standard
protocols: [Message Queue Evaluation
Notes](http://wiki.secondlife.com/wiki/Message_Queue_Evaluation_Notes)

Since then, other products have been released, some of them can also be
integrated with Delphi clients through non-Java protocols, for example:

  * [**Open Message Queue (OpenMQ) 4.4**](http://activemq.apache.org/index.html), which is the default [JMS](http://en.wikipedia.org/wiki/Java_Message_Service) provider broker in the Sun GlassFish v3 application Server
  * [**JBoss HornetQ 2.1**](http://jboss.org/hornetq/) which will be the default JMS provider in JBoss application server 6. HornetQ 2.0.GA obtained scores up to 307% higher than previously published SPECjms2007 benchmark results, on the same server hardware and operating system set-up.

A very popular open source message broker which can be used from Delphi, Java,
PHP, C# (and other) clients is

  * [**Apache ActiveMQ 5.4.1**](http://activemq.apache.org/index.html) \- free [introduction chapter](http://manning.com/snyder/) of "ActiveMQ in Action" by Bruce Snyder, Dejan Bosanac, and Rob Davies

All brokers are designed for thousands of concurrent clients and tens of
thousands of messages per second. They also typically support clustering and
failover, though this is not part of the JMS specification.

If speed is not so important, but you need High availability (even if your
internal system is down), [Amazon Simple Queue Service (Amazon
SQS)](http://aws.amazon.com/sqs/) is a cloud-based service which can be
accessed using REST and Soap style interfaces.

