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.

May be the best way is to applyUpdates foreach only AFTER an exception, on the 500 records. — philnext, Apr 10 '12 at 14:55
ApplyUpdates on 500 records, if all is OK do it for the next 500 Ones, if you have an exception, ApplyUpdates for each of you 500 records until you have an exception, treat it and continue... — philnext, Apr 11 '12 at 09:09
@philnext: It is already this way. The issue is I need to know the one record causing the exception; TLama pointed out the good direction — Jako, Apr 11 '12 at 09:20

TLama · Accepted Answer · 2012-04-10T16:44:49.353

9

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.

edited Apr 10 '12 at 16:44

answered Apr 10 '12 at 15:06

TLama

75,147
17
214
392

But [`something`](http://docwiki.embarcadero.com/RADStudio/XE2/en/Applying_Updates) tells me it might work, see the paragraph what does the [`ApplyUpdates`](http://docwiki.embarcadero.com/Libraries/en/Datasnap.DBClient.TCustomClientDataSet.ApplyUpdates) do. – TLama Apr 10 '12 at 15:15
I've extended my untested code with how I think might be possible to show the message with information about the record which couldn't be updated along with the error describing why. – TLama Apr 10 '12 at 16:48
I hope so, unfortunately I can't verify this now. So, could you give some feedback how is it going after you test it, please ? Thank you! – TLama Apr 10 '12 at 20:27
One thing about the `Action` values described [`here`](http://docwiki.embarcadero.com/RADStudio/en/Reconciling_Update_Errors). I've used the `raSkip` what IMHO means that you skip the record update error but keep it in the change log, so the next attempt to `ApplyUpdates` without that record fix will raise this again. Maybe `raCancel` might be more suitable for you. It should revert the changes for this record on your dataset (so on insert, it should remove it from your dataset); or `raRefresh` what should update the record value to value on the server side. – TLama Apr 11 '12 at 07:07
1

Thanks, I've rapidly tested with a showmessage like yours in the ReconcileErrorEvent and it looks like working. The changes to the software are now freezed but if the error will rise again I'll know exactly what to do: log to file and Action=raCancel. Thank you again – Jako Apr 11 '12 at 09:23

score 3 · Answer 2 · answered Apr 10 '12 at 16:14

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.

TClientDataset ApplyUpdates error because of database table constraint

2 Answers2

Linked