TClientDataset ApplyUpdates error because of database table constraint

Question

I have an old Delphi 7 application that loads data from one database table, make many operations and calculation and finally writes records to a destination table.

This old application calls ApplyUpdates every 500 records, for performances reasons.

The problem is that, sometimes, in this bunch of records lies one that will trigger database constraint; Delphi fires an exception on ApplyUpdates.

My problem is I don't know which record is responsible for this exception. There are 500 candidates!

Is it possible to ask TClientDataset which is the offending record?

I do not want to ApplyUpdates foreach appended record for speed issues.

Answer 1

I think you may try to implement the OnReconcileError event which is being fired once for each record that could not be applied to the dataset. So I would try the following code, raSkip means here to skip the current record:

procedure TForm1.ClientDataSet1ReconcileError(DataSet: TCustomClientDataSet;
  E: EReconcileError; UpdateKind: TUpdateKind; var Action: TReconcileAction);
begin
  Action := raSkip;
  ShowMessage('The record with ID = ' + DataSet.FieldByName('ID').AsString +
    ' couldn''t be updated!' + sLineBreak + E.Context);
end;

But please note, I've never tried this before and I'm not sure if it's not too late to ignore the errors raised by the ApplyUpdates function. Forgot to mention, try to use the passed parameter DataSet which should contain the record that couldn't be updated; it might be the way to determine what record caused the problem.

And here is described the updates applying workflow.

Answer 2

Implementing OnReconcileError will give you access to the record and data that is responsible for the exception. An easy to accomplish this is to add a “Reconcile Error Dialog”. It is located on the “New Items” dialog which is displayed by File | New | Other. Once you have added it to your project and used it in the form with the clientdataset. The following code shows how it is invoked.

procedure TForm1.ClientDataSetReconcileError(DataSet: TCustomClientDataSet;
  E: EReconcileError; UpdateKind: TUpdateKind;
  var Action: TReconcileAction);
begin
  Action := HandleReconcileError(DataSet, UpdateKind, E);
end;

It will display instead of the exception dialog. It will allow you to view the offending data and select how you want to proceed. It has been over 5 years since I last used it, hopefully I have not forgotten some details.