pythonsql-serveradodb

ADODB unable to store DATETIME value with sub-second precision

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])

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?

Solution

  • This is a well-known bug in the SQL Server OLEDB drivers going back more than 20 years archive; which means it is never going to be fixed.

    It's also not a bug in ActiveX Data Objects (ADO); which 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:

    The only solution is rather than parameterizing your datetime as timestamp:

    you need to parameterize it an "ODBC 24-hour format" yyyy-mm-dd hh:mm:ss.zzz string:

    or with even with the ADO-specific type string type:

    What about other DBTYPE_xxx's?

    You might think that the adDate (aka DBTYPE_DATE, 7) looks promising: archive

    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'

    You also cannot use adFileTime, which also looks promising:

    Indicates a 64-bit value representing the number of 100-nanosecond intervals since January 1, 1601 (DBTYPE_FILETIME).

    Meaning it could support a resolution of 0.0000001 seconds.

    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).

    Decoding TDS to confirm our suspicions

    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.

    We can issue the batch:

    SELECT ? AS Sample

    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.

    And decode it:

    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

    The short version is that a datetime is specified on-the-wire as:

    datetime is represented in the following sequence:

    • One 4-byte signed integer that represents the number of days since January 1, > 1900. Negative numbers are allowed to represent dates since January 1, 1753.
    • One 4-byte unsigned integer that represents the number of one three-hundredths of a second (300 counts per second) elapsed since 12 AM that day.

    Which means we can read the datetime supplied by the driver as:

    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

    In other words:

    But it chose not to. Bug: Driver.

    Resources to help decoding TDS