--- In IBObjects@y..., Markus Ostenried <chef_007@g...> wrote:

> Hi Tony,
>

Markus, thanks for your gently and competent reply.
THANKS

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@t...]
> >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 ?