• Welcome, Guest. Please login.
 
September 17, 2019, 09:46:20 am

News:

Welcome to the SQLitening support forums!


Alert of changes in table

Started by Rolf Brandt, June 24, 2009, 06:27:25 am

Previous topic - Next topic

Rolf Brandt

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
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

Bern Ertl

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.


Fred Meier

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. 

Rolf Brandt

I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu

Rolf Brandt

June 25, 2009, 05:36:10 am #4 Last Edit: June 25, 2009, 06:55:21 am by Rolf Brandt
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...
I like to cook with wine - sometimes I even add it to the food.
www.rbsoft.eu