sql-server - 当 DataTypeCompatility 打开时，如何将 12/30/1899 参数化为 SQL Server native 客户端？

Question

精简版
试图通过 datetime值(value) 12/30/1899 到 SQL Server，失败并显示 无效的日期格式 - 但仅适用于 native 客户端驱动程序，并且仅适用于 数据类型兼容性 模式。
长版
尝试在 ADO 中使用参数化查询时，针对 SQL Server:

SELECT ?

我参数化 datetime值作为 adDBTimeStamp :

//Language agnostic, vaguely C#-like pseudo-code
void TestIt()
{
   DateTime dt = new DateTime("3/15/2020");
   VARIANT v = DateTimeToVariant(dt);

   Command cmd = new Command();
   cmd.CommandText = "SELECT ? AS SomeDate";
   cmd.Parameters.Append(cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
   
   Connection cn = GetConnection();
   cmd.Set_ActiveConnection(cn);
   cmd.Execute(out recordsAffected, EmptyParam, adExecuteNoRecords);
}

当日期为 3/15/2020 时效果很好.
您创建了一个 VARIANT ，带有 VType 7 ( VT_DATE )，以及一个 8 字节浮点值:

VARIANT
   Int32  vt = 7; //VT_DATE
   Double date = 0;

但它在 12/30/1899 失败
如果我用一个特定的日期时间执行相同的测试代码，它会失败:

void TestIt()
{
   DateTime dt = new DateTime("12/30/1899");
   VARIANT v = DateTimeToVariant(dt);

   Command cmd = new Command();
   cmd.CommandText = "SELECT ? AS SomeDate";
   cmd.Parameters.Append(cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
   
   Connection cn = GetConnection();
   cmd.Set_ActiveConnection(cn);
   cmd.Execute(out recordsAffected, EmptyParam, adExecuteNoRecords);
}

ADO OLEDB 提供程序引发异常(即在它甚至到达 SQL Server 之前):

Invalid date format

但并非所有 SQL Server OLEDB 提供程序都会发生这种情况
在调试此问题时，我意识到并非所有 SQL Server OLEDB 提供程序都会发生这种情况。 Microsoft 通常有 4 个用于 SQL Server 的 OLE DB 提供程序:

SQLOLEDB : Microsoft OLE DB Provider for SQL Server(从 Windows 2000 开始随 Windows 一起提供)

SQLNCLI : SQL Server Native Client(随 SQL Server 2005 一起提供)

SQLNCLI10 : SQL Server Native Client 11.0(随 SQL Server 2008 一起提供)

SQLNCLI11 :SQL Server Native Client 12.0(SQL Server 2012 附带)

MSOLEDBSQL :用于 SQL Server 的 Microsoft OLE DB 驱动程序(随 SQL Server 2016 提供)

当与一些不同的提供商一起尝试时，它 对某些人来说工作正常:

SQLOLEDB :作品

SQLNCLI11 (无数据类型兼容性):有效

SQLNCLI11 (启用数据类型兼容性):失败

数据类型兼容性？
是的。 ActiveX 数据对象 (ADO)，一个围绕不友好 COM OLEDB API 的友好 COM 包装器，不理解新的 date , time , xml , datetime2 , datetimeoffset数据类型。创建了新的 OLEDB 数据类型常量来表示这些新类型。所以任何现有的 OLEDB 应用程序都不会理解新的常量。
为此，一个新的 keyword is supported by the "native" OLE DB drivers:

DataTypeCompatibility=80

您可以将其添加到连接字符串中:

"Provider=SQLNCLI11; Data Source=screwdriver; User ID=hatguy; Password=hunter2;DataTypeCompatibility=80;"

这指示 OLEDB 驱动程序仅返回首次发明 OLEDB 时存在的 OLEDB 数据类型:


SQL Server 数据类型
SQLOLEDB
SQLNCLI
SQLNCLI(w/DataTypeCompatibility=80)


xml
adLongVarWChar
141 (DBTYPE_XML)
adLongVarChar

约会时间
添加数据库时间戳
添加数据库时间戳
添加数据库时间戳

日期时间2
adVarWChar
添加数据库时间戳
adVarWChar

日期时间偏移
adVarWChar
146 (DBTYPE_DBTIMESTAMPOFFSET)
adVarWChar

日期
adVarWChar
数据库日期
adVarWChar

时间
adVarWChar
145 (DBTYPE_DBTIME2)
adVarWChar

UDT

132 (DBTYPE_UDT)
adVarBinary(已记录，未经测试)

变量(最大)
adLongVarChar
adLongVarChar
adLongVarChar

nvarchar(最大)
adLongVarWChar
adLongVarWChar
adLongVarWChar

varbinary(max)
adLongVarBinary
adLongVarBinary
adLongVarBinary

时间戳
二进制
二进制
二进制

这就是失败
什么时候:

试图参数化 datetime值(value)

值为 12/30/1899

使用“本地客户端”驱动程序时

和 DataTypeCompatilibty在

驱动程序本身在值

上窒息

当它的值(value)实际上非常好时。

尝试使用“12/30/1899”这个日期并没有本质上的错误:

SELECT CAST('18991230' AS datetime)工作正常

它在原始 OLE DB 驱动程序中工作正常

它在“ native ”OLE DB 驱动程序中工作正常

它只是在本地驱动程序中失败了 DataTypeCompatibility在

显然，这是 Microsoft OLE DB 驱动程序中的一个错误。但这是绝对的事实，Microsoft 永远不会， 曾经 , 曾经 ，修复bug。
那么如何解决呢？
我可以检测到这个特殊的日期时间，我可以尝试在我们的数据访问层中解决这个错误。

但我需要一个可以放入 VARIANT 的值结构,

表示 12/30/1899 12:00:00 AM

在 SQOLEDB 下工作

及以下 SQLNCLI xx 司机

及以下 MSOLEDBSQL司机

在 DataTypeCompatibilityMode

(这到底是怎么回事，即使关闭了模式 - 尽管在没有打开它的情况下使用 ADO 是无效的)

驱动程序生成的 T-SQL
当 OLE DB 驱动程序确实按照我说的去做时，我们可以分析生成的 RPC:
SQOLEDB

exec sp_executesql N'SELECT @P1 AS SomeDate',N'@P1 datetime','1899-12-30 00:00:00'

SQLNCLI11

exec sp_executesql N'SELECT @P1 AS SomeDate',N'@P1 datetime2(0)','1899-12-30 00:00:00'

CMRE(德尔福)

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  ComObj,
  ActiveX,
  ADOdb,
  ADOint,
  Variants;

function GetConnection(Provider: string; DataTypeCompatibility: Boolean): _Connection;
var
    connectionString: string;
begin
{
    SQLOLEDB - Default provider with Windows
    SQLNCLI11 - SQL Server 2008 native client
}
    connectionString := 'Provider='+Provider+'; Data Source=screwdriver;User ID=hydrogen;Password=hunter2;';
    if DataTypeCompatibility then
        connectionString := connectionString+'DataTypeCompatibility=80';

    Result := CoConnection.Create;
    Result.Open(connectionString, '', '', adConnectUnspecified);
end;

procedure Test(ProviderName: string; DataTypeCompatibility: Boolean);
var
    dt: TDateTime;
    v: OleVariant;
    cmd: _Command;
    cn: _Connection;
    recordsAffected: OleVariant;
    s: string;
begin
    dt := EncodeDate(1899, 12, 30);// 12/30/1899 12:00:00 AM (also known in Delphi as zero)
    v := dt; //the variant is of type VT_DATE (7)

    cmd := CoCommand.Create;
    cmd.CommandText := 'SELECT ? AS SomeDate';
    cmd.Parameters.Append(cmd.CreateParameter('', adDBTimeStamp, adParamInput, 0, v));

    try
        cn := GetConnection(ProviderName, DataTypeCompatibility);
    except
        on E: Exception do
            begin
                WriteLn('Provider '+ProviderName+' not installed: '+E.message);
                Exit;
            end;
    end;

    if SameText(ProviderName, 'SQLOLEDB') then
        s := ''
    else if DataTypeCompatibility then
        s := ' (with DataTypeCompatibility)'
    else
        s := ' (without DataTypeCompatibility)';

    cmd.Set_ActiveConnection(cn);
    try
        cmd.Execute({out}recordsAffected, EmptyParam, adExecuteNoRecords);
        WriteLn('Provider '+ProviderName+s+': success.');
    except
        on E:Exception do
            begin
                WriteLn('Provider '+ProviderName+s+' failed: '+E.Message);
            end;
    end;

end;

procedure Main;
begin
    CoInitialize(nil);

    Test('SQLOLEDB', False);        //SQL Server client that ships with Windows since 2000

    Test('SQLNCLI', False);     //SQL Server 2005 native client
    Test('SQLNCLI', True);      //SQL Server 2005 native client, w/ DataTypeCompatibilty

    Test('SQLNCLI10', False);   //SQL Server 2008 native client
    Test('SQLNCLI10', True);    //SQL Server 2008 native client, w/ DataTypeCompatibilty

    Test('SQLNCLI11', False);   //SQL Server 2012 native client
    Test('SQLNCLI11', True);    //SQL Server 2012 native client, w/ DataTypeCompatibilty

    Test('MSOLEDBSQL', False);  //SQL Server 2016 native client
    Test('MSOLEDBSQL', True);   //SQL Server 2016 native client, w/ DataTypeCompatibilty
end;


begin
  try
    Main;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
    WriteLn('Press enter to close');
    ReadLn;
end.

虽然这不是特定于 Delphi 的问题；我正在使用德尔福。所以它被标记为Delphi。如果您投诉 I'm going to choke your tongue out.

Note: This is not ADO.net, it is ADO. It is not managed .NET Framework Class Library, it is the native Win32 COM OLE DB API.

Answer 1

BrakNicku 给出了答案。

Set NumericScale property of your parameter to anything in 1-7 range.

更改代码:

Parameter p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);

到

Parameter p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
p.NumericScale = 1;

作品。
它甚至适用于 SQL Server 2000 的 SQLOLEDB 驱动程序。
不同数据类型的精度和规模
从包含不同数据类型的 SQL Server 返回行集，我可以问 OLEDB 什么 Precision , 和 NumericScale各种 T-SQL 数据类型包括:

SQL Server type   ADO type               Precision  NumericScale  DefinedSize
----------------  ---------------------  ---------  ------------  -----------
int               adInteger (3)          10         255           4
real              adSingle (4)           7          255           4
money             adCurrency (6)         19         255           8
bit               adBoolean (11)         255        255           2
tinyint           adUnsignedTinyInt (17) 3          255           1
bigint            adBigInt (20)          19         255           8
uniqueidentifier  adGUID (72)            255        255           16
char(35)          adChar (129)           255        255           35
nchar(35)         adWChar (130)          255        255           35
decimal(15,5)     adNumeric (131)        15         5             19
datetime          adDBTimeStamp (135)    23         3             16
varchar(35)       adVarChar (200)        255        255           35
text              adLongVarChar (201)    255        255           2147483647
varchar(max)      adLongVarChar (201)    255        255           2147483647
nvarchar(35)      adVarWChar (202)       255        255           35
nvarchar(max)     adLongVarWChar (203)   255        255           1073741823
xml               adLongVarWChar (203)   255        255           1073741823
image             adLongVarBinary (205)  255        255           2147483647
varbinary(max)    adLongVarBinary (205)  255        255           2147483647

由于 SQL Server 返回 datetime带有 NumericScale 的字段的 3 ;那里 5 月 成为改变的美德:

Parameter p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
p.NumericScale = 1;

到

Parameter p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
p.NumericScale = 3;

奖励阅读
永远，永远， 曾经尝试参数化 datetime如 adDBTimestamp . Microsoft 的 SQL Server OLEDB 驱动程序中存在数据丢失错误(所有这些错误):

SQLOLEDB (1999) - 失败

SQLNCLI (2005) - 失败

SQLNCLI10 (2008) - 失败

SQLNCLI11 (2010) - 失败

MSOLEDBSQL (2012) - 失败

correct answer是参数化所有 datetime值作为字符串(例如 adVarChar )使用“ODBC 24 小时格式”:

yyyy-mm-dd hh:mm:ss.zzz

2021-03-21 18:16:22.619