Board index » delphi » Cached updates with multi master/detail levels using TQuery and TStoredProc

Cached updates with multi master/detail levels using TQuery and TStoredProc

Hello all,

I am in search of tips on how to manage more than 1 level of master/detail
relation using TQuery, TStoredProc and Cached updates.  When browsing
records on a query result set that has detail, these descendant query(ies)
are refreshed thus all cached updates are wiped out.

Does anyone have tips or tricks to get around this situation.

thks & regards

Bernard

 

Re:Cached updates with multi master/detail levels using TQuery and TStoredProc


I'm using cached updates on the master table and live connections (TTables
using master-detail connections) for the multiple dependent datasets that I
open and close as I need them, and this seems to work fairly well for both
Pdox and MS SQL Server.  Hope this helps...
Quote
>I am in search of tips on how to manage more than 1 level of master/detail
>relation using TQuery, TStoredProc and Cached updates.  When browsing
>records on a query result set that has detail, these descendant query(ies)
>are refreshed thus all cached updates are wiped out.

Re:Cached updates with multi master/detail levels using TQuery and TStoredProc


Quote
>>  When browsing records on a query result set that has detail, these descendant query(ies)
>>are refreshed thus all cached updates are wiped out.

I've worked around this by managing the parameters of details from within the
masters OnAfterScroll event handler.

ie:

procedure TForm1.Query1AfterScroll(DataSet: TDataSet);
begin
  // get the detail records for Query1
  qDetail.Close;
  qDetail.ParamByName('SomeMasterField').asString := query1.FieldByName('SomeField').asString;
  qDetail.Prepare;
  qDetail.Open;
end;

You'll need to prevent the user from navigating the master tables when a child dataset has
updates pending.

They will have to  save / cancel changes prior to enabling master dataset navigation... ie:

myMasterDBGrid.Enabled := not someDetail.updatesPending;

=Bill=

Re:Cached updates with multi master/detail levels using TQuery and TStoredProc


Quote
In article <6r3418$d...@forums.borland.com>, "WmBurke(TeamB)" <no_spam_wm25bu...@earthlink.net> wrote:
>>>  When browsing records on a query result set that has detail, these
> descendant query(ies)
>>>are refreshed thus all cached updates are wiped out.

>I've worked around this by managing the parameters of details from within the
>masters OnAfterScroll event handler.

>ie:

>procedure TForm1.Query1AfterScroll(DataSet: TDataSet);
>begin
>  // get the detail records for Query1
>  qDetail.Close;
>  qDetail.ParamByName('SomeMasterField').asString :=
> query1.FieldByName('SomeField').asString;
>  qDetail.Prepare;

This is dangerous here.  You are calling prepare over and over without
unpreparing the query.  This will cause two things - 1 resource leaks and 2 -
slow down of performance.  Prepare only needs to be called once per SQL
statement.  In otherwords if you haven't changed the SQL you don't need to
reprepare the query.  With code like this it is a good idea to prepare it but
just once is ok.  When you open a Query, if it is unprepared, the query
automatically Prepares it, remembers it prepared it, and unprepares it on
close.  Safer code would be

  if not qDetail.Prepared then
    qDetail.Prepare;

This way you only prepare once.

Quote
>  qDetail.Open;
>end;

>You'll need to prevent the user from navigating the master tables when a child
> dataset has
>updates pending.

>They will have to  save / cancel changes prior to enabling master dataset
> navigation... ie:

>myMasterDBGrid.Enabled := not someDetail.updatesPending;

>=Bill=

Jeff Overcash

Re:Cached updates with multi master/detail levels using TQuery and TStoredProc


You've described the #1 limitation to using cached updates. There is no
way around this at the current time. At least not using cached updates.
If you're using Delphi C/S, you could port your logic to use
ClientDatatset technology. I wrote an article about how to do something
like this in the 4/98 issue of Delphi Informant. That's probably you're
best bet to get up and running quickly.

Good luck.
--
Dan Miser
http://www.execpc.com/~dmiser

Re:Cached updates with multi master/detail levels using TQuery and TStoredProc


You could implement master-detail using filter property of you detail TQuery
component. Just change the filter OnDataChange event of the master query.
In this case your changes are not lost.

Dimitry Fayerman.

Quote
Bernard wrote in message <01bdc6e1$0a4865e0$990da8c0@pbms03>...
>Hello all,

>I am in search of tips on how to manage more than 1 level of master/detail
>relation using TQuery, TStoredProc and Cached updates.  When browsing
>records on a query result set that has detail, these descendant query(ies)
>are refreshed thus all cached updates are wiped out.

>Does anyone have tips or tricks to get around this situation.

>thks & regards

>Bernard

Re:Cached updates with multi master/detail levels using TQuery and TStoredProc


In article <VA.0000012b.02fbe...@dmisernt.comps.com>, dmi...@execpc.com
says...

Quote
> You've described the #1 limitation to using cached updates. There is no
> way around this at the current time. At least not using cached updates.
> If you're using Delphi C/S, you could port your logic to use
> ClientDatatset technology. I wrote an article about how to do something
> like this in the 4/98 issue of Delphi Informant. That's probably you're
> best bet to get up and running quickly.

> Good luck.
> --
> Dan Miser
> http://www.execpc.com/~dmiser

A solution is to not use the datasource property of the detail queries
but to synchronize the master and details manually.
It only takes a few lines of code in e.g. AfterScroll and AfterCancel
handlers and you have full control over your queries.

Volker Wilhelm
Compu-Orga GmbH

Re:Cached updates with multi master/detail levels using TQuery and TStoredProc


Quote
Dimitry Fayerman wrote:

> You could implement master-detail using filter property of you detail TQuery
> component. Just change the filter OnDataChange event of the master query.
> In this case your changes are not lost.

> Dimitry Fayerman.

> Bernard wrote in message <01bdc6e1$0a4865e0$990da8c0@pbms03>...
> >Hello all,

> >I am in search of tips on how to manage more than 1 level of master/detail
> >relation using TQuery, TStoredProc and Cached updates.  When browsing
> >records on a query result set that has detail, these descendant query(ies)
> >are refreshed thus all cached updates are wiped out.

> >Does anyone have tips or tricks to get around this situation.

> >thks & regards

> >Bernard

Had tried it before posting but without success.  Retested the hole
thing after your suggestion and it seems to work!

It's too bad that I have to code what I call useless programming, since
the solution should not be so complicated to implement in the native
components... I just want to emulate something like the UpdateRecordType
method in order for me to flag a record visible or not (to INPRISE...
this sould be done automaticaly within a master/detail relation using
cached updates instead of refreshing detail queries).  I guess filtering
will have to do for now, until a better solution comes by!!!

Thanks, Bernard

Other Threads