Problem with reading/writing Currency values from/to database fields with the NUMERIC type.

When reading NUMERIC data from a database, objects of the TFMTBCDField type are used. They use the TBCD type for storing values, which allows you to store values with high accuracy.
However, when data from such an object is placed in a TValue, the conversions BCD -> TCustomVariant(FMTBcdVariantType) -> int64/double occur.
The last conversion (TCustomVariant(FMTBcdVariantType) -> int64/double) occurs in the TValueHelper method FromVariant. In this case, there may be a loss of accuracy.
For example, if the database, in the numeric field, stores the value 12345678901234.1234, then when reading the data in TValue<Double>, we get the value 12345678901234.1200.
To demonstrate this problem, you can add a TTestValueHelper to the test.FromCustomVariantFmtBcd the following cases:

‌

  v := VarFMTBcdCreate(StrToBcd('12.3456789', TFormatSettings.Invariant));
  fSUT := TValue.FromVariant(v);
  CheckEquals(string(v), fSUT.ToString);

  v := VarFMTBcdCreate(StrToBcd('12345678901234.56', TFormatSettings.Invariant));
  fSUT := TValue.FromVariant(v);
  CheckEquals(string(v), fSUT.ToString);

  v := VarFMTBcdCreate(StrToBcd('12345678901234.12345', TFormatSettings.Invariant));
  fSUT := TValue.FromVariant(v);
  CheckEquals(string(v), fSUT.ToString);

Without changes in Source\Base\Spring.pas, all three tests generate an error. This is wrong.

There are many ways to solve this problem.
In the attached Source\Base\Spring.pas file, I implemented a solution that is not the most efficient (in terms of time), but it is the most compatible with the current version in terms of behavior.

In addition, when storing values of the Currency type in the database (in the NUMERIC fields), the values are passed through parameters of the TCurrencyField type. However, fields of this type use the double type to store values inside.
This is even specifically mentioned in the description (http://docwiki.embarcadero.com/Libraries/Rio/en/Data.DB.TCurrencyField)..)

This leads to a loss of accuracy of the values passed from the program to the database.
You can solve this problem by using objects of the TFMTBCDField type instead of TCurrencyField. To do this, you need to make a minor change to the TDBParam.TypeInfoToFieldType method (Source\Persistence\SQL\Spring.Persistence.SQL.Params.pas file).

I found these errors when working under Delphi 10.3, but I believe that they will occur on other versions.

I do not have the permission to create a pull request, so I attach the files with the changes I made.

Comments (2)