Board index » delphi » Cached Updates Master Detail

Cached Updates Master Detail

Can anybody clear up the evident confusion with cached updates in a master
detail setting.

I want to have a few master records, which I want to be able to create
and/or edit as part of a batch.

Each master record may have a few detail records, which I want to be able to
create/ and or edit as I add or edit the master, or alternatively when I
scroll through the master records.

Finally, I want the user to be able to save or cancel the whole batch.

The problem is that when the user moves the cursor on the master query, the
updates for the detail query are seemingly lost. If ApplyUpdates is called
for the detail then all is well - but the user has lost the ability to
cancel the whole batch which is key to the whole thing.

There seem to have been a lot of posts which share this problem. Can anybody
(TEAM B??!!) tell me whether I am trying to achieve the impossible?

 

Re:Cached Updates Master Detail


When the user moves the cursor on the master the BDE is going to want to
do something with the detail records. if you don't apply any updates,
they will be lost. If you do apply the updates, you can't roll them back
once you move to a different master record. Frankly, it seems to me that
what you need is temp tables that will hold the master/detail records,
allow the user to navigate/edit/cancel at will, then when you're done,
save or cancel them.

Juan

Quote
pjmh wrote:

> Can anybody clear up the evident confusion with cached updates in a master
> detail setting.

> I want to have a few master records, which I want to be able to create
> and/or edit as part of a batch.

> Each master record may have a few detail records, which I want to be able to
> create/ and or edit as I add or edit the master, or alternatively when I
> scroll through the master records.

> Finally, I want the user to be able to save or cancel the whole batch.

> The problem is that when the user moves the cursor on the master query, the
> updates for the detail query are seemingly lost. If ApplyUpdates is called
> for the detail then all is well - but the user has lost the ability to
> cancel the whole batch which is key to the whole thing.

> There seem to have been a lot of posts which share this problem. Can anybody
> (TEAM B??!!) tell me whether I am trying to achieve the impossible?

Re:Cached Updates Master Detail


Am I the only one who thinks this is a significant limitation?
Quote
Juan Jimenez wrote in message <36362C2E.7E681...@caribe.net>...
> if you don't apply any updates,
>they will be lost. If you do apply the updates, you can't roll them back

>Juan

Re:Cached Updates Master Detail


Maybe you can try using TDatabase.StartTransaction before user make
changes.

After changes, use TDatabase.Commit to validate Data, or TDatabase.RollBack
to cancel it.

Steph.

Re:Cached Updates Master Detail


Quote
"pjmh" <p...@aapi.co.uk> wrote:
>Am I the only one who thinks this is a significant limitation?

Maybe<G>.

But maybe there are other ways to think the problem through.

First of all, transaction processing is tough for everyone, especially
in the client/server world. It's a constant topic for discussion and I
don't think anyone has come up with The Golden Idea that solves all
OLTP problems with one technology. So the "significant limitation" is
a true but theoretical observation. Delphi is no more deficient here
than anyone else. On to more practical matters.......

A transaction is commonly thought of as a related group of records
treated as one, coordinated change. The meaning of "related" is left
to the programmer, but the common usage in a master/detal relationship
is that a transaction is bounded by a change in the master. That is,
the coordination that is required is that the details always group
with the master.

Your problem is that you want to group many masters. Happily, I think
Delphi allows for that, if you have a cooperative database engine. So
I'm now going to suggest a way that Delphi might do that, but I've
never really tried it. However, it should work this way (in
psuedo-code):

1. Start a transaction (e.g. TDatabase.StartTransaction)

2. if Done, go to step 7.

3. Select and Edit a master/detail

4. ApplyUpdates to master and details

5. If updates are ok, then CommitUpdates for detail and master.

5a. RevertRecord or CancelUpdates as necessary.

6. Go to step 2

7. If you want to discard the lot Roll them back. (e.g.
TDatabase.Rollback) and go to step 9.

8. End the transaction (e.g. TDatabase.Commit.)

9. Exit.

Here's what's happening. You really have two transactions going, one
nested inside the other. The outer transaction is the database
transaction (Steps 1,7,8). The inner transaction is the cache for
cached updates (Steps 2-6).

The database is unaware of the inner transaction because the changes
are kept in the local cache. But using the cache this way, you get the
benefit of the traditional transaction concept for the master/detail.
Each inner transaction governs one master/detail relationship.

When you ApplyUpdates, the database catches all the changes for the
one master/detail data and the cache is flushed, ready for the next
master/detail.

But you don't commit the database. You leave the database transaction
open until you are finished with all the master/details you have to
change. Only then do you decide to keep (Commit) or throw away
(Rollback) all the changes to all the master/detail records.

