Board index » delphi » Problems in CacheUpdates

Problems in CacheUpdates

I am a new comer in delphi database development, I hope someone can help me
in the issue of updating a master-detail form.

When a user creates a record, before he inserts the first item in detail, I
have to post the master dataset first, otherwise, violation of  foreign key
constraints occurs if the user tries to post the detail dataset. It's quite
inconvenient if the user want to cancel all updating because the master
record is already posted and I have to do the cleaning job after that.

In my mind, I hope that users can insert details items even the master
record has not been posted, until the user press a 'Save' button, all
changes in master and detail are saved. Because of this needs, I use
CachedUpdates to implement my application. But my nightmare begins when I
fall into this.

In case of insert by using CachedUpdates:
After the user entering all master and detail data, I use
Database.ApplyUpdates method to post all changes. Unfortunately, only master
data can be saved and I have to re-open the datasets to see the change. If I
change from TQuery to TTable, both master and detail data are saved and I
can see all changes on the screen immediately. I only know that TTable do a
better job than TQuery in this case, but I still want to use TQuery because
it gives me more things to control.

In case of delete by using CachedUpdates:
By using TQuery, when the user deletes a detail item, the deleted item
appears again after Database.ApplyUpdates is called. On the other hand, if
the user deletes the detail item and then the master, violation of  foreign
key constraints occurs after Database.ApplyUpdates is called.

Is there a proper way to use CacheUpdates? I really hope that someone can
give me hints on this issue because I have to handle many master-detail
forms in my application and your opinions are very appreciated.

Thank you for your kindly attention.

Cheers,

Neon.

 

Re:Problems in CacheUpdates


Neon,

Welcome to the cached-updates club!

First of all, I can tell you that queries work just fine with cached
updates and there is no need to continually refresh your datasets to
get what you want.

However, it might take a few messages here to get you straightened
out, so please be patient.

In order to help you, we need to know more about how you use your
code. Give an example. Tell us how your master and detail tables are
linked. Do you use the OnUpdateRecord event?  Also, it would be
helpful to see two bits of code in your example. The first is the code
that calls  ApplyUpdates and the second is the code in OnUpdateRecord.

One note that might be helpful at this point: Do you use the
CommitUpdates method. It must be used to flush the cache after you
ApplyUpdates and before you make any more changes to the data. If you
don't use CommitUpdates, that may be the reason you are still seeing
deleted data after ApplyUpdates.

Phil Cain

Quote
"NEON" <neon...@aelhk.com.hk> wrote:
>I am a new comer in delphi database development, I hope someone can help me
>in the issue of updating a master-detail form.

>When a user creates a record, before he inserts the first item in detail, I
>have to post the master dataset first, otherwise, violation of  foreign key
>constraints occurs if the user tries to post the detail dataset. It's quite
>inconvenient if the user want to cancel all updating because the master
>record is already posted and I have to do the cleaning job after that.

>In my mind, I hope that users can insert details items even the master
>record has not been posted, until the user press a 'Save' button, all
>changes in master and detail are saved. Because of this needs, I use
>CachedUpdates to implement my application. But my nightmare begins when I
>fall into this.

>In case of insert by using CachedUpdates:
>After the user entering all master and detail data, I use
>Database.ApplyUpdates method to post all changes. Unfortunately, only master
>data can be saved and I have to re-open the datasets to see the change. If I
>change from TQuery to TTable, both master and detail data are saved and I
>can see all changes on the screen immediately. I only know that TTable do a
>better job than TQuery in this case, but I still want to use TQuery because
>it gives me more things to control.

>In case of delete by using CachedUpdates:
>By using TQuery, when the user deletes a detail item, the deleted item
>appears again after Database.ApplyUpdates is called. On the other hand, if
>the user deletes the detail item and then the master, violation of  foreign
>key constraints occurs after Database.ApplyUpdates is called.

>Is there a proper way to use CacheUpdates? I really hope that someone can
>give me hints on this issue because I have to handle many master-detail
>forms in my application and your opinions are very appreciated.

>Thank you for your kindly attention.

>Cheers,

>Neon.

