ADODB 无法以亚秒级精度存储 DATETIME 值 [英] ADODB unable to store DATETIME value with sub-second precision
问题描述
根据 Microsoft 文档 对于 DATETIME 列类型，该类型的值可以存储四舍五入到 0.000、0.003 或 0.007 秒增量的精度".根据他们关于数据类型的文档ADODB 使用的 adDBTimeStamp(代码 135)，ADODB 用于 DATETIME 列参数，表示日期/时间戳(yyyymmddhhmmss 加上十亿分之一的分数)".但是，当以亚秒级精度传递参数时，所有尝试(使用多个版本的 SQL Server 以及 SQLOLEDB 提供程序和较新的 SQLNCLI11 提供程序进行测试)都会失败.这是一个演示失败的重现案例:
According to the Microsoft documentation for the DATETIME column type, values of that type can store "accuracy rounded to increments of .000, .003, or .007 seconds." According to their documentation for the data types used by ADODB, the adDBTimeStamp (code 135), which ADODB uses for DATETIME column parameters, "indicates a date/time stamp (yyyymmddhhmmss plus a fraction in billionths)." However, all attempts (tested using multiple versions of SQL Server, and both the SQLOLEDB provider and the newer SQLNCLI11 provider) fail when a parameter is passed with sub-second precision. Here's a repro case demonstrating the failure:
import win32com.client
# Connect to the database
conn_string = "Provider=...." # sensitive information redacted
conn = win32com.client.Dispatch("ADODB.Connection")
conn.Open(conn_string)
# Create the temporary test table
cmd = win32com.client.Dispatch("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "CREATE TABLE #t (dt DATETIME NOT NULL)"
cmd.CommandType = 1 # adCmdText
cmd.Execute()
# Insert a row into the table (with whole second precision)
cmd = win32com.client.Dispatch("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "INSERT INTO #t VALUES (?)"
cmd.CommandType = 1 # adCmdText
params = cmd.Parameters
param = params.Item(0)
print("param type is {:d}".format(param.Type)) # 135 (adDBTimeStamp)
param.Value = "2018-01-01 12:34:56"
cmd.Execute() # this invocation succeeds
# Show the result
cmd = win32com.client.Dispatch("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "SELECT * FROM #t"
cmd.CommandType = 1 # adCmdText
rs, rowcount = cmd.Execute()
data = rs.GetRows(1)
print(data[0][0]) # displays the datetime value stored above
# Insert a second row into the table (with sub-second precision)
cmd = win32com.client.Dispatch("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "INSERT INTO #t VALUES (?)"
cmd.CommandType = 1 # adCmdText
params = cmd.Parameters
param = params.Item(0)
print("param type is {:d}".format(param.Type)) # 135 (adDBTimeStamp)
param.Value = "2018-01-01 12:34:56.003" # <- blows up here
cmd.Execute()
# Show the result
cmd = win32com.client.Dispatch("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "SELECT * FROM #t"
cmd.CommandType = 1 # adCmdText
rs, rowcount = cmd.Execute()
data = rs.GetRows(2)
print(data[0][1])
此代码在上面指示的行上引发异常，并显示错误消息应用程序对当前操作使用了错误类型的值".这是 ADODB 中的已知错误吗?如果是这样，我还没有找到任何关于它的讨论.(也许之前的讨论在 Microsoft 杀死 KB 页面时消失了.)如果与文档匹配，值的类型怎么会是错误的?
This code throws an exception on the line indicated above, with the error message "Application uses a value of the wrong type for the current operation." Is this a known bug in ADODB? If so, I haven't found any discussion of it. (Perhaps there was discussion earlier which disappeared when Microsoft killed the KB pages.) How can the value be of the wrong type if it matches the documentation?
推荐答案
这是 SQL Server OLEDB 驱动程序中的一个众所周知的错误 超过20年；这意味着它永远不会被修复.
This is a well-known bug in the SQL Server OLEDB drivers going back more than 20 years; which means it is never going to be fixed.
这也不是 ADO 中的错误.ActiveX 数据对象 (ADO) API 是底层 OLEDB API 的瘦包装器.该错误存在于 Microsoft 的 SQL Server OLEDB 驱动程序本身(所有这些)中.他们永远不会，永远，永远现在修复它；由于它们是不想维护现有代码的鸡屎，因此可能会破坏现有应用程序.
It's also not a bug in ADO. The ActiveX Data Objects (ADO) API is a thin wrapper around the underlying OLEDB API. The bug exists is in Microsoft's SQL Server OLEDB driver itself (all of them). And they will never, never, never fix it now; as they are chicken-shits that don't want to maintain existing code it might break existing applications.
所以这个错误已经延续了几十年:
So the bug has been carried forward for decades:
- SQOLEDB (1999) → SQLNCLI (2005) → SQLNCLI10 (2008) → SQLNCLI11 (2010)→ MSOLEDB (2012)
- SQOLEDB (1999) → SQLNCLI (2005) → SQLNCLI10 (2008) → SQLNCLI11 (2010) → MSOLEDB (2012)
唯一的解决方案是将您的datetime 参数化为时间戳:
The only solution is rather than parameterizing your datetime as timestamp:
adTimestamp(又名DBTYPE_DBTIMESTAMP,135)
adTimestamp(akaDBTYPE_DBTIMESTAMP,135)
您需要将其参数化为ODBC 24 小时格式" yyyy-mm-dd hh:mm:ss.zzz 字符串:
you need to parameterize it an "ODBC 24-hour format" yyyy-mm-dd hh:mm:ss.zzz string:
adChar(又名DBTYPE_STR,129):2021-03-21 17:51:22.619
adChar(akaDBTYPE_STR,129):2021-03-21 17:51:22.619
或者甚至使用特定于 ADO 的类型字符串类型:
or with even with the ADO-specific type string type:
adVarChar(200):2021-03-21 17:51:22.619
您可能认为 adDate(又名 DBTYPE_DATE、7)看起来很有希望:
You might think that the adDate (aka DBTYPE_DATE, 7) looks promising:
表示日期值 (DBTYPE_DATE).日期以双精度形式存储，其中整数部分是自 1899 年 12 月 30 日以来的天数，小数部分是一天的分数.
Indicates a date value (DBTYPE_DATE). A date is stored as a double, the whole part of which is the number of days since December 30, 1899, and the fractional part of which is the fraction of a day.
但不幸的是不是，因为它也将值参数化到服务器没有毫秒:
But unfortunately not, as it also parameterizes the value to the server without milliseconds:
exec sp_executesql N'SELECT @P1 AS Sample',N'@P1 datetime','2021-03-21 06:40:24'
你也不能使用 adFileTime，它看起来也很有前途:
You also cannot use adFileTime, which also looks promising:
表示一个 64 位值，表示自 1601 年 1 月 1 日 (DBTYPE_FILETIME) 以来 100 纳秒间隔的数量.
Indicates a 64-bit value representing the number of 100-nanosecond intervals since January 1, 1601 (DBTYPE_FILETIME).
意味着它可以支持0.0000001秒的分辨率.
Meaning it could support a resolution of 0.0000001 seconds.
不幸的是，根据 VARIANTs 的规则，您不能存储 <VARIANT 中的 code>FILETIME.由于 ADO 对所有值都使用变体，因此当它遇到变体类型 64 (VT_FILETIME) 时会抛出异常.
Unfortunately by the rules of VARIANTs, you are not allowed to store a FILETIME in a VARIANT. And since ADO uses variants for all values, it throws up when it encounters variant type 64 (VT_FILETIME).
我们可以通过解码发送到服务器的数据包来确认 SQL Server OLEDB 驱动程序没有提供具有可用精度的 datetime.
We can confirm that the SQL Server OLEDB driver is not supplying a datetime with the available precision by decoding the packet sent to the server.
我们可以发出批次:
SELECT ? AS Sample
并指定参数 1:adDBTimestamp - 3/21/2021 6:40:23.693
And specify parameter 1: adDBTimestamp - 3/21/2021 6:40:23.693
现在我们可以捕获该数据包:
Now we can capture that packet:
0000 03 01 00 7b 00 00 01 00 ff ff 0a 00 00 00 00 00 ...{............
0010 63 28 00 00 00 09 04 00 01 32 28 00 00 00 53 00 c(.......2(...S.
0020 45 00 4c 00 45 00 43 00 54 00 20 00 40 00 50 00 E.L.E.C.T. .@.P.
0030 31 00 20 00 41 00 53 00 20 00 53 00 61 00 6d 00 1. .A.S. .S.a.m.
0040 70 00 6c 00 65 00 00 00 63 18 00 00 00 09 04 00 p.l.e...c.......
0050 01 32 18 00 00 00 40 00 50 00 31 00 20 00 64 00 .2....@.P.1. .d.
0060 61 00 74 00 65 00 74 00 69 00 6d 00 65 00 00 00 a.t.e.t.i.m.e...
0070 6f 08 08 f2 ac 00 00 20 f9 6d 00 o...... .m.
并解码:
03 ; Packet type. 0x03 = 3 ==> RPC
01 ; Status
00 7b ; Length. 0x07B ==> 123 bytes
00 00 ; SPID
01 ; Packet ID
00 ; Window
ff ff ; ProcName 0xFFFF => Stored procedure number. UInt16 number to follow
0a 00 ; PROCID 0x000A ==> stored procedure ID 10 (10=sp_executesql)
00 00 ; Option flags (16 bits)
00 00 63 28 00 00 00 09 ; blah blah blah
04 00 01 32 28 00 00 00 ;
53 00 45 00 4c 00 45 00 ; \
43 00 54 00 20 00 40 00 ; |
50 00 31 00 20 00 41 00 ; |- "SELECT @P1 AS Sample"
53 00 20 00 53 00 61 00 ; |
6d 00 70 00 6c 00 65 00 ; /
00 00 63 18 00 00 00 09 ; blah blah blah
04 00 01 32 18 00 00 00 ;
40 00 50 00 31 00 20 00 ; \
64 00 61 00 74 00 65 00 ; |- "@P1 datetime"
74 00 69 00 6d 00 65 00 ; /
00 00 6f 08 08 ; blah blah blah
f2 ac 00 00 ; 0x0000ACF2 = 44,274 ==> 1/1/1900 + 44,274 days = 3/21/2021
20 f9 6d 00 ; 0x006DF920 = 7,207,200 ==> 7,207,200 / 300 seconds after midnight = 24,024.000 seconds = 6h 40m 24.000s = 6:40:24.000 AM
简短的版本是将 datetime 指定为 on-the-wire 为:
The short version is that a datetime is specified on-the-wire as:
日期时间按以下顺序表示:
- 一个 4 字节有符号整数，表示自 1 月 1 日以来的天数，>1900.允许负数表示自 1753 年 1 月 1 日以来的日期.
- 一个 4 字节无符号整数，表示从那天上午 12 点开始经过的三分之三秒(每秒 300 次计数)的数目.
这意味着我们可以读取驱动程序提供的datetime:
Which means we can read the datetime supplied by the driver as:
- 日期部分:
0x0000acf2= 44,274 = 1900 年 1 月 1 日 + 44,274 天 = 3/21/2021 - 时间部分:
0x006df920= 7,207,200 = 7,207,200/300 秒 = 6:40:24 AM
- Date portion:
0x0000acf2= 44,274 = January 1, 1900 + 44,274 days = 3/21/2021 - Time portion:
0x006df920= 7,207,200 = 7,207,200 / 300 seconds = 6:40:24 AM
所以驱动程序切断了我们日期时间的精度:
So the driver cut off the precision of our datetime:
Supplied date: 2021-03-21 06:40:23.693
Date in TDS: 2021-03-21 06:40:24
换句话说:
OLE 自动化使用 Double 来表示
datetime.
Double 的分辨率为 ~0.0000003 秒.
The Double has a resolution to ~0.0000003 seconds.
驱动程序有选项可以将时间编码到 1/300 秒:
The driver has the option to encode the time down to 1/300th of a second:
6:40:24.693 → 7,207,407 → 0x006DF9EF
但它选择不这样做.错误:驱动程序.
But it chose not to. Bug: Driver.
这篇关于ADODB 无法以亚秒级精度存储 DATETIME 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
