Daniel, Dag,
thanks for your answers: they are really helpful.
Well, right now we are now using NUMERIC(18,2) field types for currency
amount fields... so that values are stored in the db with the exact
number of decimal we need (0k, we are actually in the process of
reviewing this approach).
Do you think we should use a different field type? (e.g. DECIMAL)
Again, thanks,
Jacopo
Daniel John Debrunner wrote:
Jacopo Cappellato wrote:
Hi all,
my name is Jacopo Cappellato, I'm one of the developers of the OFBiz
project (www.ofbiz.org), that will soon start the incubation process.
Welcome, I've watching the vote on the incubator list.
OFBiz is using DerbyDb as the default db and it works pretty well even
if we have found some minor issues.
That's great, please inform this list of any issues with Derby as you
find them.
One of these is the way numbers are approximated when inserted in
numeric fields.
Namely, DerbyDB truncates the values inserted in a numeric field instead
of approximating them
(http://db.apache.org/derby/docs/10.0/manuals/reference/sqlj132.html).
Since the general approach in OFBiz is that of using db approximations
(instead of doing them in the code), this is not a good thing because
other databases (e.g. MaxDB/SapDB) perform approximations instead of
truncations in the same situations.
DECIMAL are exact numeric types, which means they do not approximate
values or calculations. They are designed for financial and scientific
applications where exact calculations are required. You don't want your
bank approximating the addition of your pay check into your existing
balance. :-)
MySQL's DECIMAL implementation has always been wrong and they have
finally fixed it in version 5.
Since OFBiz is a business application suite/framework, what sort of
"ERP, CRM, E-Business / E-Commerce, etc." requires approximations? Seems
like a set of applications that require exactness.
So switching from DerbyDB to another one could lead to different
calculations.
Switching from Derby to databases that handle DECIMAL's incorrectly can
lead to different calculations. My advice, don't switch to those broken
databases. :-)
Is there a way to configure the way approximations are done? Is it
something that should be fixed?
No it should not be fixed. Note that MySQL has fixed their broken
DECIMAL implementation in version 5.0. I never understood how people
could be using MySQL and DECIMAL, I hope no banks were.
If you want approximate numeric values, then REAL and DOUBLE are the SQL
datatypes you require.
Dan.
