Auto refresh a TDataSet / DBGrid

Question

I'm developing a software that displays information in a DBGrid via a TSimpleDataSet (dbExpress components)

The software in question is used on 2 different computers by 2 different people.

They both view and edit the same information at different times. I'm trying to figure out a way to automatically update the DBGrid (or rather, the DataSet, right?) on Computer B once Computer A makes a change to a row (edits something/whatever) and vice-versa.

Currently I've set up a TButton named Refresh that once clicked executes the following code:

procedure TForm2.actRefreshDataExecute(Sender: TObject);

begin
    dbmodule.somenameDataSet.MergeChangeLog;
    dbmodule.somenameDataSet.ApplyUpdates(-1);
    dbmodule.somenameDataSet.Refresh;
    dbmodule.somename1DataSet.MergeChangeLog;
    dbmodule.somename1DataSet.ApplyUpdates(-1);
    dbmodule.somename1DataSet.Refresh;
    dbmodule.somename2DataSet.MergeChangeLog;
    dbmodule.somename2DataSet.ApplyUpdates(-1);
    dbmodule.somename2DataSet.Refresh;
    dbmodule.somename3DataSet.MergeChangeLog;
    dbmodule.somename3DataSet.ApplyUpdates(-1);
    dbmodule.somename3DataSet.Refresh;
end;

This is fine and works as intended, once clicked. I'd like an auto update feature for this, for example when Computer A edits information in a row, Computer B's DBGrid should update it's display accordingly, without the need to click the refresh button.

I figured I would use a TTimer and set it at a specific interval, on both software on both PC's.

My actual question is:

Is there a better way than a TTimer for this? If so, please elaborate. Also, if the TTimer route is the way to go any further info you might find useful to state would be appreciated (pro's and con's and so on)

I'm using Rad Studio 10 Seattle and dbExpress components, the datasets connect to a MySQL database on my hosting where my website is.

Thanks!

Answer 1

Well, Ken White and Sertac Akyuz are certainly correct that using a server-originated notification to determine when to refresh your local dataset is preferable to continually re-reading all the data you are using from the server.

The problem AFAIK is that there is no Emba-supplied notification system which works with MySql. See this list of databases supported by FireDAC's Database Alerts:

http://docwiki.embarcadero.com/RADStudio/XE8/en/Database_Alerts_(FireDAC)

and note that it does not list MySql.

Luckily, I think there is a work-around which should be viable for a v. small system like yours currently is. As I understand it, you and your colleague's PCs are on a LAN and the MySql Server is outside your LAN and on the internet. In that situation, it doesn't need a round trip to the server for one of you to get a notification that the other has changed something in the database. Using an analogy akin to Ken's, you can, as it were, lean over the desk and say to your colleague "Hey, I've changed something, so you need to refresh your data."

A very low-tech way of implementing that would be to have somewhere on your LAN a resource that both of you can easily get at, which you can update when you make a change to the DB that means that the other of you should update your data from the server. One way to do that is to have a small, shared datafile with a number of records in it, one per server db table, which has some sort of timestamp or version-ID number which gets updated when you update the corresponding server table. Then, you can periodically check (poll) this datafile to see whether a given table has changed since you last checked; obviously, if it has, you then re-read the data you want from it from the server and update your local record of the info you read from the shared file.

You can update the shared file using handlers for the events of your Delphi client-side datasets.

There are a number of variations on this theme that I'm sure will be apparent to you; the implementational details really don't matter.

To update the shared file I'm talking about, you will need to lock it while writing to it. This answer:

How do I get the handle for locking a file in Delphi?

will show you how to do that.

Of course, the shared local resource doesn't have to be a data file. One alternative would be to use a Microsoft Message Queue service, which is sometimes used for this kind of thing, but has a steeper learning curve than a shared data file.

By the way, this kind of thing is far easier to do (at least on a small scale like you have) if you use 3-tier database access (e.g. using datasnap). In a three tier system, only the middle tier (a Delphi datasnap server which you write, but it's not that hard) talks to the server, and the clients only talk to the middle tier. This makes it easy for the middle tier server to notify the other client(s) when one of them changes the db data.

The three-tier arrangement also helps minimise the security problems with accessing a database server via the internet, because you only need one secure connection to the server, not one per client. But that's straying a bit far from your immediate problem.

I hope all this is clear, if not, ask.

Answer 2

Just use a timer and make it refresh the dataset every 5 min. No big deal. If the usage is not frequent then you can set it to fire every 10 or 15 min. There is nothing wrong with the timer if it set on longer intervals. Today's broadband connection's can easily handle the traffic so can Access. If the table is not huge of course.