Change Notification?

[email protected] · Post by **[email protected]** » Fri 30 Apr 2010 17:55

I've been using the SDAC components for a while now with no problems, prior to this I was working with Oracle.

I have one issue which still eludes me. Within Oracle I could create a trigger which on Insert/Update/Delete of a record would push a customizable notificiation of that change to any connected instances of my application so that the application would be displaying current data.

How do I replicate this functionality with SDAC/MS SQL 2005? Is it something to do with change notification? If so, where can I find some documentation and sample code?

Application is written in Delphi6; but to be honest anything would help at the moment!

TIA,

Mark.

Dimon · Post by **Dimon** » Wed 05 May 2010 07:04

SDAC supports SQL Server Notification Services with the help of the TMSChangeNotification component. The TMSChangeNotification component is used to register queries with the database and receive notifications in response to DML or DDL changes on the objects associated with queries. You can assign a TMSChangeNotification object to the TMSQuery.ChangeNotification property of the dataset you want to be notified about changes.
You can find more detailed information about this component in the SDAC help.
Also you can look at the example of using it in the ChangeNotification demo in SDAC General Demo.

[email protected] · Post by **[email protected]** » Mon 17 May 2010 06:59

Do you know whether the change notification merely tells you the query has changed, or whether it's specific to which record in the record set?

Dimon · Post by **Dimon** » Mon 17 May 2010 08:08

TMSChangeNotification is notified about any changes made in the resultset, and it does not get information about changed record number.

[email protected] · Post by **[email protected]** » Mon 17 May 2010 19:43

So all you would know is that something in the record set had changed and therefore that it needed to be refreshed?

That's hugely wasteful of resources. I create a query and then load an object in memory for each row. What I'm after is some way of notifying the application that a given row has changed so that that individual object can be reloaded. Can you suggest a way I can acheive this effect with MS SQL?

Dimon · Post by **Dimon** » Wed 19 May 2010 08:28

You can specify that TMSQuery should be automatically refreshed when the underlying data on the server is changed. For this set the MSQuery.ChangeNotification property and the MSQuery.Options.ReflectChangeNotify property to True.
To refresh a dataset quickly your table should contain a timestamp field to use the RefreshQuick method instead of Refresh.