Re: Pro*C: Loss of Decimal Places INSERTing From "double" Host Var into NUMBER(16,6) Column

"Fleetie" <fleetie_at_nospam.com> wrote in message news:g10psm$clq$1_at_aioe.org...
> Hi.
>
> Forgive the news server I'm posting from; it's the only one I have easy
> access to here at work, and it's a work-related problem.
>
> Yesterday, we discovered that we are getting loss of precision, in the
> form of truncation to 5 decimal places to the right of the point, when
> INSERTing from a host variable in Pro*C that's declared as a C double.
>
> It's a clean truncation/rounding to 5 decimal places, though the column
> into which the INSERT is occurring is declared as NUMBER(16,6).
>
> The numbers I'm trying to INSERT are unix-format times, with fractions
> of a second down to microseconds, e.g.: 1211363748.425374.
>
> So there are 16 decimal places to be stored in total, with 6 to the right
> of the decimal place.
>
> Yet when I SELECT the values back out of the table, I see all the values
> rounded to 5 places to the right of the point.
>
> HOWEVER, if I INSERT a double LITERAL value, all of the decimal places are
> inserted correctly:
>
> EXEC SQL INSERT INTO TESTPRECISION (KEY,VALUE)
> VALUES
> (
> -1,
> 1234567890.123456
> );
>
> I then see the full 6 places to the right of the point, in the database.
>
> Also, even if I increase the column width to accommodate more places in
> total, e.g. to NUMBER(19,7), the INSERTS from the double host variable
> are still rounded off at 5 places.
>
> Then I tried converting the numbers to a string with:
>
> sprintf(TVSTime,"%.6f",TVTime);
>
> When I INSERT the string variable TVSTime into the NUMBER column, I get
> the full
> precision stored, presumably because Oracle performs the conversion and
> INSERTs the converted values without loss of precision.
>
> I'm at a loss to explain why INSERTing from a C double host variable only
> stores 5 decimal places to the right of the point.
>
> The version information is:
>
> OS: SunOS solar 5.9 Generic_118558-06 sun4u sparc SUNW,Sun-Fire-V440
> Oracle : Oracle Database 10g Release 10.1.0.2.0 - 64bit Production
> Pro*C: Pro*C/C++: Release 10.1.0.2.0
>
> Could it be anything to do with option native_types in Pro*C's pcscfg.cfg?
>
> Thanks in advance for any help to resolve this.
>
>
> Martin
>
>

Use timestamp datatype.
Jim Received on Wed May 21 2008 - 09:19:26 CDT