Re:Problems in CacheUpdates


Philip,

It's my pleasure to join the cached-updates club and thank you for your
reply.

In order to familiar with the cached-updates, I create two tables in
InterBase for testing.

Header          Item
----------              --------
Key1            Key1
Data1           Key2
Data2           Data1
                Data2

Header is the master table and Item is the detail table.
Key1 is  the primary key of Header and (Key1, Key2) is the primary key of
Item. And Item's Key1 is the foreign key of Header's Key1.

In my form, there are one TDatabase, two TQuery, two TDataSource, two
DBGrid, two TDBNavigator and two TButton.
The TQuery components (Query1 and Query2) are used for the two tables.
Their CachedUpdates are set to True.
The SQL string for Query1: 'select * from Header'
The SQL string for Query2: 'select * from Item where Key1 = :Key1'
The DataSource property of Query2 is set to the DataSource component of
Query1, so the two tables are linked.
One DBGrids is for Header and another is for Item. Everytime the user
clicks the 'Save' button on the form, it calls the code -
Database1.ApplyUpdates([Query1, Query2]). I do not use the OnUpdateRecord
Event for further handling. Another button is for terminating the program.

The problems I faces are:
1. Insert a master record
After the user clicking the 'Save' button, the newly inserted record is
disappeared. If I add codes to re-open Query1, I can see the new record but
the cursor does not pointed to it but the first record. If I use TTable
instead, it's discovered that all problems are gone - I can see the newly
inserted record immediately and the cursor points to it after
Database1.ApplyUpdates([Table1, Table2]) is called. Is it possible to let
the TQuery behaves like TTable in this case?

2. Insert a master record and a detail record
Basically, the problem is similar to above case - newly inserted data is
disappeared after 'Save' button is pressed. Unfortunately, the detail
record is lost even I refresh the tables. I think it's because the master
table is updated first, after it's updating, all DB-aware components are
refreshed so that the newly input detail data in the buffer is flushed. How
can I post the master and detail data properly? Its again that TTable can
work properly in this case.

3. Delete a master record and its detail record
By doing above operation, the foreign key constraint violates when the
'Save' button is pressed. I try to change the code from
Database1.ApplyUpdates([Query1, Query2]) to Database1.ApplyUpdates([Query2,
Query1]) but can't solve the problem. And also I try to use
Query2.ApplyUpdates and then Query1.ApplyUpdates instead, but still fails.
The only thing I can do is after deleting the details, press 'Save' button
- Database1.ApplyUpdates([Query1, Query2]), and after deleting the master,
press 'Save' button again. This time, it works fine. But the point is I
want to press once to save all changes, but how to do?

I struggle for above problems almost for a week, they may be simple to
someone, but is really hard for me. I really want to handle the
CachedUpdates issue in a good manner because it's very important for me to
develop database applications.

Thank you very much for your kindly attention. If possible, please give me
advices.

Regards,

Neon.

Philip Cain <philc...@orelle.com> wrote in article
<363ae1ec.3150...@forums.borland.com>...

Quote
> Neon,

> Welcome to the cached-updates club!

> First of all, I can tell you that queries work just fine with cached
> updates and there is no need to continually refresh your datasets to
> get what you want.

> However, it might take a few messages here to get you straightened
> out, so please be patient.

> In order to help you, we need to know more about how you use your
> code. Give an example. Tell us how your master and detail tables are
> linked. Do you use the OnUpdateRecord event?  Also, it would be
> helpful to see two bits of code in your example. The first is the code
> that calls  ApplyUpdates and the second is the code in OnUpdateRecord.

> One note that might be helpful at this point: Do you use the
> CommitUpdates method. It must be used to flush the cache after you
> ApplyUpdates and before you make any more changes to the data. If you
> don't use CommitUpdates, that may be the reason you are still seeing
> deleted data after ApplyUpdates.

> Phil Cain

> "NEON" <neon...@aelhk.com.hk> wrote:

> >I am a new comer in delphi database development, I hope someone can help
me
> >in the issue of updating a master-detail form.

