Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 10 total
Thread "pushing" database changes to clients?
Mon, Nov 20 2006 4:31 AMPermanent Link

=?iso-8859-1?Q?Thomas_Eg_J=F8rgensen?=
Hi,

We have a application running in a client/server setup. Either in a
Terminal Services enviroment or in a normal client/server setup in a LAN
network. Our application is showing "real time" information on the users
screen. This information changes at random and each client-application
have no chance of knowing when it changes. Today we have the DBISAM
database located on a network share, but we are considering using a
dedicated database-server application to do the job...

Today we handle this by doing a complete reload of all the data every 1
minut. But we are talking about a rather large amount of data, so this
is a very "cpu/network trafic expensive" solution. This solution also
raises the issue about wheter or not the information on the clients
screen is up-to-date at a specific moment or not? So instead I'd rather
have a solution which "pushed" the changes to the clients telling them
"Hi, please note that row XX in table YY changed, please update your
display". A way of telling all the other client application when one
client changed some data...

I'm guessing that i'm not the only one having these problems and i was
wondering if any of you would like to share ideas about how solve this
type of problem...?

My initial idea was to use a dedicated server application which uses a
TCP connection to push these informations to the client, but i'm not if
this is "best practice"...?

Thanks in advance...

Best Regards
 Thomas Eg Joergensen
Mon, Nov 20 2006 4:40 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Thomas,

<< I'm guessing that i'm not the only one having these problems and i was
wondering if any of you would like to share ideas about how solve this
type of problem...? >>

Use triggers in the database server to log whenever a specific table is
modified (insert, update, or delete) in another "modification status" table
with one record per table that needs to be tracked.  Then, have the client
simply call check the "modification status" table via a:

ModStatusTable.Refresh;
LastModified:=ModStatusTable.FieldByName('LastModified').AsDateTime;

type of processing.  If the table has been modified since the last time it
was loaded to the client, then go ahead and grab the data from the database
server.

--
Tim Young
Elevate Software
www.elevatesoft.com

Mon, Nov 20 2006 9:20 PMPermanent Link

"Ralf Bertoldi"
Thomas Eg Jørgensen wrote:

> I'm guessing that i'm not the only one having these problems and i
> was wondering if any of you would like to share ideas about how solve
> this type of problem...?

in  a C/S app. I use a ServerSide procedure that is called every x
seconds.
Just ask the server for the "lastchanged" time of a table, compare it
to the stored "last update", and reload if necessary..

Client:
---------------------
Inside the timer event:

procedure Timer1Timer(Sender: TObject);
var
   dLastUpdateTime:TDateTime;
begin
     dLastUpdateTime := GetLastUpdate('anytable');
     if dLastStoredUpdateTime < dLastUpdateTime then
     begin
       dLastStoredUpdateTime := now;
       
        ... refresh, reload..

     end;
end;


function GetLastUpdateTime(myTable:string): TDateTime;
begin
 with dm1.DBS1 do
 begin
   RemoteParams.CreateParam(ftString,'table').AsString := myTable;
   try
     // statements to try
     CallRemoteProcedure('lastupdate');
     Result := RemoteParamByName('lastupdate').AsDateTime;
   except
     on e: Exception do
     begin
       MessageDlg('Error:'+e.message, mtError, [mbOK], 0);
     end;
   end;    // try/except
 end;
end;    //
---------------------
Server:

 if (AnsiCompareText(ProcedureName, 'lastupdate')=0) then
 begin
   tblTemp := TDBIsamTable.Create(self);
   try
   tblTemp.SessionName := ServerSession.SessionName;
   tblTemp.DatabaseName := Tempdatabase.DatabaseName;
   tblTemp.TableName :=
ServerSession.RemoteParams.ParamByName('table').AsString;
   tblTemp.Open;
   tblTemp.Refresh;
   ServerSession.RemoteParams.Clear;


ServerSession.RemoteParams.CreateParam(ftDateTime,'lastupdate').AsDateTi
me := tblTemp.LastUpdated;
   tblTemp.Close;
   finally
     tblTemp.Free;
   end;
 end



HIH
ralf
Mon, Nov 20 2006 10:02 PMPermanent Link

