3

The documentation says:

TFMTBCDField encapsulates the fundamental behavior common to binary-coded decimal (BCD) fields. BCD values provide greater precision and accuracy than floating-point numbers. BCD fields are often used for storing and manipulating monetary values.

Unfortunately, I'm finding that using such a field in combination with an Extended value, I'm losing precision (in this example two digits): if I use

BcdField.AsExtended := Value;

the value gets actually truncated to four digits. What can I do about this?

Complete example:

procedure TForm1.Button1Click(Sender: TObject);
var
  LValue: Double;
  LDataset: TClientDataSet;
  LFieldDef: TFieldDef;
begin
  LValue := 1 / 3;
  LDataset := TClientDataSet.Create(self);
  try
    LFieldDef := LDataset.FieldDefs.AddFieldDef;
    LFieldDef.DataType := ftFMTBcd;
    LFieldDef.Size := 6;
    LFieldDef.Precision := 10;
    LFieldDef.Name := 'A';
    LDataset.CreateDataset;
    LDataset.Append;
    LDataset.FieldByName('A').AsExtended := LValue;
    LDataset.Post;
    ShowMessage(FloatToStr(LDataset.FieldByName('A').AsExtended));
    ShowMessage(FloatToStr(LValue));
  finally
    FreeAndNil(LDataset);
  end;
end;

Output (in message boxes):

0,3333
0,333333333333333
ventiseis
  • 3,029
  • 11
  • 32
  • 49
  • Ehm, define it as `ftExtended`? – Victoria Dec 12 '17 at 20:49
  • Okay, but most of the time we are using `MSSQL` and `NUMERIC(..)` fields, which results in `ftFmtBcd` fields on the delphi side. I don't want to change the fields in the database... – ventiseis Dec 12 '17 at 20:58
  • 2
    It seems to be a Seattle problem. Just tried it in Tokyo and was surprised that it worked. Perhaps related to https://quality.embarcadero.com/browse/RSP-16200? – ventiseis Dec 12 '17 at 21:07
  • 2
    Try with `LFieldDef.Size := 15; LFieldDef.Precision := 15;`. – Rudy Velthuis Dec 12 '17 at 22:45
  • 1
    Strictly speaking it would be more accurate to use `TField.AsBCD` which returns a [TBcd](http://docwiki.embarcadero.com/Libraries/Tokyo/en/Data.FmtBcd.TBcd) record. But there may be a problem... I don't have a recent version to check the source code, but current documentation states that: `TFMTBCDField.Value` is the same as `AsBCD`, which in turn "returns the value of AsCurrency converted to a TBcd value". This implies a bug in that by going via `Currency`, a field specifically intended to support larger precision than `TBCD` is being hamstrung to `Currency` precision in any case. – Disillusioned Dec 13 '17 at 00:49
  • @RudyVelthuis Doesn't change a thing – ventiseis Dec 13 '17 at 08:41
  • @CraigYoung Thank you. this works! `AsBcd` is overwritten in `TFmtBcdField`, so the documentation for `TField isn't applicable here. – ventiseis Dec 13 '17 at 08:50
  • @ventiseis: it does for me, D10 Tokyo. – Rudy Velthuis Dec 13 '17 at 12:32
  • @ventiseis Thanks for confirming. I've written up an answer based on your feedback. You may already be familiar with some of the points raised in my answer; I've included them for the sake of completeness. – Disillusioned Dec 13 '17 at 13:45

1 Answers1

4

Strictly speaking it would be more accurate to use TField.AsBCD which returns a TBcd record. TFmtBcdField overrides the default implementation and returns an accurate TBcd record.

TBcd is a record structure with support for simple arithmetic operators and implicit conversion from Integer and Double. So it should be suitable for most purposes.

The drawbacks are:

  • Mathematical operations may be marginally slower due to lack of built in machine instruction. But it's probably a reasonable trade-off for cases where precise representation is required. Benchmark and evaluate as needed.
  • Some functions that take Double or Integer parameters may need TBcd overload implementations.

Some related considerations:

  • The use of Double is not appropriate if you require accurate precision due to the nature floating point representation. See Is floating point math broken? for more info.
  • The use of Extended has the same problems as Double even though the extra 2 bytes provide greater range and higher precision - it's still a floating point data type. Furthermore, Extended has its own problems. Take note of the warnings here.
  • If you don't need precise representation, then you can convert to Double using BcdToDouble. See BCD Support Routines.
  • Another option to consider in cases where you don't need more than 4 decimals, but do need precise representation: Use the Currency data type. It's represented as a 64-bit integer with assumed division by 10000 which is how it supports 4 decimal digits.
Disillusioned
  • 14,635
  • 3
  • 43
  • 77