Board index » delphi » POSSIBLE BUG in cached update

POSSIBLE BUG in cached update

I'm having a problem with cached updates and TQuery components.  Basically,
what I am trying to do is very simple.  I've got a table, INCIDENTS, with
two fields in it: INCIDENT_ID (a unique integer), and INCIDENT_NUMBER (a
character field, like INO-0001).  I've got cached updates working using
a TUpdateSQL component and the onUpdateRecord inside of a transaction. The
idea is that I will be adding related queries, or increasing the complexity
of the one I have (shouldn't matter, right? ;v}).  It works great (with
interbase, but not with MS SQL Server) up until I try to handle recovering
from an error.

Imagine a user (me) enters in an INCIDENT_ID, but forgets the INCIDENT_NUMBER.
He hits the button, which applies the cached updates. When the ExecSQL
procedure gets run in Query1UpdateRecord (the onUpdateRecord event handler),
the server generates an error, as well it should.  An exception gets raised,
and the transaction gets rolled back.  Fine so far.  The user adds an
INCIDENT_NUMBER to the record, hits the button again. and the transaction
goes through.  It seems like it is working just fine.  

Now, the user does the same thing again.  He enter a new INCIDENT_ID
without the INCIDENT_NUMBER, the transaction fails, and he then edits the
offending record, and hits the button again.  This time, he gets a duplicate
key error in INCIDENT_ID. Huh!??!

I've traced it down, by looking at the SQL statements that are coming to
the server, and by using the de{*word*81} to see exactly what values are going
to the Query1UpdateRecord (OnUpdateRecord) procedure.  It appears that
however a record is updated in the cache, an extra update is getting
generated, with some merging of previous cached updates and the current
one.  In particular, the OldValue and the Value of the two fields seem to be
the same, but the NewValue (which is, apparently, used by the UpdateSQL
to do its thing), is set to values from previous cached updates.  If I
modify the values with the de{*word*81} (that is, change the INCIDENT_ID to
one that is not currently in the Table), I can prevent this error from
aborting the transaction.  I will eventually get to the "correct"
transaction, which then works just fine.

It seems to be that the first Transaction error causes one erroneous
record for the next transaction error, which in turn has two, and
then three, etc.  Either that, or it is that same number of cached
updates, error or no error.  If that doesn't make sense, don't worry
about it.  Sufice it to say, it just keeps getting worse.

I think I can get around the problem by looking at the Value property
of a field and determine if it is the same as the NewValue property.
If not, simply mark the record as updated, without actually doing so,
and move on.  But that means that if there are a lot of updated records,
there will be a lot of superfluous records floating around in the cache.

( Actually,I don't understand why you need a Value and a NewValue.  
If its a new record, there is no old value, and Value is just fine.
If it is a Modified record, you can see that from the difference between
OldValue and Value.  If it is a delete, just blow away whatever
was Value. Who needs NewValue?)

In any case, this looks like a bug in cached updates.  I have provided
the following:

        Step-By-Step Trace, using the de{*word*81}
        Description of the table, INCIDENTS
        Source Code for the app.  (its only one form)

Forgive me for the long-windedness of this message, but I wanted to
be clear on exactly what the problem is.  Someone please tell me if
this is truely a bug, as I suspect, or if I am doing something which
is not allowed.  If it is a bug, any ideas on when will it be fixed,
and will my suggested work-around actually work.  I'm posting this
to the Borland Forum on Compuserve as well.  (Why the hell can't
Borland get an technical support email address, fer crying out
loud?)

-Mike Conner
Milwaukee Software

======================================================================  
Step-by step Trace of the problem:  The values were determined by
evaluating Query1 when I entered the procedure
TForm1.Query1UpdateRecord.  

First Attempt: Insert INCIDENT_ID 1008, without INCIDENT_NUMBER:

        UpdateKind is correct (ukInsert)

                OldValue        Value           NewValue

        ID      Null            1008            1008
        NUMBER  NULL            NULL            NULL

        After NULL Violation of INCIDENT_NUMBER:

Re-"Edit" the record to add INCIDENT_NUMBER 'INO-1008':

        UpdateKind is correct (ukInsert)

                OldValue        Value           NewValue

        ID      Null            1008            1008
        NUMBER  NULL            'INO-10008'     'INO-1008'

        Transaction successful!!!!!

Second Attempt, insert INCIDENT_ID 1009, w/o INCIDENT_NUMBER:

        UpdateKind is correct (ukInsert)

                OldValue        Value           NewValue

        ID      Null            1008            1008
        NUMBER  NULL            NULL            NULL

        After NULL Violation of INCIDENT_NUMBER:

