Board index » delphi » BDE can't handle transaction correctly with cache updates

BDE can't handle transaction correctly with cache updates

Hello,
I have a big problem with BDE and transactions.
If you start a transaction, call the method ApplyUpdates of a TQuery
component and you roll back, it's impossible, after, to re-apply to the
database the changes that are still in cache. The component is in a strange
state, it has all information in cache (if you test modified record you'll
find Field.NewValue <> Field.OldValue) but the property UpdatesPending is
false and, in this situation, ApplyUpdates does nothing.
This is a problem because it's impossible to assure the correct behaviour of
an application after a roll back.
Anyone can help me?

Piergiorgio.

 

Re:BDE can't handle transaction correctly with cache updates


I think there is nothing there but logical. After rollback the cache content
has to be made unavailable because you canceled all operations that were
done within the transaction. Do you want to cancel the canceling ?

--
Christophe Dary
cd...@usa.net
http://www.geocities.com/SiliconValley/Hills/1623/

Re:BDE can't handle transaction correctly with cache updates


Perhaps you can call TBDEDataSet.CancelUpdates for each dataset after
performing the rollback.

V/R
Russell L. Smith

Quote
Piergiorgio wrote in message <73gosj$a...@forums.borland.com>...
>I have a big problem with BDE and transactions.
>If you start a transaction, call the method ApplyUpdates of a TQuery
>component and you roll back, it's impossible, after, to re-apply to the
>database the changes that are still in cache. The component is in a strange
>state, it has all information in cache (if you test modified record you'll
>find Field.NewValue <> Field.OldValue) but the property UpdatesPending is
>false and, in this situation, ApplyUpdates does nothing.

Re:BDE can't handle transaction correctly with cache updates


Yes, but I loose the changes. If you have a lot of datasets and, on update,
only one causes a DB error I don't want to clear all modification to all
datasets but simply I want to adjust the wrong dataset.
I know that I should check all datasets before apply updates but sometimes
isn't possible to foresee all cases, I always know that I can handle the
error on OnUpdateError but if to correct the error I need to wait some user
actions?
Delphi help for TBDEDataSet.ApplyUpdates says:
"... an application should call the CommitUpdates method to clear the cached
update buffer ..."
so after the execution of this method the update buffer isn't empty and the
records are still in cache.
Help for TBDEDataSet.UpdatesPending says:
"... If UpdatesPending is False, there are no records in the cache ..."
Why after TBDEDataSet.ApplyUpdates and before TBDEDataSet.CommitUpdates
UpdatesPending is false?
There are differences between D4 and D3, try this on a modified dataset:

Database.StartTransaction;
DataSet.ApplyUpdates;
Database.Rollback;
Database.StartTransaction;
DataSet.ApplyUpdates;
Database.Commit;

With D3 You'll find the modify on the database, with D4 not, why?

What I'm wrong?

Piergiorgio.

PS: sorry, my english isn't good, I hope you have no problem to understand
me.

Russell L. Smith wrote in messagge <73l92a$e...@forums.borland.com>...

Quote
>Perhaps you can call TBDEDataSet.CancelUpdates for each dataset after
>performing the rollback.

>V/R
>Russell L. Smith

>Piergiorgio wrote in message <73gosj$a...@forums.borland.com>...
>>I have a big problem with BDE and transactions.
>>If you start a transaction, call the method ApplyUpdates of a TQuery
>>component and you roll back, it's impossible, after, to re-apply to the
>>database the changes that are still in cache. The component is in a
strange
>>state, it has all information in cache (if you test modified record you'll
>>find Field.NewValue <> Field.OldValue) but the property UpdatesPending is
>>false and, in this situation, ApplyUpdates does nothing.

Re:BDE can't handle transaction correctly with cache updates


This might be more complicated than you need, but here's what we do in
OnUpdateError:

* Based on the exception (and all the TDBError's NativeErrors) determine
which message or messages are appropriate.

* Save these messages, a reference to the dataset row, and offending field
(if available) in a data structure.  Messages are displayed to the user only
after all updates have been applied to prevent a transaction from{*word*154}
open.

* Copy the row to a local temporary table, which is dynamically created when
needed and destroyed when processing is complete.

* After all updates have been applied, call CommitUpdates and commit the
database transaction.

* Close and re-open all datasets to show the results of server-side triggers
and to make sure the user is working with the most current data.

* Copy the data from the temporary tables over top of the refreshed
datasets.

* Display the error messages for the user.

* The user can make any corrections and save again.

Most of this is generic and occurs in a foundation layer shared by multiple
applications.

V/R
Russell L. Smith

Quote
Piergiorgio wrote in message <73lv3p$ev...@forums.borland.com>...
>Yes, but I loose the changes. If you have a lot of datasets and, on update,
>only one causes a DB error I don't want to clear all modification to all
>datasets but simply I want to adjust the wrong dataset.

Re:BDE can't handle transaction correctly with cache updates


Very nice and sharpened error handling !

My apologies to Piergiorgio for misunderstanding his question at first time.

--
Christophe Dary
cd...@usa.net
http://www.geocities.com/SiliconValley/Hills/1623/

Re:BDE can't handle transaction correctly with cache updates


I think this is a serious BDE bug, and you?

Piergiorgio.

Re:BDE can't handle transaction correctly with cache updates


Hi!

I work with Delphi 2.0 and as I rember the behaviour is the same.
Between ApllyUpdates an d commitupdates updatespending returns false.
After !!! calling commtiupdates I have sometimes the problem that
updatespending is true ! (Even if no error has occured and the updates
were written to the database ....). Happy Errorhunting.

Quote
Piergiorgio wrote:
> I think this is a serious BDE bug, and you?

> Piergiorgio.

Re:BDE can't handle transaction correctly with cache updates


Russell,

In your excellent explanation of OnUpdateError, you said:

Quote
>* Based on the exception (and all the TDBError's NativeErrors) determine
>which message or messages are appropriate.

>...
>* After all updates have been applied, call CommitUpdates and commit the
>database transaction.

What happens if the error is not appropriate to the update or not
considered in the handler or something with fatal qualities (e.g. lost
connection)?

What do you do if you start with:

Dataset.DataBase.StartTransaction;
try
        Dataset.ApplyUpdates;
        Dataset.CommitUpdates;
        Dataset.Database.Commit;
except
        Dataset.Database.RollBack;
end;

This try...except block doesn't distinguish between errors caught in
OnUpdateError and other kinds. One error in OnUpdateError will trigger
the except and everything gets rolled back, willy-nilly.

If you leave the RollBack out, the transaction doesn't get closed.

So, when you hit the except block, you want to tell if you've already
handled it in OnUpdateError. Do we have to go through all the error
qualifications again?

I don't think it's a good idea to Commit blindly here (to leave the
RollBack out). The error could have come from Commit, not
ApplyUpdates. And it could have come from some error not handled in
OnUpdateError.

The best situation would be to have this except block ignore errors
handled in OnUpdateError, but I can't figure out how. I've tried
uaAbort and SysUtils.Abort, but to no avail.

And, of course, it's not always useful or necessary to go through the
recycling that you suggest in the full-blown error handling method you
describe.

How do you handle this?

Phil Cain

Re:BDE can't handle transaction correctly with cache updates


Quote
Philip Cain wrote in message <3665e504.7924...@forums.borland.com>...
>What happens if the error is not appropriate to the update or not
>considered in the handler or something with fatal qualities (e.g. lost
>connection)?

Good question.  Only EDatabaseErrors are sent to OnUpdateError.  Almost 100%
of the time, the exception will be an EDBEngineError (a subclass).  If the
exception is not anticipated and therefore not handled, our last-ditch
default handler will display the message in the TDBError to the user,
alongside the handled error messages.  This messages are not displayed until
all updates are applied.

Our testers (or users) will then submit a trouble report because they like
plain-text error messages, not messages like "ORA-24324:  service handle not
initialized".

Quote
>Dataset.DataBase.StartTransaction;
>try
> Dataset.ApplyUpdates;
> Dataset.CommitUpdates;
> Dataset.Database.Commit;
>except
> Dataset.Database.RollBack;
>end;

>This try...except block doesn't distinguish between errors caught in
>OnUpdateError and other kinds. One error in OnUpdateError will trigger
>the except and everything gets rolled back, willy-nilly.

The rollback in your exception handler will never happen unless the update
is aborted, which will only happen either if you don't have an OnUpdateError
event handler, or you set UpdateAction to uaAbort.  Look at the exception
handler in TBDEDataSet.CachedUpdateCallBack to see how this is handled in
the VCL.  Database errors should never make it to this outer exception
handler unless a huge disaster occurs.

Using the model I described, if ten rows are modified, and two result in
validation errors or database engine errors, the eight rows will be updated
and committed, while the two defective rows will be skipped.  The
transaction will be committed because the update is never aborted.

Quote
>If you leave the RollBack out, the transaction doesn't get closed.

The transaction is always terminated, usually by a commit, so that
successfully updated rows are saved (even if update errors occur on other
rows).

Quote
>So, when you hit the except block, you want to tell if you've already
>handled it in OnUpdateError. Do we have to go through all the error
>qualifications again?

The except block will usually never be called because the errors are handled
within OnUpdateError.

Quote
>I don't think it's a good idea to Commit blindly here (to leave the
>RollBack out). The error could have come from Commit, not
>ApplyUpdates.

Yes, the error could have come from the commit.  That is an example of a
huge disaster to which I previously referred.

Quote
>And it could have come from some error not handled in OnUpdateError.

It would not be a result of an unhandled error in OnUpdateError because our
last-ditch handler saves the error messages and uaSkips the update for that
row.

Quote
>The best situation would be to have this except block ignore errors
>handled in OnUpdateError, but I can't figure out how. I've tried
>uaAbort and SysUtils.Abort, but to no avail.

Very infrequently we do want to abort the entire update when an exception is
handled within OnUpdateError.  That works OK because the rollback will
occur.  I don't think you would want to ignore this type of error.  If you
wanted to, you could use something like:

    except
      on E:Exception do
        if not (E is EAbort) then
        begin
            // rollback, etc.
        end;
    end;

Quote
>And, of course, it's not always useful or necessary to go through the
>recycling that you suggest in the full-blown error handling method you
>describe.

Agree.  Sometimes it is a real pain.  Thankfully it is 99% in a corporate
layer.

V/R
Russell L. Smith

Re:BDE can't handle transaction correctly with cache updates


Russell,

Thanks for that thoughtful response. But I'm still confused. You said:

Quote
>The except block will usually never be called because the errors are handled
>within OnUpdateError.

My problem is that I can't get the error "handled" in OnUpdateError.
To review, I start the update process like this:

Dataset.DataBase.StartTransaction;
try
        Dataset.ApplyUpdates;
        Dataset.CommitUpdates;
        Dataset.Database.Commit;
except
        Dataset.Database.RollBack;
end;

Then, in OnUpdateError, I have something like this:

   if (E is EDBEngineError) then
       begin
       ErrCode := (E as EDBEngineError).Errors[0].ErrorCode;
       if ErrCode = DBIERR_FORIEGNKEYERR then
           begin
           Msg := 'You cannot delete this order'
                    + ' because it still has open items.'
                + ' This order will not be deleted.';
           MessageDlg(Msg, mtInformation,[mbOk], 0);
           UpdateAction := uaAbort;
  //         SysUtils.Abort;
           end;
       end;

Specifically, my problem here is that this error still fires the
rollback, even though I don't want it to.

More generally, there are these possiblities that I don't understand
how to control:
1. the error applies to one record and the response should be to
revert that record (get it out of the cache).
2. The error applies to one record and the response should be to not
update but to leave it in the cache.
3. The error, although appearing on one record, affects the entire
cache and whole update should be aborted but leaving the cache intact.
4. Same as #3 but aborting the cache as well as the transaction.

My case above needs the solution #1. I had thought that uaAbort here
would apply the abort to that one record only and effectively be a
RevertRecord. What I expected was that uaAbort would revert the
record, cancel the error ("handle" the error) and allow ApplyUpdates
to go to the next record.  Apparently not so, but I don't know what to
do about it.

Instead, uaAbort seems to have a cache-wide (or transaction-wide)
effect.

So my first question is: how do I handle a record-level message
without it affecting the transaction?

Then:
Why does uaAbort seem to have such wide reaching effects when other
UpdateActions deal with only the cache record at hand?

And,
How do I handle the other possibilities on the list?

I realize that at least some of my problem is caused by my lack of
mastery of errors generally and I may be taxing your patience here.
But you've been steadfast so far and if you can wade through all of
this, then you get the gold star for the week.

Phil

Re:BDE can't handle transaction correctly with cache updates


Quote
Philip Cain wrote in message <3667f6b1.1310...@forums.borland.com>...
>Instead, uaAbort seems to have a cache-wide (or transaction-wide)
>effect.

You are correct.  Setting UpdateAction to uaAbort bails out of the entire
transaction.  Updates for subsequent rows are not applied because the
uaAbort will result in a subsequent EAbort exception which will cause your
Rollback to occur.  This is documented in the help.

I think you want to uaSkip rather than uaAbort.  The help defines uaSkip as:

    Skips updating the record that raised the error condition, and leaves
    the unapplied changes in the cache.

Quote
>1. the error applies to one record and the response should be to
>revert that record (get it out of the cache).

This might be a {*word*156}e.  I have never tried this because we always
Close/Open after updates are applied, which effectively does what you want
for uaSkip'd records.  Since you don't want to Close/Open, maybe a
RevertRecord within OnUpdateError will work.  If you test this, be sure it
reverts the correct record using a test case with a multi-record update.
Too bad there isn't a uaRevert...

Quote
>2. The error applies to one record and the response should be to not
>update but to leave it in the cache.

Normal behavior of uaSkip.  If you decide to Close/Open you need to make
additional provisions.

Quote
>3. The error, although appearing on one record, affects the entire
>cache and whole update should be aborted but leaving the cache intact.

Normal behavior of uaAbort, Rollback in outer exception handler.

Quote
>4. Same as #3 but aborting the cache as well as the transaction.

uaAbort, Rollback in outer exception handler, and CancelUpdates.

BTW, your code sample has a message being displayed in the OnUpdateRecord
event handler.  Be aware that a user might go to lunch while the error
message is displayed and leave a transaction open (bad bad bad).  This might
also cause implicit locks that prevent other users from working.  Be careful
here...

Good luck.  When in doubt, trace through VCL code and consult the BDE API
Reference (bde32.hlp).

V/R
Russell L. Smith

Re:BDE can't handle transaction correctly with cache updates


"Russell L. Smith" <Russell_L._Sm...@mail.amsinc.com> wrote:

Quote
>Too bad there isn't a uaRevert...

How true. I think perhaps the Delphi folks didn't think this all the
way through..........

I can see now that the whole process will be some kind of cooperation
between the OnUpdateError event and the try...ApplyUpdates....except
block. I'll think of something.

Thanks for your help and the tip about the error message.

You get the gold star for this week<g>.

Phil

Other Threads