Normally, we don't use cached updates this way. The original design
goal is to allow the user to make changes locally and then to update
the database in a single, short burst of updating. That's why we
usually wrap the ApplyUpdates in a database transaction. We do this
because we want to manage traffic on the server and ensure that new
data is updated cleanly so that other users get the best benefit of
data. We don't want to deal with lockouts or sloppy updates that span
coffee breaks or lunch hours.

But we're saying that we don't care about that so much here and that
we'd rather have broader user control and we're willing to sacrifice
accessibility for more local control.

This is where the cooperative database comes in. Some databases
tolerate this kind of open-ended treatment. Less well designed
databases will lock every other user out of every table you are using
until Commit or Rollback. Then again, you may not care because in this
case, you might know that the business environment of the transaction
won't cause such a conflict. Those are the trade-offs that we all have
to make all the time, and you are the one who has to make the call
here.

Hope this helps.

Phil Cain

Re:Cached Updates Master Detail


Popular topic.  If you link the detail TQuery to the master TQuery using the
TQuery.DataSource property, the detail cache will be flushed on master
navigation because the detail TQuery is closed and opened for each row in
the master.

To have an effective master/detail relationship that allows navigation in
the master, you must NOT use the TQuery.DataSource property to link the
datasets.  One possible alternative is to use a TDataSource.OnDataChange
event handler on the master to programmatically set the detail query's
Filter property.  If you filter rather than close/open the detail query, the
detail cache will not be flushed.

Watch out for performance issues with the detail query.  Make sure your
application is designed such that the maximum number of detail rows across
ALL master rows that can be navigated is limited.  We have found that 2000
is a good limit to use; this of course depends upon your application.

We have successfully used a similar technique for multi-level nested
master-detail relationships.

V/R
Russell L. Smith

Quote
pjmh wrote in message <7156f0$b...@forums.borland.com>...
>The problem is that when the user moves the cursor on the master query, the
>updates for the detail query are seemingly lost. If ApplyUpdates is called
>for the detail then all is well - but the user has lost the ability to
>cancel the whole batch which is key to the whole thing.

Re:Cached Updates Master Detail


BTW, a note on database transactions:

If you use database transactions to assist with the master/detail
relationship, the application design must constrain the user to operate
within a single "saveable" set of forms per database connection.

Our applications have multiple non-modal forms with nested master-detail
relationships on each form.  Each form can be saved independently of the
other (similar to editing multiple Word documents at one time).  As a
result, a database transaction cannot be held open for more than the amount
of time required to apply a single form's updates.

V/R
Russell L. Smith

Quote
pjmh wrote in message <7156f0$b...@forums.borland.com>...
>The problem is that when the user moves the cursor on the master query, the
>updates for the detail query are seemingly lost. If ApplyUpdates is called
>for the detail then all is well - but the user has lost the ability to
>cancel the whole batch which is key to the whole thing.

Re:Cached Updates Master Detail


Quote
Philip Cain wrote in message <3637396e.2031...@forums.borland.com>...
>"pjmh" <p...@aapi.co.uk> wrote:

>Normally, we don't use cached updates this way.

Nor Us!!

Quote
>This is where the cooperative database comes in. Some databases
>tolerate this kind of open-ended treatment. Less well designed
>databases will lock every other user out of every table you are using
>until Commit or Rollback. Then again, you may not care because in this
>case, you might know that the business environment of the transaction
>won't cause such a conflict. Those are the trade-offs that we all have
>to make all the time, and you are the one who has to make the call
>here.

And this is the root of the problem. SQL Server (6.5 - although 7 appears no
better in this case) cannot be described as co-operative. The locks escalate
quickly no matter what we try to do to stop them (NO LOCK, ROW LOCK etc.
etc.) even though the underlying business process means that contention
would be very unlikely if not impossible.

Incidentally, at first sight MIDAS appears to be one way out of the problem,
allowing the user to move between master records while holding changes to
the detail records in the cache.... watch this space

Re:Cached Updates Master Detail


Of the database, yes, of Delphi, that depends on where you stand when you
look at it. You'll have the same problems with other tools as well, unless
they internally perform the workaround I suggested. Not to say there are
not -other- workarounds, of course.

Juan

Quote
pjmh wrote in message <716o8e$d...@forums.borland.com>...
>Am I the only one who thinks this is a significant limitation?

>Juan Jimenez wrote in message <36362C2E.7E681...@caribe.net>...
>> if you don't apply any updates,
>>they will be lost. If you do apply the updates, you can't roll them back

>>Juan

Other Threads