Re-"Edit" the record to add INCIDENT_NUMBER 'INO-1009':

        UpdateKind is correct (ukInsert)

                OldValue        Value           NewValue

        ID      Null            1009            1008            <<< ERROR
        NUMBER  NULL            'INO-1009'      'INO-1008'      <<< ERROR

        This is the "wrong" record.  For Some Reason, it appears
        That a previously applied cached update is being "confused"
        with the current one.  

        At this point I used the de{*word*81} to modify the NewValue of
        the INCIDENT_ID to allow updates to continue:

                Query1.Fields[0].NewValue := 1010

        UNINTENDED, but ALTERED Transaction Successful !

        UpdateKind is correct (ukInsert)

                OldValue        Value           NewValue

        ID      Null            1009            1009            <<< Correct!
        NUMBER  NULL            'INO-1009'      'INO-1009'

        INTENDED Transaction Successful !!!!!

        By avoiding an error using the de{*word*81}, I allowed updates to
        continue, so it could insert the "correct" update could occur.

Third Attempt: same Run, insert INCIDENT_ID 1111 w/o INCIDENT_NUMBER:

        UpdateKind is correct (ukInsert)

                OldValue        Value           NewValue
        ID      Null            1111            1111
        NUMBER  NULL            NULL            NULL

        After NULL Violation of INCIDENT_NUMBER:

Re-"Edit" the record to add INCIDENT_NUMBER 'INO-1008':

        UpdateKind is correct (ukInsert)

                OldValue        Value           NewValue

        ID      Null            1111            1010            <<< ERROR Again
        NUMBER  NULL            'INO-1111'      'INO-1008'      <<< ERROR Odd!

        Again, this is the "wrong" record.  Note, however, that the
        INCIDENT_ID comes from one previously added record, and the
        INCIDENT_NUMBER comes from another.  Odd, and I cannot begin
        to speculate on why that is.

        At this point I used the de{*word*81} to modify the NewValue of
        the INCIDENT_ID to allow updates to continue:

                Query1.Fields[0].NewValue := 1112

        UNINTENDED, but ALTERED Transaction Successful !

        UpdateKind is correct (ukInsert)

                OldValue        Value           NewValue

        ID      Null            1111            1009            <<< ERROR Again
        NUMBER  NULL            'INO-1111'      'INO-1009'      <<< ERROR Odd!

        Yet again, this is the "wrong" record.  I expected this one to
        be correct.  At this point I used the de{*word*81} to modify the
        NewValue of the INCIDENT_ID to allow updates to continue, just to
        see if there are any more:

                Query1.Fields[0].NewValue := 1113

        UNINTENDED, but ALTERED Second Transaction Successful !

        UpdateKind is correct (ukInsert)

                OldValue        Value           NewValue
        ID      Null            1111            1111            <<< Correct!
        NUMBER  NULL            'INO-1111'      'INO-1111'

        INTENDED Transaction Successful !!!!!

        It appears that several records are getting goofed up in the cache.
        The more opperations you perform, the more gets messed up.

        It seems as if the CommitUpdates function isn't clearing the cache.

======================================================================  

Here is the format of the table, INCIDENTS.  I have used both Interbase
and MS SQL Server 6.0, with similar results. (Although I'm getting lots
of errors with the SQL Links Drivers, but that is another problem.)

The Table, INCIDENTS, looks like:

        INCIDENT_ID     long            Unique, non-null
        INCIDENT_NUMBER char(15)        non-null

======================================================================  

What follows is the code for the Application, including the form, as a
text file:

{ ================= First, the project file: ===================== }

program Project1;

uses
  Forms,
  junk in 'junk.pas' {Form1};

{$R *.RES}

begin
  Application.Initialize;
  Application.CreateForm(TForm1, Form1);
  Application.Run;
end.

{ === junk.pas: only unit for the app ======================= }

unit junk;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, ExtCtrls, DBCtrls, Mask, DBTables, DB, Grids, DBGrids;

type
  TForm1 = class(TForm)
    Query1: TQuery;
    DataSource1: TDataSource;
    UpdateSQL1: TUpdateSQL;
    Query1INCIDENT_ID: TIntegerField;
    Query1INCIDENT_NUMBER: TStringField;
    Label1: TLabel;
    DBEdit1: TDBEdit;
    Label2: TLabel;
    DBEdit2: TDBEdit;
    DBNavigator1: TDBNavigator;
    Button1: TButton;
    DBGrid1: TDBGrid;
    procedure Button1Click(Sender: TObject);
    procedure Query1UpdateRecord(DataSet: TDataSet;
      UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction);
    procedure Query1BeforeClose(DataSet: TDataSet);
    procedure Query1BeforePost(DataSet: TDataSet);
    procedure Query1BeforeInsert(DataSet: TDataSet);
    procedure Query1BeforeEdit(DataSet: TDataSet);
    procedure DataSource1StateChange(Sender: TObject);
    procedure Query1AfterInsert(DataSet: TDataSet);
    procedure DBEdit1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

procedure DoNothing;
var
  x : integer;
begin
  x := 100;
end;

procedure TForm1.Button1Click(Sender: TObject);
var
  dummy : boolean;
  updateQuery : TQuery;
  dummyStr : string;
  dummyInt : integer;
  bm : TBookMark;
