Re: Float Precision with PROC*C

In article <CnuM2B.Mzp_at_ireq.hydro.qc.ca>

           paubert_at_tdsb-s.mais.hydro.qc.ca "Philippe Aubert" writes:

> We have an Oracle table (Oracle 6.0.33) which contains a column
>like this :
> NB_POINTS NUMBER(6,3)
>
>
> In a PRO*C program, we declared a FLOAT variable to receive the
>contents of the column NB_POINTS.
>
> When we have 0.100 in the Oracle table, we received 0.09999994039535..
>when we use a printf("%f",nb_points);
>
> We don't want to use an external format because we make calculation
>with this variable.
>
> Is someone Aware of this problem ?
> How could we solve it, without "playing with the external format"?
>
> Thank you to give me a quick answer.
>
> Philippe AUBERT
>

You will come up against this type of problem again and again. Many floating point numbers which are easily represented in base-10 are not exactly representable in base-2. Base conversions into and out of base-2 (float) to base-10 (printf) will cause errors. Performing arithmetic on these numbers can introduce rounding errors also.

Three solutions:

(1) Your numbers are of such a magnitude that you will be able to multiply them by 1000 when retrieving them from the database. Use a long int in your program. Divide the number by 1000 when re-inserting it into the database.

(2) Oracle doesn't screw up. Always keep your numbers in VARCHARs and use SQL to manipulate them. Example

	VARCHAR v[100];
	EXEC SQL SELECT X INTO :v FROM TAB WHERE...;
	EXEC SQL SELECT :v * 2 / 3 INTO :v FROM DUAL;
	EXEC SQL UPDATE TAB SETTING X = :v WHERE...;

(3) Keep your numbers in character arrays and use an Arbitrary Precision Maths package to manipulate them. There exist one or two of these in the public domain (look for apm...) and I've written add and subtract functions that work well. Even if they are slow they are much quicker than using Oracle as in (2) above.

-- 
Paul Beardsell                          SAM Business Systems Ltd
~~~~~~~~~~~~~~                          21 Finn House, Bevenden St, HOXTON,
pbeardsell_at_cix.compulink.co.uk          Hackney, London, N1-6BN, UK.
paul_at_hoxton.demon.co.uk                 (+44 or 0)71 608-2391