Cached Updates: deleting records 
Author Message
 Cached Updates: deleting records

I want to be able to delete records in a table(TAdminTypeTable) which has
CachedUpdates=true.
I do Delete and then ApplyUpdates which can cause an Exception
(Master-Detail relationship) so I want to recover from this.

I made this:

----------------------------------
procedure TformTreeEdit.DBDelete;
begin
Editset.Delete;

try
    EditSet.CSMApplyUpdates;
except
    EditSet.CSMCancelUpdates;
    raise;
end;

end;

where:
-----------------------
procedure TAdminTypeTable.CSMApplyUpdates;
begin
Database.StartTransaction;
try
    if UpdateStatus = usInserted then                 // not
        begin
        Self.ApplyUpdates;
        AddDefaultRights(FieldValues[fKeyField]);
        fcsmAdminRightTable.ApplyUpdates;
        end
    else
        begin
        fcsmAdminRightTable.ApplyUpdates;
        Self.ApplyUpdates                                   // raises
exception on Ref.Intrg !
        end;
    Database.Commit;
except
    Database.Rollback;                                    // database is
rolledback
    raise;
end;
Self.CommitUpdates;
fcsmAdminRightTable.CommitUpdates;
end;

and:
procedure TAdminTypeTable.CSMCancelUpdates;
begin
fcsmAdminRightTable.CancelUpdates;
inherited CancelUpdates;
end;

----------------------------------
Is this correct?
Do I really need the exception handling in DBDelete?
what if call CSMCancelUpdates right after Database.Rollback?

Thanx for your attention
CU
WS



Wed, 18 Jun 1902 08:00:00 GMT  
 Cached Updates: deleting records


Quote:
>I want to be able to delete records in a table(TAdminTypeTable) which has
>CachedUpdates=true.
>I do Delete and then ApplyUpdates which can cause an Exception
>(Master-Detail relationship) so I want to recover from this.

[...]

Quote:
>Is this correct?
>Do I really need the exception handling in DBDelete?
>what if call CSMCancelUpdates right after Database.Rollback?

What is the exception class (like EDatabaseError, EConvertError, etc.)?
What was the error message?

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Steve Koterski              "Health nuts are going to feel stupid someday,
Felton, CA                  lying in hospitals dying of nothing."
                                                              -- Redd Foxx



Wed, 18 Jun 1902 08:00:00 GMT  
 Cached Updates: deleting records

Quote:
>What is the exception class (like EDatabaseError, EConvertError, etc.)?
>What was the error message?

The error is :'Detail table exists'   which is perfectly correct, but i
wondered my solution to 'undelete' with an CancelUpdates(which restores the
record) was correct.
or

Where can I find good documentation/Demos on Cached Updates?
(I have read the Dev. Guide)

Thanx
CU
WS



Wed, 18 Jun 1902 08:00:00 GMT  
 Cached Updates: deleting records


Quote:
>The error is :'Detail table exists'   which is perfectly correct, but i
>wondered my solution to 'undelete' with an CancelUpdates(which restores the
>record) was correct.

It sounds like you have some form of referential integrity in place.
Attempts to delete a master row while matching detail rows still exist
causes an exception. This has nothing directly to do with cached updates,
but with how you delete rows when tables are linked with referential
integrity. You would get similar errors event without cached updates
enabled.

Try applying the updates for the child table first, then those for the
master table.

Quote:
>Where can I find good documentation/Demos on Cached Updates?
>(I have read the Dev. Guide)

As far as I know, the Developer's Guide is it. What version of Delphi do
you have? The cached updates chapter underwent considerable update
concurrent with Delphi 4, along with the addition of numerous source
examples.

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Steve Koterski              "Health nuts are going to feel stupid someday,
Felton, CA                  lying in hospitals dying of nothing."
                                                              -- Redd Foxx



Wed, 18 Jun 1902 08:00:00 GMT  
 Cached Updates: deleting records
Hello,
Quote:
>It sounds like you have some form of referential integrity in place.
>Attempts to delete a master row while matching detail rows still exist
>causes an exception.

Yes, I _know_ why it happens and I _want_ to see that error, but I want to
know how I can
_recover gracefully_ from this error and undelete the detail-records. It
seems to work
with an CancelUpdates but is that the correct way?

Quote:
>>Where can I find good documentation/Demos on Cached Updates?
>>(I have read the Dev. Guide)

>As far as I know, the Developer's Guide is it. What version of Delphi do
>you have?

I have D5 Professional. Which has got quite a lot of info on this,
like(slightly modified:):

procedure ApplyChangesAfterDelete;// only after delete!
begin
Database1.StartTransaction;
try
    Detail.ApplyUpdates; // first delete the details
    Master.ApplyUpdates;
    Database1.Commit;
except
    Database1.RollBack;
    Raise;
end;
Detail.CommitUpdates;
Master.CommitUpdates;
end;

The problem is: If the Masterrecord has Referential Integrity with another
table, and so can NOT be deleted, I want to recover from this so the
Detail-table must be restored in the cache. (the masterrecord is still in
the cache) Is it correct to do this with Cancelupdates?