> >When a user creates a record, before he inserts the first item in
detail, I
> >have to post the master dataset first, otherwise, violation of  foreign
key
> >constraints occurs if the user tries to post the detail dataset. It's
quite
> >inconvenient if the user want to cancel all updating because the master
> >record is already posted and I have to do the cleaning job after that.

> >In my mind, I hope that users can insert details items even the master
> >record has not been posted, until the user press a 'Save' button, all
> >changes in master and detail are saved. Because of this needs, I use
> >CachedUpdates to implement my application. But my nightmare begins when
I
> >fall into this.

> >In case of insert by using CachedUpdates:
> >After the user entering all master and detail data, I use
> >Database.ApplyUpdates method to post all changes. Unfortunately, only
master
> >data can be saved and I have to re-open the datasets to see the change.
If I
> >change from TQuery to TTable, both master and detail data are saved and
I
> >can see all changes on the screen immediately. I only know that TTable
do a
> >better job than TQuery in this case, but I still want to use TQuery
because
> >it gives me more things to control.

> >In case of delete by using CachedUpdates:
> >By using TQuery, when the user deletes a detail item, the deleted item
> >appears again after Database.ApplyUpdates is called. On the other hand,
if
> >the user deletes the detail item and then the master, violation of
foreign
> >key constraints occurs after Database.ApplyUpdates is called.

> >Is there a proper way to use CacheUpdates? I really hope that someone
can
> >give me hints on this issue because I have to handle many master-detail
> >forms in my application and your opinions are very appreciated.

> >Thank you for your kindly attention.

> >Cheers,

> >Neon.

Re:Problems in CacheUpdates


Neon,

Let's see the code under your "Save" button. Without seeing the code,
it's difficult, and often impossible, to be specific about the
problem.

In the meantime, here are some comments.

I suspect that the problem is that you've made a master/detail
relationship through the datasets. I don't do that and I'm happy with
the results. There may be some others here who can tell you how to
tune cached-updates when the datasets are linked, but I can't.

Here's what I do:
1. I always ApplyUpdates when the master changes and at no other time.
2. Because the datasets are not linked, I must refresh the detail
dataset in my code each time the master record changes.
3. I always ApplyUpdates by dataset (Query1.ApplyUpdates, for example)
and never by database.
4. I always include ApplyUpdates inside a database transaction. (I
will be able to comment on that when I see your code for "Save")

Do you use TUpdateSQL components? (If you do not, then
TQuery.RequestLive must be set to True.)
Do you get any messages besides the key violation message?

Because this is a master/detail relationship, you may have to use
OnUpdateRecord because the order of updates becomes important in a
master/detail. For example, when adding a new master, it's important
to add the master before adding any detail. When deleting, it's
important to delete the details first.

A note about disappearing records. Once I thought records were
disappearing on me. I added them at the bottom of a grid and, when I
pressed the "Save" button, they seemed to go away. But it turns out
they did not. Instead, they moved to another place on the grid. I was
entering at least part of the key for those records and when I saved
them they simply moved to their proper place on the grid, depending on
the sort order which depended on the key value I entered.

Good luck

Phil Cain

Re:Problems in CacheUpdates


Hi, Philip

I only place the code 'Database1.ApplyUpdates([Query1, Query2])' under the
"Save" button. I use ApplyUpdates method of TDatabase rather than that of
TDataset because the former one can handle the transaction automatically
(it is stated from the online document).

I will try to control the master/detail relationship by code instead of
setting the mastersource property of the detail dataset. Moreover, I think
I rely on the TDatabase.ApplyUpdates method too much that I don't try to
control the order of posting for master and detail datasets properly, if I
use ApplyUpdates by dataset like you, I think most of the problems can be
solved.

I rarely use TUpdateSQL component, in fact, I think that I should use it
only if the TQuery.RequestLive cannot be set to True. Is there any reason
to use it in some cases?

Thank you very much for your comments that are very helpful to me, it's
great to feel that I am not alone on the road of application development.

Regards,

Neon.

Philip Cain <philc...@orelle.com> wrote in article
<363dd8e0.2612...@forums.borland.com>...

Quote
> Neon,

