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!

Of course the better way is to be notified by the database server. For this, the database server, if there is one, need to provide such functionality and also the access driver need to support it. DBX probably won't, but it should be possible to employ an auxiliary connection. Like IBEventAlerter for instance for interbase/firebird. — Sertac Akyuz, Oct 27 '16 at 01:43
Why would this be a better way as opposed to the timer? Just out of curiosity. Also, I'm using MySQL and yes the datasets are connected to a database — t1f, Oct 27 '16 at 01:45
Your program need not hammer the server for nothing for every so often. Probably 99 percent of your refreshes are to return empty handed. If you stretch the interval to avoid that, then you'd undertake the risk of not notifying a change. — Sertac Akyuz, Oct 27 '16 at 01:48
Doing it the way you are requires repeated round trips to the server (for every call to MergeChangeLog, one for ApplyUpdates, and one for Refresh), the vast majority of which do nothing but waste network bandwidth and CPU cycles. Imagine papers moving around an office. Your code is the equivalent of running down the hall to a coworkers desk looking for new work, back to your desk, and immediately back down the hall again. Not seriously going to work, right? Using server notifications means sitting at your desk, working, and then having your coworker call you when work arrives. — Ken White, Oct 27 '16 at 01:49
@KenWhite Your example sent me in to a laughing frenzy, lol. So basically I can set up a server notification you mention and tie that in to my software so it will update (trigger the timer/Tbutton?) when it receives that notification? Hope I got that right. If so, I imagine it's a lot to do and wouldn't fit to this question so can you provide some links in a comment, so I can read and get started on this? Thanks! — t1f, Oct 27 '16 at 02:16
It doesn't trigger a timer or button. You just respond to the notification by refreshing the data in the event that catches the notification. The specific mechanism depends on the DB controls you're using and the DB drivers that are in use. Seattle's documentation related to dbExpress should give you information about registering for DB events if they're supported for your DBMS. — Ken White, Oct 27 '16 at 02:21
Will you and the other user(s) updating this database be on the same LAN? I realise you've said that the database is located remotely ... — MartynA, Oct 27 '16 at 08:14
@MartynA - Yes, the database is on a hosting that has my website and the PC's using the software and subsequently working with the data from the database are in our office, they connect to our office router for wireless internet, so yes, same network. — t1f, Oct 27 '16 at 14:26

score 2 · Accepted Answer · edited May 23 '17 at 12:33

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.

Extremely helpful, thank you, just read most of the stuff and will try it soon. — t1f, Oct 28 '16 at 01:08

score 1 · Answer 2 · answered Aug 08 '18 at 08:25

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.

Auto refresh a TDataSet / DBGrid

2 Answers2