"Ralf Bertoldi"
Forgot some stuff..

In Client:

procedure Timer1Timer(Sender: TObject);
var
    dLastUpdateTime:TDateTime;
begin
      dLastUpdateTime := GetLastUpdate('anytable');
      if dLastStoredUpdateTime < dLastUpdateTime then
      begin
        dLastStoredUpdateTime := now;
   /* better use GetRemoteDateTime vs. now
      mostly not all clients have the correct time..*/
        
         ... refresh, reload..

      end;
end;


function GetLastUpdate(myTable:string): TDateTime;
begin
  with dm1.DBS1 do
  begin
    RemoteParams.CreateParam(ftString,'table').AsString := myTable;
    /* forgot the dbname..*/
    RemoteParams.CreateParam(ftString,'dbname').AsString  :=
dm1.database.RemoteDatabase;
    try
      // statements to try
      CallRemoteProcedure('lastupdate');
      Result := RemoteParamByName('lastupdate').AsDateTime;
    except
      on e: Exception do
      begin
        MessageDlg('Error:'+e.message, mtError, [mbOK], 0);
        ...
      end;
    end;    // try/except
  end;
end;    //

---------------------
Server:

/* .. create the database

TempDatabase := TDBISAMDatabase.Create(self);
try

 with TempDatabase do
 begin
   SessionName    := ServerSession.SessionName;
   DatabaseName   := 'tempdb';

ServerEngine.GetServerDatabase(ServerSession.RemoteParams.ParamByName('d
bname').AsString,
   TempDescription,TempPath);
   Directory := Temppath;
   connected      := True;
 end;    // with

....
....

  if (AnsiCompareText(ProcedureName, 'lastupdate')=0) then
  begin
    tblTemp := TDBIsamTable.Create(self);
    try
    tblTemp.SessionName := ServerSession.SessionName;
    tblTemp.DatabaseName := Tempdatabase.DatabaseName;
    tblTemp.TableName :=
ServerSession.RemoteParams.ParamByName('table').AsString;
    tblTemp.Open;
    tblTemp.Refresh;
    ServerSession.RemoteParams.Clear;
 
ServerSession.RemoteParams.CreateParam(ftDateTime,'lastupdate').AsDate
Ti me := tblTemp.LastUpdated;
    tblTemp.Close;
    finally
      tblTemp.Free;
    end;
  end

.....
....
finally
 TempDatabase.Free;
end;




HIH
ralf
Tue, Nov 21 2006 8:59 AMPermanent Link

=?iso-8859-1?Q?Thomas_Eg_J=F8rgensen?=
"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> skrev i en
meddelelse
news:E8693808-AD17-4316-91CD-FFE081E55CBB@news.elevatesoft.com...
> << I'm guessing that i'm not the only one having these problems and i
> was wondering if any of you would like to share ideas about how solve
> this
> type of problem...? >>
>
> Use triggers in the database server to log whenever a specific table
> is modified (insert, update, or delete) in another "modification
> status" table with one record per table that needs to be tracked.
> Then, have the client simply call check the "modification status"
> table via a:
>
> ModStatusTable.Refresh;
> LastModified:=ModStatusTable.FieldByName('LastModified').AsDateTime;
>
> type of processing.  If the table has been modified since the last
> time it was loaded to the client, then go ahead and grab the data from
> the database server.
>

Hi Tim,

Thanks for your input, but this approch still uses "pulling" rather than
"pushing"...? Of course it greatly reduce the amount of transfered data
and refreshing needs. But would a "pushing" solution not be a better
approch or is it just a lot of work for nothing?

Best Regards
 Thomas
Tue, Nov 21 2006 10:13 AMPermanent Link

FWIW, one of the apps I have written for a client has what is essentially
a large set of files, and they need to be sent to a number of remote
systems. I happen to use DBISAM for storage, but it is not part of the
data transferred. The method they use is that the application that updates
the data files also writes to an updates file with packets of data that
have changed. My app then takes that and sends it to the other end and the
client then applies the update to its copy of the data too. If the client
hasn't had updates for a while, then when it connects it skips the updates
and just gets the whole big file set and then gets the updates from then
on. That cuts out the long delay in getting all the updates which may not
be valid any more anyway.

Just a thought - more complex but an option.

/Matthew Jones/
Tue, Nov 21 2006 11:19 AMPermanent Link

"Johnnie Norsworthy"
I don't remember the message, but I have the impression that ElevateDB will
have some kind of messaging built in that may make this type of flagged
change system easy to implement.

I'd love an event on a table or query:
 .AffectedTableChanged (triggered when any underlying table was changed)

I'd also love a:
Session.MessageReceived(Msg:String);
Session.SendMessage(SessionID,Msg:String);
Session.BroadcastMessage(Msg:String);

But I might roll those myself after ElevateDB is out.

Session.BroadcastMessage('The server will be shut down for maintenance at
12:00AM');

-Johnnie

Tue, Nov 21 2006 1:14 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Johnnie,

<< I don't remember the message, but I have the impression that ElevateDB
will have some kind of messaging built in that may make this type of flagged
change system easy to implement. >>

Not initially, but it is slated for inclusion as soon as possible.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tue, Nov 21 2006 1:15 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Thomas,

<< Thanks for your input, but this approch still uses "pulling" rather than
"pushing"...? Of course it greatly reduce the amount of transfered data
and refreshing needs. But would a "pushing" solution not be a better approch
or is it just a lot of work for nothing? >>

The problem with pushing is what to do about clients that have been offline
for a period of time, or clients that are very busy doing other things at
the moment.  These scenarios cause problems with pushing that pulling
doesn't suffer from.

--
Tim Young
Elevate Software
www.elevatesoft.com

Wed, Nov 22 2006 4:25 PMPermanent Link

"Norman W. Clark [Clark-Tech Inc.]"
Faced with similar situations with multi-user/terminal server/LAN applications,
I have used the kbmMW messaging features to post a message to all 'affected'
users of a change in their view of the data and upon receipt of the message, the
'affected' users then pull/refresh the data.  This technique minimizes the
network traffic all around.   Such message traffic on the LAN is negligible
compared to a timed, brute-force Table/Query refresh.  By 'affected', I refer to
changes in a table that should be relevant to only some users and not others.
You can get as sophisticated as you want in this approach.

Things can get a little interesting when you have an application running on
Terminal Services as well as on a LAN/WAN and they are accessing a common set of
tables.  Hint - think hub/spoke messaging ....

..... Ahh the things we end up doing just so our clients enjoy a responsive
application - AND to distinguish ourselves from our less informed (i.e.
non-DBISAM) developers.

--
.... Norm

Norman W. Clark, Clark-Tech Inc.
nclark@clark-tech.com
www.clark-tech.com
www.smbproducts.com
Tel: 403-297-9797



"Thomas Eg Jørgensen" <thomas@hest.notaplan.com> wrote in message
news:EA00C380-8F1D-44B7-A2E2-B86F8C9CDDFB@news.elevatesoft.com...
> Hi,
>
> We have a application running in a client/server setup. Either in a Terminal
> Services enviroment or in a normal client/server setup in a LAN network. Our
> application is showing "real time" information on the users screen. This
> information changes at random and each client-application have no chance of
> knowing when it changes. Today we have the DBISAM database located on a
> network share, but we are considering using a dedicated database-server
> application to do the job...
>
> Today we handle this by doing a complete reload of all the data every 1 minut.
> But we are talking about a rather large amount of data, so this is a very
> "cpu/network trafic expensive" solution. This solution also raises the issue
> about wheter or not the information on the clients screen is up-to-date at a
> specific moment or not? So instead I'd rather have a solution which "pushed"
> the changes to the clients telling them "Hi, please note that row XX in table
> YY changed, please update your display". A way of telling all the other client
> application when one client changed some data...
>
> I'm guessing that i'm not the only one having these problems and i was
> wondering if any of you would like to share ideas about how solve this type of
> problem...?
>
> My initial idea was to use a dedicated server application which uses a TCP
> connection to push these informations to the client, but i'm not if this is
> "best practice"...?
>
> Thanks in advance...
>
> Best Regards
>  Thomas Eg Joergensen

Image