Hi Jason,

I was just taking a quick look at the code and I begin to see some of
the problems you face. However I do wonder if one of Marco's
suggestion may be worth considering...

When -SQLScale = 4 do a direct assignment to the currency result
value. That way we can at least say that NUMERIC(18,4) will be
treated consistently with the Delphi Currency data type.


When the -SQLScale <> 4 then there are some real problems that cannot
be worked around (AFAICT), they can only be documented and highlighted
so that developers understand the restrictions...

When scale < 4 it is not possible to guarantee that assignment to
Delphi currency type will not overflow. eg.

NUMERIC(18,2) actually has 16 digits of integer significance

Delphi currency type has the range:
922337203685477.5808 - 922337203685477.5807
which is only 14 digits of integer significance.

When scale > 4 then assignment to currency type will lose data -
because the currency data type only has 4 decimal places.


So I suggest documenting the restrictions and then altering your code
so that you read the quad/int64 value directly as currency, and then
only applying scaling (divide or multiply by 10) for the difference
between the SQLScale and 4. That way you should always fit as well as
possible within the constraints specified above.


The result of this is that developers could safely use AsCurrency for
NUMERIC(18,4). For all other scales I would recommend that developers
use AsExtended - or embed value restrictions in their domain/field
declarations to ensure the database will not accept data that would
overflow the delphi currency type. eg:

CREATE DOMAIN CURRENCY2_D
AS NUMERIC(18,2)
CHECK( VALUE BETWEEN -99999999999999.99 AND +99999999999999.99 );

CREATE DOMAIN CURRENCY3_D
AS NUMERIC(18,3)
CHECK( VALUE BETWEEN -99999999999999.999 AND +99999999999999.999 );


Note that these extreme values are well worth testing. I just tested
these using IB_SQL and when I edited the field in the browse there was
rounding errors on the last decimal place. When I issued an UPDATE
there were no rounding issues, so the problem is almost certain in the
IBO code but I have not yet investigated exactly where.


Are you considering looking into discovering the precision, so that
Marco's currency control could limit its input accordingly? I said
that it would probably be too expensive to performance (as well as
only being available in IB6+), but I was wondering what your thoughts
were in this regard.


Geoff Worboys
Telesis Computing