Implement MySQL event notification back to a Delphi application

Question

G'Day,

Is anyone able to provide some pointers on how I can notify my Delphi application that a particular record in my MySQL database has changed? Something along the lines of the event system from Interbase?

Ideas I have looked at:

.: Q4M :. (http://q4m.31tools.com/)

Pros: Native MySQL solution requiring no external daemons Cons: No Win32 build exists due to it using Posix calls specific to Linux

.: MySQL Message API :. (http://messagequeue.lenoxway.net/)

Pros: Robust (using spread.org) Cons: No Win32 binary. Additional configuration and daemon(s) of spread.org required

.: Custom User Defined Function :.

I am attempting to write a UDF that can use the Win32 API PostMessage() so send a windows message to a simple socket server.

Pros: Integrated (albeit with external DLL dependency) with MySQL. Can be customised to my needs Cons: I cannot get it to work (See post MySQL User Defined Function to send a windows message). This may be because MySQL is running as a service

Any pointers, ideas etc. greatly appreciated.

--D

Answer 1

As an option you may consider to use a middle-tier solution like a RemObject DataAbstract or kbmMW. AFAIK, they allow to track the changes on the middle layer and provide mechanisms to notify clients about that.

Answer 2

I ended up implementing this as follows:

• Created Windows app that listened on a TCP port as well as a Windows Pipe
• Created a mySQL User Defined Function (UDF) that would connect to the above Windows Pipe and send some information
• Added triggers to the tables in the database to invoke the UDF with information about which table, what operation (insert, deleted, update), primary key values
• TCP clients can now connect to the Windows app to receive the information passed on from the UDF
• The TCP clients can then refresh as needed using the information retrieved

Works well and is light weight bandwidth wise (as clients only refresh what they need). Also keeping the TCP Server on the same machine as the database and using a Windows Pipe means the pipe can be kept open, and by writing to the pipe there is no TCP stack overhead. Means the load on mySQL and the time taken to execute the UDF is very minor.