Oracle OCCI stmt.setTimestamp insert TIMESTAMP(6): microseconds always 0

Question

UPDATE: The "fraction of a second" parameter to Timestamp's constructor actually takes nanoseconds... I guessed it was hundredths of a second and my low values were rounded away. Question left for reference....

I'm struggling with Oracle's C++ library - OCCI. Summarily:

• creating Timestamp objects and verifying they're good to hundredths of a second (though I'd like more!)
• using stmt.setTimestamp then executeUpdate() to insert into a TIMESTAMP(6) column which should preserve microseconds
• selecting the row in Oracle SQL Developer: the sub-second component is always 0-ed e.g. 14-JUL-11 06.03.27.000000000.

Problem

I need subsecond precision - hopefully microseconds! We've put a lot of work into capturing that precision in our servers and need (at least some of) it for analysis.

Details

I create a Timestamp from year/month/day hour/minute/second/millisecond, reducing the last to hundredths of a second as that seems to be what the constructor supports. (No Oracle documentation I can find specifies the interpretation, but in a fromText example "xff" clearly corresponds to a ".##" hundredths suffix in the value to convert. What's the point of TIMESTAMP(6) supporting 6 decimal places if you can't insert them?)

oracle::occi::Timestamp temp =
    oracle::occi::Timestamp(_env, year, month, day,
                            hour, minute, second, millisecond / 10);

// re-extract the broken-down time from temp to prove it's stored successfully
int ye;
unsigned mo, da, ho, mi, se, fs;
temp.getDate(ye, mo, da);
temp.getTime(ho, mi, se, fs);

return temp;

Here, fs gets the milliseconds/10 value as expected.

I use this as in:

oracle::occi::Timestamp ts;
ts = _pImpl->makeOracleTimestamp(p->ATETimeStamp);
stmt.setTimestamp(11, ts);

Where field 11 is a TIMESTAMP(6).

Selecting the row in Oracle SQL Developer, the other parts of the timestamp column are correct but the sub-second component is 0-ed ala 14-JUL-11 06.03.27.000000000.

Any insight much appreciated!

(If relevant, using MSVC++ 2005, Oracle 10.2.0.4 sdk, SQL Developer 3.0.04 - please ask if something else might be relevant).

Thanks, Tony

Answer 1

Turns out the "fractional seconds" field is nominally in nanoseconds rather than hundredths. I wish Oracle would say that in their documents! I say nominally because if it really preserved the least-significant digits then the hundredths values I had would have appeared as a number of nanoseconds and I might have immediately guessed at the problem - instead it seems values < 100 nanoseconds are lost anyway (and perhaps bigger - I haven't probed the cut-off point).

Thanks to anyone who had a look at the question or tried some research / investigation.