How can I implement "writable calculated" fields in a TDataSet?

Question

I am in the need to add additional fields to a TDataSet that don't exist in the underlying database but can be derived from existing fields. I can easily do this with caclulated fields and that works perfectly.

Now I want to edit these fields and write the changed data back. I can reverse the calculation to write the data back into the existing fields, but the DB controls just don't let me edit calculated fields.

Is there any approach that allows me to do this?

Update: Ok, some more details about the background.

The dataset has a blob field, which is a TBytes representation. Some of the bytes are identified to contain information that can be represented in a convenient way with existing DB edit fields. Not all of the bytes are known, though, so the TBytes representation has to be kept as it is for processing through another application that knows about it. This app also modifies existing and inserts new records.

The TBytes of different records in the dataset often map to different fields representations, although setting a filter or range on the dataset will ensure that they have the same mapping.

As I said, extracting the known bytes and convert it into strings, dates, numbers and so on via calculated fields is no problem. Reconverting those values into the TBytes is also possible. The problem is making those extra fields editable, while keeping the dataset navigation intact.

If it helps: We have classes that do the bidirectional mapping, exposing the fields as published properties.

Uphemia

Calculated Fields are per definition read-only (well, during the CalcFields stage they are not). Please explain in more detail what you are after. – Roman

Are you willing to use or already using TClientDataSets? – Monikamoniker

I'm open for anything. Still investigating the other suggestions, but not satisfied yet. – Uphemia

Answer 1

The answer depends on a data access components you are using. I am using Anydac and it support fkInternalCalc fields, which may be as calculated as manually edited.

Darill

The database is local, so I'd rather avoid any layer not definitely needed. But the hint to the fkInternalCalc fields got me on the right path, I think. – Uphemia

It was hard to decide which answer to accept, but the hint to fkInternalCalc fields did kick me into the right direction. – Uphemia

Answer 2

I think calculated fields are by definition read-only, with values calculated on the client. What you want could probably be implemented by an updatable view. You could define the view with calculated fields already - these would be calculated in SQL on the server - and an update trigger, maybe an insert trigger, too - to perform the reverse calculation. Then from the client you could use the view transparently like a table.

Mickel

I know that calculated fields are read-only, hence the quotes. The problem is that these fields cannot be calculated in SQL nor on the server. – Uphemia

Interesting, why not? If you can't use SQL for some reason, can't you write a UDF? – Mickel

Perhaps another option would be to use a client dataset + dataset provider with an ApplyUpdates handler. – Mickel

How can you make the provider expose those fields not part of the original dataset? Do you have an example? – Uphemia

Sorry, I don't have any example at hand, but as Fabricio said, you can use OnGetRecords to modify the data packet before sending it to the client and in the opposite direction I think you can use OnBeforeApplyUpdates. But regarding your update, I have UDFs for example to unzip a blob or parse XML and return certain values, as a result the UDF can simply be called within the SQL statement. – Mickel

Answer 3

I had similar Issue with a ClientDataSet, i have solved this issue using dummy fileds on the SQL-Stmt so i could simulate the Fields in the Database.

See my Question

Malachi

Answer 4

You can use TDatasetProvider.OnGetRecords (doesn't remember if this is the correct name of the event) and modify the datapacket sent to the Clientdataset.

Of course, you'll have to deal with them on an ApplyUpdates handler, as TOndrej said.

Velure

Answer 5

Woll2Woll's infopower components (I just tested their TwwDBEdit) allow to do such thing. So I would think whatever blocks you is at the TDBEdit level (or at the TFieldDataLink level).

What exactly differs in TwwDBEdit, I don't know. (And I'm not sure the license agreement would allow me to post here...).

Kitty

Answer 6

In our database design, some values are percentages relative to another column (called oMean below), whereas other float values are stored as absolutes. Our customers later wanted both options (rel. and abs.) for all fields, so we came up with the following class derived from TFloatField. It should work for all TDataSet descendants.

unit EditableCalcFloatField;

interface

uses
  db, classes;

type
  TEditableCalcFloatField = class(TFloatField)
  public
    oAbs, oRel, oMean: TField;
  protected
    function GetCanModify: Boolean; override;
    procedure SetAsFloat(Value: Double); override;
  end;

implementation

function TEditableCalcFloatField.GetCanModify: Boolean;
begin
  Result := oMean.AsFloat <> 0;
  if not Result then Exit;
  Result := (oAbs <> nil) or (oRel <> nil);
end;

procedure TEditableCalcFloatField.SetAsFloat(Value: Double);
var
  fMean                                 : Double;
begin
  inherited;

  if DataSet.State in [dsEdit, dsInsert] then begin
    fMean := oMean.AsFloat;
    if fMean = 0 then Exit;
    if oAbs <> nil then
      oAbs.AsFloat := Value / 100 * fMean
    else
      oRel.AsFloat := Value / fMean * 100;
  end;
end;

end.

To use it without a package, you have to create the field in FormCreate, before the dataset is opened:

with TEditableCalcFloatField.Create(Self) do
begin
  oAbs := sqlMerkmaleYourAbsoluteColumn;
  DisplayLabel := sDisp;
  oMean := sqlMerkmaleAssignedValue_Mean;
  Calculated := True;
  FieldName := 'R' + oAbs.FieldName;
  DataSet := sqlMerkmale;
end;

And of course, its contents can be set either in the OnCalcFields event or by the user.

Dannie

I don't understand the Boolean logic in SetAsFloat: if (fMean = 0) and (fMean = -999) then Exit;...` did you mean or? – Gift

Yes, you're right, it should be or. Thanks for pointing that out. – Dannie

Great idea, thanks! I would just add one thing: handling in SetAsFloat should be surrounded by AutoCalcFields set to false and then restored. Otherwise, in dsEdit state, if you change this field, and subsequently linked fields (oAbs, oRel), that triggers calculation of calculated fields which recurses back to setting this field, more likely than not. Which recurses indefinitely, then. – Bombast

Answer 7

Use a TQuery descendant (MyQuery) with 'Select *, 0 as TempField from MyTable'

Procedure MyQueryAfterOpen(Dataset:TDataSet);
Begin
    DataSet.FieldByName('TempField').ReadOnly := False;
End;

It is now an editabe temporary field

Ozellaozen