Thanx
CU
WS



Wed, 18 Jun 1902 08:00:00 GMT  
 Cached Updates: deleting records


[...]

Quote:
>Yes, I _know_ why it happens and I _want_ to see that error, but
>I want to know how I can _recover gracefully_ from this error and
>undelete the detail-records. It seems to work with an
>CancelUpdates but is that the correct way?

Sorry. When all you know of another person is a single newsgroup post, the
risk of misjudging their technical acuity is high. This can result in
either speaking at too high a technical level and losing them or at too low
a level and insulting them. My stating the obvious was not a deliberate
insult, but a result of this misjudgement.

[...]

Quote:
>The problem is: If the Masterrecord has Referential Integrity with another
>table, and so can NOT be deleted, I want to recover from this so the
>Detail-table must be restored in the cache. (the masterrecord is still in
>the cache) Is it correct to do this with Cancelupdates?

Whether cached updates can accommodate your case is situational. Under some
conditions it can and under other conditions it might be inadequate.

The first thing you need to look at is the order in which the cached
changes are being applied. Your referential integrity demands the detail
table rows be deleted first. Otherwise you cannot delete the master table
rows cannot be deleted from the base table (i.e., the cached deletion
applied). This might require special handling in a handler for the TQuery
component's OnUpdateRecord event. For instance, you might use a TQuery to
delete all detail table rows from the base detail table using a single SQL
statement (not possible with a TUpdateSQL component). Then allow a
TUpdateSQL component to handle applying the deletion of the master table
row normally.

If all one cache set is going to be is one master table row and the
associated detail table rows, cached updates and/or a database transaction
should suffice. When you are talking about deleting rows, the CancelUpdates
method can work. But for any other update type, not only is the update
process aborted but all changes are lost. The same pplies if you use a
database transaction. Rolling back the database on occurrence of an
exception would result in the return of deleted rows, but the loss of any
other update types.

If you apply the master table's updates first, the CancelUpdates method is
the way to undo the deletions. This is because you cannot apply the cached
deletion to the base master table due to the error and the cached deletions
of the associated detail rows have not yet been applied. But if you apply
the detail table cached changes first, the only way to recover them would
be with a database transaction.

However, if more than one master row is involved, using cached updates
would negate valid deletions along with the one you wanted to stop (the one
triggering the error).

So I would probably recommend a complete cycle of editing data, applying
the cached updates, and refreshing the dataset once for each master row
operated on.

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Steve Koterski              "Health nuts are going to feel stupid someday,
Felton, CA                  lying in hospitals dying of nothing."
                                                              -- Redd Foxx



Wed, 18 Jun 1902 08:00:00 GMT  
 Cached Updates: deleting records
Quote:
>Sorry.. My stating the obvious was not a deliberate
>insult, but a result of this misjudgement.

I was not insulted, and really appreciate your help. I just wanted to make
the problem clear.

Quote:
>>The problem is: If the Masterrecord has Referential Integrity with another
>>table, and so can NOT be deleted, I want to recover from this

>Whether cached updates can accommodate your case is situational. Under some
>conditions it can and under other conditions it might be inadequate.

>Your referential integrity demands the detail
>table rows be deleted first.

OK, we do that

Quote:
>This might require special handling in a handler for the TQuery
>component's OnUpdateRecord event.

We only update on TTables.

Quote:
>If you apply the master table's updates first, the CancelUpdates method is
>the way to undo the deletions. This is because you cannot apply the cached
>deletion to the base master table due to the error and the cached deletions
>of the associated detail rows have not yet been applied. But if you apply
>the detail table cached changes first, the only way to recover them would
>be with a database transaction.

We use both Transactions(Commit/RollBack) and ApplyCancelUpdates(as the
Dev.Guide shows).
The Rollback UNdeletes the detail records in the physical table.

Quote:
>However, if more than one master row is involved, using cached updates
>would negate valid deletions along with the one you wanted to stop (the one
>triggering the error).

No, we only delete ONE master row. We don't want to get into trouble:)

Quote:
>So I would probably recommend a complete cycle of editing data, applying
>the cached updates, and refreshing the dataset once for each master row
>operated on.

Yes, we do that. And we think of handling ApplyUpdateErrors by returning to
the state before ANY editing started.
We will look at how exactly we will do that.
Maybe I will have to ask some more questions then.
Thanx4now
CU
WS


Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Cached Updates QUESTION: Sorting table of updated and non-updated records

2. Deleting from Master/Detail using cached updates

3. Cached Updates TQuery Problem with Modify/Delete (within transaction)

4. Record Lock in cached update when using updateobject

5. cached updates + locked records

6. Using UpdateSQL for updating cached records

7. Cached Updates - OldValue property upon record insert is not NULL

8. Cached Updates Problem: Missing Records - Bogus RecordCount

9. TTable Cached update record lock

10. Which record caused an error using cached updates?

11. Source: SQL on the fly for Updating, Inserting, and Deleting records

12. Updating/deleting records with TQuery

 

 
Powered by phpBB® Forum Software