> Let's see the code under your "Save" button. Without seeing the code,
> it's difficult, and often impossible, to be specific about the
> problem.

> In the meantime, here are some comments.

> I suspect that the problem is that you've made a master/detail
> relationship through the datasets. I don't do that and I'm happy with
> the results. There may be some others here who can tell you how to
> tune cached-updates when the datasets are linked, but I can't.

> Here's what I do:
> 1. I always ApplyUpdates when the master changes and at no other time.
> 2. Because the datasets are not linked, I must refresh the detail
> dataset in my code each time the master record changes.
> 3. I always ApplyUpdates by dataset (Query1.ApplyUpdates, for example)
> and never by database.
> 4. I always include ApplyUpdates inside a database transaction. (I
> will be able to comment on that when I see your code for "Save")

> Do you use TUpdateSQL components? (If you do not, then
> TQuery.RequestLive must be set to True.)
> Do you get any messages besides the key violation message?

> Because this is a master/detail relationship, you may have to use
> OnUpdateRecord because the order of updates becomes important in a
> master/detail. For example, when adding a new master, it's important
> to add the master before adding any detail. When deleting, it's
> important to delete the details first.

> A note about disappearing records. Once I thought records were
> disappearing on me. I added them at the bottom of a grid and, when I
> pressed the "Save" button, they seemed to go away. But it turns out
> they did not. Instead, they moved to another place on the grid. I was
> entering at least part of the key for those records and when I saved
> them they simply moved to their proper place on the grid, depending on
> the sort order which depended on the key value I entered.

> Good luck

> Phil Cain

Re:Problems in CacheUpdates


Neon,

Quote
>I only place the code 'Database1.ApplyUpdates([Query1, Query2])' under the
>"Save" button. I use ApplyUpdates method of TDatabase rather than that of
>TDataset because the former one can handle the transaction automatically
>(it is stated from the online document).

Transactions can be handled automatically by Tdataset.ApplyUpdates,
too. Automatic updates are not governed by the qualifier to
ApplyUpdates (TDatabase or TDataset), no matter what the documentation
seems to say. You can go either way.

Automatic updates are determined by your use of OnUpdateRecord and
OnUpdateError. If you use either one of those, then updates are not
automatic and you must manually post all your records. If you do not
use either one of those, then Delphi will post cached updates
automatically.

Your code under the save button is not enough, for two reasons. First,
you should enclose that code in a database transaction. Second, you
must flush the cache with CommitUpdates. The cache is not flushed
automatically. (That is, I would never assume it to be flushed
automatically.) Try something like this:

TDatabase.StartTransaction
try
        (TDatabase or TDataset).ApplyUpdates;
        TDatabase.Commit;
        TDatabase.CommitUpdates;  <=== flushes the cache
except
        TDatabase.RollBack;
end;

At this point, if you hit a rollback, your cache is still intact and
you can react to the error without having to enter all your changes
again.

Quote
>...
>I rarely use TUpdateSQL component, in fact, I think that I should use it
>only if the TQuery.RequestLive cannot be set to True. Is there any reason
>to use it in some cases?

You're right. You don't need TUpdateSQL if RequestLive is True. And
that may be preferable to using TUpdateSQL.

TUpdateSQL gives you complete control over the SQL used for
add/modify/delete actions. Also, as you've seen, it gives you a way to
handle queries when RequestLive cannot be True, as with joined
queries.

But there is a drawback. TUpdateSQL cannot give you control over the
UpdateMode. That is, if you want the update mode to UpWhereChanged,
you will be disappointed with TUpdateSQL, which tends to act like
UpWhereAll. This won't matter if you have a relatively small user
group and it is unlikely that any two users will try to update the
same record at the same time. But if users often try to update the
same record at the same time, you may have some concurrency problems.

Quote

>Thank you very much for your comments that are very helpful to me, it's
>great to feel that I am not alone on the road of application development.

You're welcome.

Phil

Re:Problems in CacheUpdates


Phil,

Thank you for your comments again. It's time for my to test the concept
practically after learning so much from you. I hope I have some valuable
experience to share with others in this issue such as that you did for me.

Regards,

Neon.

Other Threads