#1 2011-08-27 15:54:55

esmondb
Member
From: London
Registered: 2010-07-20
Posts: 299

binary-coded decimal

Would it be worth adding a binary-coded decimal type to the synopse library? It would avoid the use of delphi floating point types and hopefully be quick to convert to text/json.

Offline

#2 2011-08-27 18:32:03

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: binary-coded decimal

The Delphi currency type is already with 4 fixed decimals.
It's perfectly safe, stored internaly as an Int64 (the value *10000), and there are dedicated fast and direct conversion functions in SynCommons (faster than the version provided by Delphi, which use a conversion to a floating-point each time).

It's natively handled by the ORM, and also by our SQLite3 or other DB classes: in particular, it will use no floating-point conversion for Oracle or OleDB.

Offline

#3 2011-08-28 06:10:20

esmondb
Member
From: London
Registered: 2010-07-20
Posts: 299

Re: binary-coded decimal

Thanks for explaining that - not looked at Delphi's currency type before. I've been working with some data created by programs written with CB86 (an old BASIC like language) which uses a variable length binary-coded decimal and thought they looked interesting.

Offline

#4 2011-08-28 16:29:40

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: binary-coded decimal

What's nice with Currency is:
- Its 4 decimal precision is perfect for avoiding any rounding issue;
- It is an Int64 "internally" so it's very fast to work with, and won't suffer from rounding issues (such as with double or extended type);
- It is a native OleDB field type - and a COM interface type see http://msdn.microsoft.com/en-us/library … S.85).aspx
- It can be faster converted to/from text than standard floating-point, since there is no scientific format to handle, and it can use Int64 arithmetic instead of slower and round-error-prone floating point arithmetic.

SQLite3 does not handle this type - so we use a FLOAT/DOUBLE type instead.
OleDB handles this natively, and we (ab)use (of) this feature.
Oracle/OCI does not handle this, be we retrieve the value as text from the DB, and use our fast conversion routine to avoid any conversion issue. When used in the ORM, we define an Oracle NUMBER(19,4) column type for currency fields. See ORA_FIELDS[] array in SynDB.pas.

Our ORM handles currency as native type, and JSON is used to avoid any rounding error: since "double" is just used for storage, we avoid any rounding border effect in your application.

Offline

Board footer

Powered by FluxBB