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

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 Received on Wed May 21 2008 - 04:26:45 CDT