currency type using FireDAC only saves 2 decimals

[Silber_Felix]

I've found this Topic:

currency type property is saving only two decimals in PostgreSQL with firedac

I have a property that is of type currency, which can have up to four decimals, which is also mapped to four decimals and when saving it is only two decimals. I'm using delphi Seattle, PostgreSQL version 10, aurelius version 5.11, FireDac. with firebird and sqlite databases worked, recorded with four decimals [Entity] [Table('mov')] [Id('FId', TIdGenerator.IdentityOrSequence)] [Sequence('seq_mov')] TMov = class strict private [Column('id', [TColumnProp.Required])] FId: int64; [Column(…

Taking a closer look into FireDAC-Methods and how this is used within Aurelius, in my opinion, as Studart Francy already wrote, you should use ftBCD instead of ftCurrency.

Because FireDAC will use SQL_CASH (SQL-OID 790).
For example postgres will then only save 2 digits (PostgreSQL: Documentation: 15: 8.2. Monetary Types)

If you would allow ftBCD for Postgres and use ftBCD for Params instead of ftCurrency, all digits would be stored...

It feels wrong, that you are using numeric for definition of currencies while you are using FireDAC-Money-Type for setting values.

From my point of view you should at least use currency and Moneytype or better BCD with numeric.
Maybe this should be configured using GlobalSettings.

[wlandgraf (Wagner Landgraf)]

@Silber_Felix

Because FireDAC will use SQL_CASH (SQL-OID 790).

So, as I mentioned in that topic, that's a FireDAC issue, isn't it? If the Currency Delphi type can hold up to 4 digits, it shouldn't be using a Postgres type that only supports 2 digits. Isn't there a global FireDAC type mapping where you can map all ftCurrency types to ftBCD?

[Silber_Felix]

@wlandgraf

Isn't there a global FireDAC type mapping where you can map all ftCurrency types to ftBCD?

As described in documentation:
https://docwiki.embarcadero.com/RADStudio/Alexandria/en/Data_Type_Mapping_(FireDAC)
it is.

But you should be aware of it, implementing your own postgres driver.

[Silber_Felix]

After testing this in the used FDConnection:
https://docwiki.embarcadero.com/RADStudio/Alexandria/en/Data_Type_Mapping_(FireDAC)

FormatOptions.OwnMapRules := True;
FormatOptions.MapRules.Add(dtBcd, dtCurrency);

everything works fine (for postgres).
Be aware, that it might not work with other databases...