Client-Server database application: how to notify clients that data was changed?

Question

Is it possible to know when and if the contents of certain tables in a database has changed? How can my SQL Server notify the client applications that the data was changed by another user? How to implement query notifications with dbGo ?

Do my clients need to poll the database, or is there a callback mechanism for this?

My client is a Delphi application with TADODataSet, and my server is SQL Server 2005/2008, serving multiple clients.

Bromo

Possible duplicate https://mcmap.net/q/1318039/-how-to-monitor-sql-server-database-table-from-delphi-application/960757 – Patella

@TLama, still no conclusive solution for TADODataSet and SQL-Server there. – Bromo

If this answer is correct, then dbGo (ADO) does not support notifications... – Patella

This one is better https://mcmap.net/q/1318040/-how-to-use-sqlserver-service-broker-sqlnotifications-with-delphi7/960757, however the answer is still no, dbGo doesn't support query notifications. – Patella

This is why I prefer to use Bold for Delphi framework. It update the clients automatically when database is changed. It is very convenient. There is of course a lot of other features also to speed up development. – Pusillanimity

Answer 1

The Delphi dbGo controls does not support Query Notifications, so either poll the database or try to check e.g. some of the following:

• WMI Provider for Server Events - MSSQL Service Broker
• DA-SOFT AnyDAC TADEventAlerter component - Query Notifications mechanism
• Devart SDAC TMSChangeNotification component - Query Notifications mechanism

Patella

Answer 2

No, you would have to build your own "middle tier" to do this. The simplest way is to poll the server. A more complex solution is to build your own "middle tier" server that polls the database and pushes notifications to the clients (with your own client connection).

A middle ground solution is to have a separate client on the server poll the tables you need to "watch" and write "notfication flags" into another database table that can be polled by the clients. At least this way the clients can poll a simple table with minimal data that lists what has changed which can then be read by another select query. A lot less effort on the part of the clients.

Herbertherbicide

There is also a "mixed-middle-ground" way: the record-changing database client apps themselves (including application server side logic) can send broadcast 'record changed!' messages to all listening clients over the middle tier. This would be an 'immediate' way to notify other apps about the change. In addition, your 'separate client on the server' could run in longer intervals to reduce the amount of polling, and could skip a polling cycle every time it receives a client-generated notification messages... – Typeset