Oracle OCCI stmt.setTimestamp 插入 TIMESTAMP(6)：微秒始终为 0

发布于 2024-11-23 21:49:07 字数 1597 浏览 1 评论 0原文

更新：Timestamp 构造函数的“秒的一小部分”参数实际上需要纳秒......我猜它是百分之一秒，我的低值被四舍五入。留下问题供参考....

我正在努力使用 Oracle 的 C++ 库 - OCCI。简而言之：

使用 stmt.setTimestamp创建 Timestamp 对象并验证它们在百分之一秒内是否良好（尽管我想要更多！）
，然后使用 executeUpdate() 插入到 TIMESTAMP(6) 列中，该列应保留微秒
选择 Oracle SQL Developer 中的行：亚秒部分始终为 0-ed，例如2011 年 7 月 14 日 06.03.27.000000000。

问题

我需要亚秒级精度 - 希望微秒！我们投入了大量的工作来捕获服务器中的精度，并且需要（至少部分）它进行分析。

详细信息

我从年/月/日时/分/秒/毫秒创建一个 Timestamp ，将最后一个秒减少到百分之一秒，因为这似乎是构造函数支持的。（我找不到任何 Oracle 文档指定解释，但在 fromText 示例中，“xff”明显对应于要转换的值中的百分之一后缀“.##”。这有什么意义TIMESTAMP(6) 支持 6 位小数，如果您无法插入它们？）

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;

这里，fs 按预期获取毫秒/10 值。

我将其用作：

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

其中字段 11 是 TIMESTAMP(6)。

在 Oracle SQL Developer 中选择该行，时间戳列的其他部分是正确的，但亚秒部分是 0-ed ala 14-JUL-11 06.03.27.000000000。

任何见解非常感谢！

（如果相关，使用 MSVC++ 2005、Oracle 10.2.0.4 sdk、SQL Developer 3.0.04 - 请询问是否还有其他相关的内容）。

谢谢，托尼

原文

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

分享到QQ

分享到微博