Subject Re: [IBO] retrieving size and precision of numeric fields peter@cyionics.com 2003-06-09T06:49:18Z
Hi Salim

What you are trying to achieve is imposible without rounding

100/3 produces a re-cursive number

i.e. 33.333333333......... to infinity

You need to decide to what precision you wish to work.

If working to 2 digits for instance then use 2 digits and the third to round with or put another way , if I was working in UK pounds and pence I would scale all my entries to be based upon 0.1pence.

You may also wish to use integers as the precision can be more tightly controlled.
This can be achieved by storing you values in multiples of 10^n

e.g. for 1 digit resolution after the decimal point multiply by 10
for 2 100 etc

e.g. 100 / 3 using integer division would become

10000 / 3 = 3333 which then dividing by 100 again and converting to a float

33.33

etc

This way the precision and problems with floating point calculations can be ignored and you will have more control over potential errors.

Peter

----- Original Message -----
From: Salim Naufal
To: IBObjects@yahoogroups.com
Sent: Sunday, June 08, 2003 11:09 PM
Subject: [IBO] retrieving size and precision of numeric fields

I have a database that contains fields of type Numeric(18,2).
Using a TIBOQuery, the corresponding field is a TBCDField having
"Precision"=0 and "Size"=8 instead of 18 and 2 respectively. (Using IBX
components, the resulting TIBBCD Field Precision and Size are correct)

This causes a problem in the following case:

Using ClientDataSets, the resulting precision and size become 32 and 8
respectively. This causes problems in applications where rounding to 2
decimal digits is required. To illustrate this case, take an accounting
program. Sum(Debits) = Sum(Credits). If one is to have a debit of 100.00
which is to be spread on 3 accounts for credits. We should get the
following:

Debit 100.00,
Credit Account 1 = 33.33 ,
Credit Account 2 = 33.33 ,
Credit Account 3 = 33.34 (Which is Debit - Credit Act1 - Credit Act2).

However, using IBO with the ClientDatasets, I get the following:

Debit 100.00,
Credit Account 1 = 33.3333 ,
Credit Account 2 = 33.3333 ,
Credit Account 3 = 33.3334

When the changes are applied to the database, the amounts are rounded and we
get:
Debit 100.00,
Credit Account 1 = 33.33 ,
Credit Account 2 = 33.33 ,
Credit Account 3 = 33.33

Therefore, The Sum(Credits) = 99.99 and not 100

As a result, in order to use IBO in an accounting program, we have to add
additional rounding functions to correct this.

I have uploaded on my site a Delphi 7 project and a database to illustrate
this problem. It illustrates the problem using IBO and also shows the
results using IBX components. The Total Size including the database is 36
KB:

http://www.softnet-eng.com/support/prectest.zip

Simply extract the files to directory c:\PrecTest.

The ideal solution would be to have the IBO fields retrieve the correct
field Precision and Size. How can we achieve this? Are there other
alternatives?

___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !