2

I have a TADODataset executed with (only for example):

SELECT id, name, lastname, name + ' ' + lastname as fullname
FROM persons
ORDER BY lastname

After I open the dataset, I can modify "name" and "lastname" fields, but can't modify"fullname", because it's calculated.

I try to open TADODataset to TClientDataset via DataProvider, but it takes too long (there are about 100K records in source dataset):

SrcDS.FieldDefs.Update;
for i := 0 to Pred(SrcDS.FieldDefs.Count) do
  SrcDS.FieldDefs[i].CreateField(SrcDS).ReadOnly := false;
DestDS := TClientDataset.Create(nil);
DestDS.SetProvider(SrcDS);
DestDS.Open;
DestDS.SetProvider(nil);

All in all, i want to have an independent dataset with changeable fields.
How can i modify calculated fields in the dataset?

kobik
  • 21,001
  • 4
  • 61
  • 121
a.vakhrushev
  • 37
  • 3
  • 2
    You want fullname to be editable - and then what? You can't write it back to the database (unless you parse it back into name and lastname in an event handler, which is error prone). What are you trying to accomplish with fullname? – Jan Doggen Mar 15 '13 at 08:32
  • I don't want to write it back to database. Dataset linked to TcxGrid component. I want change dataset on client only, so changes will be displayed in component. – a.vakhrushev Mar 15 '13 at 08:36
  • You change the full name by changing its components... What's the use of allowing edit's to full name when you don't write them back? Unless would you be splitting the new full name and writing the split results to the first/last name fields? In that case: you'll have lots of fun with multi-cultural names. – Marjan Venema Mar 15 '13 at 09:42
  • 1
    It was only example to demonstrate calculated field – a.vakhrushev Mar 15 '13 at 09:56
  • 1
    +1 This is a good question. I was trying to achieve the same behavior at some point. my conclusion was, it can't be done. even with `LockType = ltBatchOptimistic` you *could* edit the calculated filed, but I didn't find a way to tell the provider to *not* update changes made to the calc field during `UpdateBatch`. – kobik Mar 15 '13 at 12:02
  • You probably would need to undo all the changes on fullname before you do an `ApplyUpdates`. This way the provider would see that it don't need change anything. Another option would be use `onBeforeUpdateRecord` event handler of the DataSetProvider component to write your own SQL. See this [Building Applications With ClientDataSet and InterBase Express](http://edn.embarcadero.com/article/27653), subtitle "Updating Datasets That Cannot Be Updated". – EMBarbosa Nov 06 '13 at 14:22
  • Also, very similar question: http://stackoverflow.com/questions/13505612/i-need-to-avoid-attempting-to-update-non-physical-fields-in-a-delphi-tclientdata?rq=1 – EMBarbosa Nov 07 '13 at 12:54

1 Answers1

2

You have to calculated the field in Delphi. Create a new field by rightclicking on the TADODataset component, select New Field, give it a name and set it's type to 'calculated'.

In the OnCalculateFields-Event simply write:

Procedure TMyDataModule.MyDatasetCalculate(Sender : TDataset);
Begin
  MyDataSetFullName.AsString := MyDatasetFirstName.AsString+' '+MyDataSetLastName.AsString;
End;

Update: Regarding your second problem (100.000 records): If you load them into your ADODataset using LockType = ltBatchOptimistic, it will be fast enough and nothing is saved to the database, unless you call the UpdateBatch Method.

If this is still too slow, try using the async load feature (See the ExecuteOptions)

alzaimar
  • 4,572
  • 1
  • 16
  • 30
  • Sad, but true: i need to modify current dataset or fast create copy of it – a.vakhrushev Mar 15 '13 at 07:47
  • "i need to modify current dataset or fast create copy of it" ?? What does that mean – Jan Doggen Mar 15 '13 at 08:30
  • Only modify dataset, not to write it back to database. – a.vakhrushev Mar 15 '13 at 08:39
  • Ok, and you still want to e.g. change the first name and the full name automatically changes as well? Then you could use `LockType = ltBatchOptimistic` and finally discard/cancel the changes. But still have to do the field calculation in Delphi (naturally) – alzaimar Mar 15 '13 at 08:57
  • I don't need full name changes automatically anymore, only dataset with fields, which i can modify – a.vakhrushev Mar 15 '13 at 09:06
  • 1
    @Tona, "Only modify dataset, not to write it back to database". You dont want to write "name, lastname" back to the DB? if yes, alzaimar gave you the correct answer. – kobik Mar 15 '13 at 12:06