begin
  bm := Query1.GetBookMark;
  dummy := Query1.Database.InTransaction;
  //if Query1.State = dsInsert then Query1.Post;
  Query1.DataBase.StartTransaction;

  try
    Query1.ApplyUpdates;
    Query1.DataBase.commit;
  except
    Query1.DataBase.rollback;
    Query1.GotoBookMark(bm);
    Query1.FreeBookMark(bm);
    raise;
  end;
  Query1.CommitUpdates;
...

read more »

 

Re:POSSIBLE BUG in cached update


Quote
Mike Conner (mcon...@earth.execpc.com) wrote:

: I'm having a problem with cached updates and TQuery components.  Basically,
: what I am trying to do is very simple.  I've got a table, INCIDENTS, with
: two fields in it: INCIDENT_ID (a unique integer), and INCIDENT_NUMBER (a
: character field, like INO-0001).  I've got cached updates working using
: a TUpdateSQL component and the onUpdateRecord inside of a transaction. The
: idea is that I will be adding related queries, or increasing the complexity
: of the one I have (shouldn't matter, right? ;v}).  It works great (with
: interbase, but not with MS SQL Server) up until I try to handle recovering
: from an error.

: Imagine a user (me) enters in an INCIDENT_ID, but forgets the INCIDENT_NUMBER.
: He hits the button, which applies the cached updates. When the ExecSQL
: procedure gets run in Query1UpdateRecord (the onUpdateRecord event handler),
: the server generates an error, as well it should.  An exception gets raised,
: and the transaction gets rolled back.  Fine so far.  The user adds an
: INCIDENT_NUMBER to the record, hits the button again. and the transaction
: goes through.  It seems like it is working just fine.  

: Now, the user does the same thing again.  He enter a new INCIDENT_ID
: without the INCIDENT_NUMBER, the transaction fails, and he then edits the
: offending record, and hits the button again.  This time, he gets a duplicate
: key error in INCIDENT_ID. Huh!??!

: I've traced it down, by looking at the SQL statements that are coming to
: the server, and by using the de{*word*81} to see exactly what values are going
: to the Query1UpdateRecord (OnUpdateRecord) procedure.  It appears that
: however a record is updated in the cache, an extra update is getting
: generated, with some merging of previous cached updates and the current
: one.  In particular, the OldValue and the Value of the two fields seem to be
: the same, but the NewValue (which is, apparently, used by the UpdateSQL
: to do its thing), is set to values from previous cached updates.  If I
: modify the values with the de{*word*81} (that is, change the INCIDENT_ID to
: one that is not currently in the Table), I can prevent this error from
: aborting the transaction.  I will eventually get to the "correct"
: transaction, which then works just fine.

: It seems to be that the first Transaction error causes one erroneous
: record for the next transaction error, which in turn has two, and
: then three, etc.  Either that, or it is that same number of cached
: updates, error or no error.  If that doesn't make sense, don't worry
: about it.  Sufice it to say, it just keeps getting worse.

: I think I can get around the problem by looking at the Value property
: of a field and determine if it is the same as the NewValue property.
: If not, simply mark the record as updated, without actually doing so,
: and move on.  But that means that if there are a lot of updated records,
: there will be a lot of superfluous records floating around in the cache.

: ( Actually,I don't understand why you need a Value and a NewValue.  
: If its a new record, there is no old value, and Value is just fine.
: If it is a Modified record, you can see that from the difference between
: OldValue and Value.  If it is a delete, just blow away whatever
: was Value. Who needs NewValue?)

To anybody who cares:

I've talked to Borland on this.  After spending an hour and a half on
the line teaching their technical support "expert" how cached updates
are supposed to work, I gave up, told him to work on my example.  At
this point, I guess he asked someone who actually knew how to use
the stuff.

Apparently, when an error occurs in a transaction, unless you can fix
it in the OnRecordError, the cache will be messed up.  Apparently,
Borland does not consider this a bug.  Apparently, Borland is full of
it.  I was told that the only thing you can really do at in the event
of a transaction error is handle it and retry or abort the transaction
and cancel your updates (CancelUpdates).   If you cancel you updates,
then you loose everything the user had entered.   Therefore you will
need to "cache the cache", so to speak.    I have found in practice
that CancelUpdates Doesn't seem to do anything at all( on a TQuery, at
least).  Someone suggested turning on and off the cached updates:

        Query1.CachedUpdates := True;
        Query1.CachedUpdates := False;

(To whomever made that suggestion, thanks.  Sorry, I can't remember
your name at the moment.)  However, this also is not perfect.  As soon
as you do this, you have lost all of the updates that had previously
been applied to the dataset in prior, successful, transactions.  

So, it looks like there is no way to get to a pre-ApplyUpdates state.
However, you could "cache the cache", and on a transaction error,
close and re-open the query, then apply the user updates.  I haven't tried
this, but I can think of no reason why this shouldn't work.

I hope this helps someone, and saves them from calling the support line.
IMO, Borland really dropped the ball on this one.  Some kind of
note about this shortcomming should have been included with the Cached
Updates Documentation.

Mike Conner
Milwaukee Software

Other Threads