My application needs to be alerted of changes in the database. If one client creates a new record or changes an existing one, then all other clients need to reflect the changes almost immediately, or at least within the next few seconds.
In my old VB6 app (based on Access databases using DAO) I do this by quering the file date/time stamp of the database every 5 seconds. If it changed clients would update their query.
What would be best way to do this in SQLitening? I noticed the slGetChangeCount function. Would calling this function be the best way to be alerted if rows were added or changed. Or are there any other or better scenarios?
Quote from: Rolf Brandt on June 24, 2009, 06:27:25 AM... I noticed the slGetChangeCount function. Would calling this function be the best way to be alerted if rows were added or changed.
That function is intended to report changes resulting from the last INSERT/UPDATE/DELETE statement that the instant process has executed. So calling it from one workstation would only report changes initiated by that workstation.
Quote from: Rolf Brandt on June 24, 2009, 06:27:25 AM... ... quering the file date/time stamp of the database every 5 seconds. If it changed clients would update their query.
It should be very easy to do something similar with SQLite/SQLitening. You can create a TRIGGER that will update a date/time stamp for every record that is inserted/updated. You can use a WM_TIMER function to scan for date/time stamps newer than the last checkpoint.
Checking the file date-time stamp will also work with SQLite. You would
need to write a SQLiteningProc to be able to do it in both local and
remote mode. It would return the date-time of the database file.
The file date-time stamp solution works if you want to know if ANY of the
tables changed. If your were only interested in certain tables then Bern's
trigger solution would be better.
Thanks Fred, thanks Bern.
Just to let you know...
I used the trigger solution.
I added a table 'LastUpdate' with a field 'time'. Whenever in the 'order' table a record is added, updated, or deleted the trigger write a time stamp into the 'time' field.
CREATE TRIGGER [RecUpdate]
FOR EACH ROW
UPDATE LastUpdate SET Time = datetime('now') WHERE Time >= '';
The same applies for INSERT and DELETE.