Hi Tony,

I wrote this a few days ago. Maybe it helps.

Bye,
Markus


-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-


Subject: RE: multiuser / simultanous updates in dbgrid ? - long

Hi Bruno,

we had this problem, too, although we're using Jason Wharton's IBObjects
(www.IBObjects.com). With Jason's advise we built a "notifying system":

You need to have one table "IBO$DMLCache" that contains information
about all the updates you're interested in.
Its structure is:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

/* Table */

CREATE TABLE IBO$DMLCACHE(
DMLID INTEGER NOT NULL,
DMLACTION CHAR(1) NOT NULL, // values are I(nsert),
U(pdate) and D(elete)
KEYFIELDS VARCHAR(128) NOT NULL, // column names of the
keyfields, e.g. 'PL_ID_PLACE' or 'Field1;Field2'.
// this is needed
to determine on the client side which dataset
// needs to be
refreshed
KEYVALUE1 VARCHAR(128) NOT NULL, // values of the fields specified
in "KeyFields"
KEYVALUE2 VARCHAR(128) ,
KEYVALUE3 VARCHAR(128) ,
KEYVALUE4 VARCHAR(128) ,
DMLUSER VARCHAR(32) NOT NULL, // the InterBase user name
DMLTIMESTAMP DATE NOT NULL,
PRIMARY KEY (DMLID)
);


/* Triggers */

SET TERM ^;

CREATE TRIGGER BI_IBO$DMLCACHE FOR IBO$DMLCACHE ACTIVE BEFORE INSERT
POSITION 0 AS
DECLARE VARIABLE XCACHECOUNTER INTEGER;
BEGIN
// supply missing values
IF ( NEW.DMLID IS NULL ) THEN BEGIN
NEW.DMLID = GEN_ID( GEN_IBO$DMLCACHE, 1 );
END
IF ( NEW.DMLUSER IS NULL ) THEN BEGIN
NEW.DMLUSER = USER;
END
IF ( NEW.DMLTIMESTAMP IS NULL ) THEN BEGIN
NEW.DMLTIMESTAMP = 'NOW';
END

// TBL_DBCTRL contains a record counter for this table because doing a
SELECT Count(*)
// everytime would be to slow
IF (NOT EXISTS (SELECT INTVALUE
FROM TBL_DBCTRL
WHERE FLDNAME = 'CACHECOUNTER')) THEN BEGIN
INSERT INTO TBL_DBCTRL (FLDNAME, INTVALUE)
VALUES ('CACHECOUNTER', 0);
END

SELECT INTVALUE
FROM TBL_DBCTRL
WHERE FLDNAME = 'CACHECOUNTER'
INTO :XCACHECOUNTER;

// if the counter exceeds the limit then all but the last 50 records
will be deleted
// and the counter is reset to 0
IF (XCACHECOUNTER >= 300) THEN BEGIN
EXECUTE PROCEDURE PROC_CLEAR_DMLCACHE;
UPDATE TBL_DBCTRL
SET INTVALUE = 0
WHERE FLDNAME = 'CACHECOUNTER';
END ELSE BEGIN
UPDATE TBL_DBCTRL
SET INTVALUE = :XCACHECOUNTER + 1
WHERE FLDNAME = 'CACHECOUNTER';
END
END
^

// this Trigger posts the event so that all applications connected to the
database get notified of the change
CREATE TRIGGER AI_IBO$DMLCACHE FOR IBO$DMLCACHE ACTIVE AFTER INSERT
POSITION 0 AS
BEGIN
POST_EVENT 'IBO$DMLCACHE';
END
^

SET TERM ;^
COMMIT;
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Now you need for every table you want to know its updates 1 to 3 trigger,
for example
for a table "Places" you would add these triggers:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SET TERM ^;

CREATE TRIGGER AI_PLACES_DMLC FOR PLACES ACTIVE AFTER INSERT POSITION 0 AS
BEGIN
INSERT INTO IBO$DMLCACHE(
DMLACTION,
KEYFIELDS,
KEYVALUE1)
VALUES (
'I',
'PL_ID_PLACE',
NEW.PL_ID_PLACE
);
END
^

CREATE TRIGGER AU_PLACES_DMLC FOR PLACES ACTIVE AFTER UPDATE POSITION 0 AS
BEGIN
INSERT INTO IBO$DMLCACHE(
DMLACTION,
KEYFIELDS,
KEYVALUE1)
VALUES (
'U',
'PL_ID_PLACE',
OLD.PL_ID_PLACE
);
END
^

CREATE TRIGGER AD_PLACES_DMLC FOR PLACES ACTIVE AFTER DELETE POSITION 0 AS
BEGIN
INSERT INTO IBO$DMLCACHE(
DMLACTION,
KEYFIELDS,
KEYVALUE1)
VALUES (
'D',
'PL_ID_PLACE',
OLD.PL_ID_PLACE
);
END
^

SET TERM ;^
COMMIT;

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

In your application you have to register the InterBase event "IBO$DMLCACHE"
and when
it is posted you have to get the last records from table IBO$DMLCACHE. Your
app can save
the last processed DMLID so the next time the event occurs you only select
DMLCache records
where (DMLID > :xLastDMLID).

Finally you select one DMLCache record into your app for each
insert/update/delete in db.
This DMLCache record provides you with the necessary information to update
your datasets
and db controls.
But the best of this method is: You don't have to refresh the whole Dataset
(fetch ALL records)
from the server to the client but you can update only one record in the
datasets buffer and
refresh the controls.

The client side stuff I mentioned here at last is done by IBObjects
automatically - you just have to
set a few properties.

But if you can live with a refresh ( = close & open of the dataset) then it
would be sufficient to
write a few triggers that only post IB events in updates.
You then can hook into these events on the client app an do your refresh.

HTH,
Markus

>-----Original Message-----
>From: Bruno Fierens [mailto:bruno.fierens@...]
>When multiple users have this grid with the EMPLOYEE table showing,
>editing
>of one user on one machine is not reflected on the grid of the second
>user
>on another machine. Can this be achieved ?