I am including the changes Geoff had done for numerics in the next
sub-release. For this reason I will have it flagged as a test sub-release. I
would appreciate it if all those who have a lot of numerics/rounding issues
in their applications to get this test release and drill it heavily and
respond back to me about your findings and a little bit about what you did
to test it.

I'm with Geoff, this area is easy to put in a glitch and no matter how much
testing I do I won't be satisfied untill I hear it from at least 5 or 6 of
you that things look great.

Many thanks to Geoff for his assistance with these issues.

Kind regards,
Jason Wharton

----- Original Message -----
From: "Geoff Worboys" <geoff@...>
To: <IBObjects@yahoogroups.com>
Sent: Friday, November 09, 2001 4:41 AM
Subject: Re: [IBO] Is CURRENCY field column attributes ignored?


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