SQLitening Support Forum

SQLitening Support => You've got Questions? We've got Answers! => Topic started by: Rolf Brandt on June 24, 2009, 06:27:25 AM

Title: Alert of changes in table
Post by: Rolf Brandt on June 24, 2009, 06:27:25 AM
Hi,

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?

Rolf
Title: Re: Alert of changes in table
Post by: Bern Ertl on June 24, 2009, 10:19:21 AM
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.

Title: Re: Alert of changes in table
Post by: Fred Meier on June 24, 2009, 02:45:04 PM
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. 
Title: Re: Alert of changes in table
Post by: Rolf Brandt on June 24, 2009, 05:44:49 PM
Thanks Fred, thanks Bern.

Rolf
Title: Re: Alert of changes in table
Post by: Rolf Brandt on June 25, 2009, 05:36:10 AM
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]
AFTER UPDATE
ON [Order]
FOR EACH ROW
BEGIN
UPDATE LastUpdate SET Time = datetime('now') WHERE Time >= '';
END

The same applies for INSERT and DELETE.
Works fine...