Board index » delphi » ApplyUpdates Hangs - HELP!

ApplyUpdates Hangs - HELP!

Hi,

I'm using cached updates with a TQuery/TUpdateSQL to cache deletes and
inserts but am having a problem.

Basically I'm deleting all record for a certain criteria and then replacing
them with new ones. The reason I don't do a modify for each record is that
the number of replacment entries may be different then the original records.

The problem I'm having is that after the Deletes and Inserts are cached it
hangs up on the ApplyUpdates call. If I just do the Delete it works fine and
if I just do the Insert it works fine. But, when I do the delete and insert
together hangs in the ApplyUpdates with the SQL cursor. I also noticed with
SQLTrace that a connection was started right after the delete SQL
statements.

 What am I doing wrong?  Whats missing?  HELP !!!

Thanks in advance
Jeff Little
j...@medusaresearch.com

I'm using Delphi4 SP2 with BDE 5.0.1.22 and MS-SQL 6.5

BDE Params:

SQLPASSTHRUMODE = SHARED AUTOCOMMIT
SCHEMA CACHE SIZE = 200
BACTH COUNT = 200

Here's the revelant code:

procedure TForm1.cmdReplaceClick(Sender: TObject);
begin
  with qrySub do
  begin
    Database.StartTransaction;
    try
//    delete all existing records
      First;
      while not EOF do
        Delete;

//    insert new info
        Insert;
        FieldByName('Entry_ID').Clear;  {identity field}
        FieldByName('Person_ID').AsInteger    :=
qryPerson.FieldByName('Person_ID').AsInteger;
        FieldByName('EntryStr').AsString          := edtEntry1.AsString;
        Post;

        Insert;
        FieldByName('Entry_ID').Clear; {identity field}
        FieldByName('Person_ID').AsInteger    :=
qryPerson.FieldByName('Person_ID').AsInteger;
        FieldByName('EntryStr').AsString          := edtEntry2.AsString;
        Post;

//    make changes to db
      ApplyUpdates;
      Database.Commit; {on success, commit the changes};
    except
      Database.Rollback; {on failure, undo the changes};
      raise; {raise the exception to prevent a call to CommitUpdates!}
    end;
    CommitUpdates; {on success, clear the cache}
  end;
end;

procedure TForm1.qrySubUpdateRecord(DataSet: TDataSet;
  UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction);
begin
  if UpdateKind in [ukModify,ukDelete] then
  begin
    TUpdateSQL( qrySub.UpdateObject ).Apply(UpdateKind);
    UpdateAction := uaApplied;
  end
  else
    UpdateAction := uaSkip;
end;

 

Re:ApplyUpdates Hangs - HELP!


You might try moving the startTransaction statement:

procedure TForm1.cmdReplaceClick(Sender: TObject);
begin
  with qrySub do
  begin
//    Database.StartTransaction;
    try
//    delete all existing records
      First;
      while not EOF do
        Delete;

//    insert new info
        Insert;
        FieldByName('Entry_ID').Clear;  {identity field}
        FieldByName('Person_ID').AsInteger    :=
qryPerson.FieldByName('Person_ID').AsInteger;
        FieldByName('EntryStr').AsString          := edtEntry1.AsString;
        Post;

        Insert;
        FieldByName('Entry_ID').Clear; {identity field}
        FieldByName('Person_ID').AsInteger    :=
qryPerson.FieldByName('Person_ID').AsInteger;
        FieldByName('EntryStr').AsString          := edtEntry2.AsString;
        Post;
   except
       cancelUpdates;
       raise;
   end;

//    make changes to db
   try
      Database.StartTransaction;// You don't need the transaction active until updates are applied
      ApplyUpdates;
      Database.Commit; {on success, commit the changes};
    except
      Database.Rollback; {on failure, undo the changes};
      raise; {raise the exception to prevent a call to CommitUpdates!}
    end;
    CommitUpdates; {on success, clear the cache}
  end;
end;

=Bill=

Other Threads