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


On Wed, 23 Jun 1999 10:10:39 +0100, "Aaron Aardvark"

Quote
<aa...@castle-cadenza.demon.co.uk> wrote:
>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 :

<snip>

Quote
>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.

You should try using an optimistic locking schema.  That means that
the update SQL statements should check that the record they are
updating is still the same previously read.  This id done with the
WHERE clause of the UPDATE (and DELETE) SQL statement.

This is easily done by having the IDE generate the update SQL using ad
UpdateMode of upWhereAll, or, if you feel lucky, upWhereChanged.

If you set things this way, then, when the record has been changed,
the WHERE does not match any record.  An error is raised as no records
get updated (or deleted).  You can handle this in the OnUpdateError
and provide feedback to the user.

Regards,

--
Marco Rocci
MicroEra srl
Turin, Italy
-----------------
vota contro lo SPAM su: http://www.politik-digital.de/spam/

Other Threads