Board index » delphi » Cached updates and UpdateSQL

Cached updates and UpdateSQL

Dear All,

I'm using cached updates to write to a TQuery dataset which has been
produced by a join of two tables. I make the following call after each ten
updates are written to the cache :

TDatabase.ApplyUpdates( [MyQuery] );

The query's OnUpdateRecord handler does the following, invoking the
UpdateSql script of two TUpdateSql objects to update each table making up
the joined dataset :

procedure TForm1.Query1UpdateRecord(DataSet: TDataSet;
UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction);
begin
    UpdateSQL1.Dataset := (DataSet as TBDeDataset);
    UpdateSQL1.Apply(UpdateKind);
    UpdateSQL2.Dataset := (DataSet as TBDEDataset);
    UpdateSQL2.Apply(UpdateKind);
    UpdateAction := uaApplied;
end;

I also have an OnUpdateError handler connected to the query being updated.

The problem is that the BDE appears not to detect rows that have changed on
the Oracle table since they were written to the local cache, and the cached
copies end up overwriting the later edits residing on the table. The delphi
help says explicitly that the BDE should detect subsequently changed rows on
the table. Also, if I simulate a row lock on one of the rows in the cache,
my application waits indefinitely until the lock is released before applying
any of the updates in the cache. In both cases I would expect the
TQuery.OnUpdateError event to fire up my error - handling code, but this
does not happen.

The database is Oracle 8 and the BDE is the latest version.

Does anyone have any idea why this happens and any solutions?

Cheers

Aaron

 

Re:Cached updates and UpdateSQL


Quote
"Aaron Aardvark" <aa...@castle-cadenza.demon.co.uk> wrote:
>The problem is that the BDE appears not to detect rows that have changed on
>the Oracle table since they were written to the local cache, and the cached
>copies end up overwriting the later edits residing on the table. The delphi
>help says explicitly that the BDE should detect subsequently changed rows on
>the table.

Aaron,

Your otherwise very clear explanation gets vague at this point. Can
you be clearer about what you mean when you say that the "BDE appears
not to detect rows?"

At this point, I would suggest that you move this:

Quote
>    UpdateSQL1.Dataset := (DataSet as TBDeDataset);

out of the OnUpdateRecord event.

I don't know if this is part of the update problem you have, but it's
not very efficient code. TUpdateSQL does a lot of work when you assign
its dataset property. In your case, it's the same work for every
record you want to post, so you should do it once, before you
StartTransaction and ApplyUpdates.

Outside of the OnUpdateRecord event, you can simply use the name of
the dataset, like this:

Quote
>    UpdateSQL1.Dataset := MyQuery;

Your approach seems to indicate that you are trying for a generic
OnUpdateRecord handler for every cashed updates process. I don't
recommend that.

Further, this approach doesn't take into consideration such things as
foreign key development in your joined query. For example, if you add
a record to your dataset, you will want to add the master side of the
join before adding the detail side. Likewise, on a delete, you will
want to delete the detail side first. You do this by testing the
UpdateKind argument.

Phil Cain

--

Re:Cached updates and UpdateSQL


Quote
> Your otherwise very clear explanation gets vague at this point. Can
> you be clearer about what you mean when you say that the "BDE appears
> not to detect rows?"

By this I mean the locked rows or rows that have been modified while those
in the cache were being worked on. What I'm looking for is some means of
detecting whether the rows have been changed while I've been editing them or
if they are currently locked. It should be done as a matter of course, but
for some reason, the BDE doesn't come back with any errors.

Aaron

Re:Cached updates and UpdateSQL


Quote
"Aaron Aardvark" <aa...@castle-cadenza.demon.co.uk> wrote:
>> Your otherwise very clear explanation gets vague at this point. Can
>> you be clearer about what you mean when you say that the "BDE appears
>> not to detect rows?"

>By this I mean the locked rows or rows that have been modified while those
>in the cache were being worked on. What I'm looking for is some means of
>detecting whether the rows have been changed while I've been editing them or
>if they are currently locked. It should be done as a matter of course, but
>for some reason, the BDE doesn't come back with any errors.

Aaron,

It's not supposed to. Your dataset is really a snapshot of the
database at the time you did the query. Any changes after that point
aren't known to you until you try to update.

When you do the query, the BDE has no way of knowing whether you will
update or not. If you don't update, who cares that there have been
changes in the meantime? It's only when you update that the database
and the BDE can check to see if you might be updating something that
may have changed.

You can use the dataset's updatemode to set a sensitivity level for
this kind of checking, but essentially we are stuck with waiting until
we try to update to find out if we're stepping on any toes. Then the
BDE gives us error conditions that we must respond to or suffer the
consequences. Our job is to catch those messages and do the right
things with them.

Phil Cain

--

Other Threads