3

I am developing a Delphi XE7 application with data stored in an online Mysql database. For the database access I use FireDAC. Because the application can be used on more than one computer simultaneously I need to be notified when a table is changed, so I can update the displayed information on each computer. FireDAC has a component called TFDEventAlerted which sounded like exactly what I need for this. But this component gives an error when activating (calling Register): [FireDAC][Phys][MySQL]-303. Capability is not supported. I am not sure what this means, but after reading more about the component it seems Mysql does not support this type of events? If so: can anyone tell me whether there is another solution to accomplish the same?

Any help would be appreciated as I cannot seem to find a good solution.

Mark Broek
  • 31
  • 1
  • 4
  • 6
    MySQL cannot do this. If I were you I would develop a middleware application/service that interfaces with the db. The frontend app only communicates with the middleware. With the observer pattern, it is easy to notify all listeners (ie the frontend apps) when updates occur on the db. – whosrdaddy Sep 13 '15 at 16:39

3 Answers3

2

Native MySQL doesn't have the push-notification feature you're hoping to use. To make this work you'll need to poll (to regularly run a query) to look for changes.

There are some ways to overcome this limitation if the scale of your system makes polling infeasible. You could add a user-defined function to your MySQL server, like this one to send messages: https://github.com/mysqludf/lib_mysqludf_stomp#readme

This won't work if you don't own the MySQL server; most hosting services won't allow you to install UDFs.

Or, you could build a message publish/subscribe app. This is pretty easy to do with the Amazon simple queuing service or with rabbitmq. But it's a different kind of system design from what you are probably used to.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • I do not own the database server, so UDF's will not be possible. I will look into RabitMQ later on. In a different project I used Postgresql, which does support these database notifications, but than I ran into problems because every customer IP-address needed to be added separately by the hosting service. Most hosting services do not even allow this. This made me decide to use Mysql for this project. – Mark Broek Sep 13 '15 at 19:30
  • Please be careful with MySQL databases open to the public net. Because cybercriminals. Use ssl connections if you can. – O. Jones Sep 13 '15 at 20:01
2

In my article series about Firebird Database Events I proposed a solution based on message-oriented middleware. The middle tier of your application then would notify all interested parties about certain database events. Middle tier code would be database independent, all you need is a message broker who is specialized in reliable message delivery. An imaginary example for a 'after post' event handler is shown below:

procedure TAppDataModule.PurchaseOrderAfterPost(DataSet: TDataSet);
var
  Notification: INotification;
begin
  Notification := NotificationService.CreateNotification(PURCHASE_ORDER_TABLE_UPDATED);
  Notification.SetIntProperty(PURCHASE_ORDER_ID, PurchaseOrderID.AsInteger);
  NotificationService.Send(Notification);
end;

Popular free/libre open source message brokers are for example Apache ActiveMQ and RabbitMQ.

mjn
  • 36,362
  • 28
  • 176
  • 378
  • Do I understand correctly that I have to run a service at the database server for this solution? Sadly this will not be possible because the database has to run on a hosting service. – Mark Broek Sep 13 '15 at 19:23
  • @MarkBroek so the database is accessed directly over the Internet, without any middle (application server or REST) layer? – mjn Sep 14 '15 at 05:45
  • Well, yes. I use FireDAC components to establish the connection with the online database. The hosting provider gives the option to add access hosts for the db. Here I add the client IP addresses. So only a limited number of IP addresses have access. – Mark Broek Sep 14 '15 at 11:54
  • In this case your clients could exchange messages through a cloud-based messaging service such as [CloudAMQP](https://www.cloudamqp.com/) ("RabbitMQ as a Service") - it can be used from many programming languages, including Delphi – mjn Sep 15 '15 at 06:00
1

TFDEventAlerted control is not for MySQL database. That database doesn't support event model. If you want update data in "real time" then you must add manual request for changed data

Here are steps:

  1. Add new field to your database table like "last_updated";

  2. Fill that field by now() value on update or insert actions (by trigger or sql);

  3. Add timer to delphi app and add request by SELECT MAX(last_updated) AS last_updated FROM my_table for last updated time;

  4. If that time is new then request updated data by SELECT * FROM my_table WHERE last_updated >= :need_last_updated.

JayDi
  • 1,037
  • 15
  • 24
  • Thank you for the detailed steps! Although this is not ideal as it will make the application slower and the updates will not be realtime, this could indeed be a solution. – Mark Broek Sep 13 '15 at 19:25