Embed MySQL event notification in a Delphi application

Question

G'day,

Can anyone point out some pointers to how I can notify a Delphi application that a particular record in my MySQL database has changed? Anything along the lines of the Interbase event system?

Ideas I looked at:

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

Pros: MySQL's own solution that does not require external daemons Cons: there is no Win32 build because of it, using Linux-specific Posix calls

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

Pros: Reliable (using spread.org) Cons: no Win32 binary. Requires additional configuration and the .org distribution daemon.

: Custom User Function :.

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

Pros: integrated (albeit with external DLL dependency) with MySQL. It can be configured for my needs. Cons: I cannot get it to work (see Posting Custom MySQL function to send a Windows message ). Perhaps this is due to the fact that MySQL works as a service

Any pointers, ideas, etc. highly appreciated.

- D

Answer 1

I ended up implementing this as follows:

• A Windows application was created that listened to the TCP port as well as Windows Pipe
• Created a custom function mySQL (UDF), which will connect to the aforementioned Windows handset and send some information.
• Triggers are added to tables in the database for calling UDF with information about which table, which operation (insert, delete, update), primary key values
• TCP clients can now connect to a Windows application to retrieve information transferred from UDF
• TCP clients can then be updated as needed using the information received.

It works well and has a small bandwidth (as clients only update what they need). In addition, storing a TCP server on the same computer as in the database and using a Windows pipe means that the channel can be opened and there is no TCP stack overhead when writing to the pipe. It means the load on mySQL and the time taken to execute UDF is very small.

Answer 2

As an option, you can use a mid-range solution such as RemObject DataAbstract or kbmMW. AFAIK, they allow you to track changes at an average level and provide mechanisms to notify customers about this.