> My code would produce:
> tmpINT64=30*100 ----> tmpINT64=3000 that is OK and will be ok when
> read since it will be scaled down
> IBO code in the loop does in two steps:
> tmpCurrency := tmpCurrency * 10 --> Currency = 300
> tmpCurrency := tmpCurrency * 10 --> Currency = 3000 OVERFLOW!!!
> So my code and IBO are not the same at all.

Ahhh... (the light finally comes on :-)
I see what you are getting at, I was coming at this from a different
perspective.

The current (for VCL40+) code is...

for ii := 1 to -SQLScale do tmpVal := tmpVal * 10;
int64(FNewColumnBuffer^) := Trunc( tmpVal );

This is different to what I expected, as it is trying to treat
currency as a true floating point value - by expanding value to
integer and then truncating the decimal places. My suggestion **was**
going to be...

for ii := -3 to SQLScale do
tmpVal := tmpVal / 10;
currency(FNewColumnBuffer^) := tmpVal;

Note that there is preliminary code in the function that means
SQLScale > 4 is already excluded from this processing. So the above
simply scales currency value **down** to fit with the database
definition if scale < 4 (well -4 but you get gist).

This then runs into your other comment...

> We must round to scale when assigning to tmpINT64, so if
> Delphi currency has 3 decimals and numeric(15,2) is used,
> it will be rounded to 2 decimals.

Delphi currency always has 4 decimal places.

I just ran a test... one of the differences between currency and a
IB/FB scaled integer is that Delphi does perform rounding on division
of currency values (exact 0.5 rounding). My example code above is
actually incorrect - because it would result in rounding of rounding.
eg:

2.5549
/ 10 = 0.2555
/ 10 = 0.0256

2.5549
/ 100 = 0.0255


So...

My first recommendation is to introduce some utility code to perform
down-scaling without this problem. ie. Accumulate a divisor using the
"for scale" loop, and then use that divisor to scale the value.

My second recommendation is simply restrict the scaling of currency so
that it matches the *difference* in scales - rather than the current
mechanism of going all the way to full integer and then truncating.

And my third recommendation is to do away with the scale > 4 test for
currency values. Insert a separate test into the scale adjustment for
such values and do away with converting currency to extended and then
back to scaled integer with the inherent problems.

And my final recommendation is to document the restrictions that exist
with AsCurrency. They still apply even after the above adjustments
are made. That is: Given NUMERIC(x,y)

A. IB/FB will not restrict input according to the declared
precision, so it is possible to insert values to the full storage
limit (if x > 9 then this means upto 18 digits regardless of x in the
declaration). To prevent the database from storing values that may
overflow the Delphi currency type (when the scale < 4) you need to
apply a check constraint on the domain/field similar to...

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 );


B. If the declared scale is > 4 then Delphi currency will lose
value resolution - it only supports exactly 4 decimal places. So if
you need a scale > 4 dont use AsCurrency - AsExtended is probably the
best you can do.


I think that covers it.

Jason, the question becomes; When and how do you want to tackle this?
I suggest that the changes are significant enough to warrant a test
release, typos are just too easy to make in this sort of stuff.


Geoff Worboys
Telesis Computing