当 DataTypeCompatility 打开时，如何将 12/30/1899 参数化为 SQL Server 本机客户端?

2021-10-26 00:00:00 sql-server delphi oledb ado

短版

尝试将 datetime 值 12/30/1899 传递给 SQL Server，失败，日期格式无效 - 但仅适用于本机客户端驱动程序，并且仅在 DataTypeCompatiblity 模式下.

长版

尝试在 ADO 中使用参数化查询时，针对 SQL Server:

SELECT ?

我将 datetime 值参数化为 adDBTimeStamp:

//语言不可知，隐约似C#的伪代码无效的测试它(){DateTime dt = new DateTime(3/15/2020");变体 v = DateTimeToVariant(dt);命令 cmd = 新命令()；cmd.CommandText = "选择?AS SomeDate";cmd.Parameters.Append(cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);连接 cn = GetConnection();cmd.Set_ActiveConnection(cn);cmd.Execute(out recordsAffected, EmptyParam, adExecuteNoRecords);}

当日期为 3/15/2020 时效果很好.

您创建了一个 VARIANT，带有 VType，共 7 个(VT_DATE)，以及一个 8 字节浮点值:

VARIANTInt32 vt = 7;//VT_DATE双日期 = 0;

但它在 12/30/1899 失败

如果我用一个特定的日期时间做同样的测试代码，它就会失败:

void TestIt(){DateTime dt = new DateTime(12/30/1899");变体 v = DateTimeToVariant(dt);命令 cmd = 新命令()；cmd.CommandText = "选择?AS SomeDate";cmd.Parameters.Append(cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);连接 cn = GetConnection();cmd.Set_ActiveConnection(cn);cmd.Execute(out recordsAffected, EmptyParam, adExecuteNoRecords);}

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

无效的日期格式

但并非所有 SQL Server OLEDB 提供程序都会发生这种情况

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

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 应用程序都不会理解新的常量.

为此，一个新的关键字由本机" OLE DB 驱动程序支持:

DataTypeCompatibility=80

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

<块引用>

"Provider=SQLNCLI11;数据源=螺丝刀；用户 ID=hatguy；Password=hunter2;DataTypeCompatibility=80;"

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

<头>

SQL Server 数据类型	SQLOLEDB	SQLNCLI	SQLNCLI (w/DataTypeCompatibility=80)
XML	adLongVarWChar	141 (DBTYPE_XML)	adLongVarChar
日期时间	adDBTimeStamp	adDBTimeStamp	adDBTimeStamp
datetime2	adVarWChar	adDBTimeStamp	adVarWChar
日期时间偏移	adVarWChar	146 (DBTYPE_DBTIMESTAMPOFFSET)	adVarWChar
日期	adVarWChar	adDBDate	adVarWChar
时间	adVarWChar	145 (DBTYPE_DBTIME2)	adVarWChar
UDT		132 (DBTYPE_UDT)	adVarBinary(已记录，未经测试)
varchar(max)	adLongVarChar	adLongVarChar	adLongVarChar
nvarchar(max)	adLongVarWChar	adLongVarWChar	adLongVarWChar
varbinary(max)	adLongVarBinary	adLongVarBinary	adLongVarBinary
时间戳	adBinary	adBinary	adBinary

失败了

什么时候:

尝试参数化 datetime 值
值为 12/30/1899
使用本地客户端"驱动程序时
和 DataTypeCompatilibty 已开启
驱动程序本身因值而窒息
当它的价值实际上非常好时.

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

SELECT CAST('18991230' AS datetime) 工作正常
它在原始 OLE DB 驱动程序中运行良好
它在本机" OLE DB 驱动程序中运行良好
它只是在带有 DataTypeCompatibility 的本机驱动程序中失败

显然这是 Microsoft OLE DB 驱动程序中的错误.但微软永远不会、永远、永远、永远修复这个错误，这是一个绝对的事实.>

那么如何解决这个问题?

我可以检测到这个特殊的日期时间，我可以尝试解决我们数据访问层中的这个错误.

但我需要一个可以放入VARIANT结构的值，
表示12/30/1899 12:00:00 AM
在 SQOLEDB
在 SQLNCLIxx 驱动程序
和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 控制台}{$R *.res}用途System.SysUtils,对象，ActiveX，数据库，注意，变体；函数 GetConnection(Provider: string; DataTypeCompatibility: Boolean): _Connection;无功连接字符串:字符串；开始{SQLOLEDB - Windows 的默认提供程序SQLNCLI11 - SQL Server 2008 本机客户端}connectionString := 'Provider='+Provider+';数据源=螺丝刀；用户ID=氢；密码=hunter2；';如果 DataTypeCompatibility 那么connectionString := connectionString+'DataTypeCompatibility=80';结果:= CoConnection.Create;Result.Open(connectionString, '', '', adConnectUnspecified);结尾;过程测试(提供者名称:字符串；数据类型兼容性:布尔值)；无功dt: 日期时间；v:OleVariant；cmd:_命令；cn:_连接；受影响的记录:OleVariant；s:字符串；开始dt := EncodeDate(1899, 12, 30);//12/30/1899 12:00:00 AM(在 Delphi 中也称为零)v := dt;//该变体的类型为 VT_DATE (7)cmd := CoCommand.Create;cmd.CommandText := '选择 ?AS SomeDate';cmd.Parameters.Append(cmd.CreateParameter('', adDBTimeStamp, adParamInput, 0, v));尝试cn := GetConnection(ProviderName, DataTypeCompatibility);除了在 E: 例外做开始WriteLn('Provider '+ProviderName+' 未安装: '+E.message);出口;结尾;结尾;如果 SameText(ProviderName, 'SQLOLEDB') 那么s := ''否则如果 DataTypeCompatibility thens := '(具有数据类型兼容性)'别的s := '(没有数据类型兼容性)';cmd.Set_ActiveConnection(cn);尝试cmd.Execute({out}recordsAffected, EmptyParam, adExecuteNoRecords);WriteLn('Provider '+ProviderName+s+': 成功.');除了在 E:Exception 上做开始WriteLn('Provider '+ProviderName+s+' 失败:'+E.Message);结尾;结尾;结尾;程序主要；开始CoInitialize(nil);测试('SQLOLEDB'，假)；//自 2000 年以来随 Windows 提供的 SQL Server 客户端测试('SQLNCLI'，假)；//SQL Server 2005 原生客户端测试('SQLNCLI'，真)；//SQL Server 2005 本机客户端，具有数据类型兼容性测试('SQLNCLI10'，假)；//SQL Server 2008 原生客户端测试('SQLNCLI10'，真)；//SQL Server 2008 本机客户端，具有数据类型兼容性测试('SQLNCLI11'，假)；//SQL Server 2012 原生客户端测试('SQLNCLI11'，真)；//SQL Server 2012 本机客户端，具有数据类型兼容性测试('MSOLEDBSQL'，假)；//SQL Server 2016 原生客户端测试('MSOLEDBSQL'，真)；//SQL Server 2016 本机客户端，具有数据类型兼容性结尾;开始尝试主要的;除了在 E: 例外做Writeln(E.ClassName, ': ', E.Message);结尾;WriteLn('按回车键关闭');读Ln;结尾.

虽然这不是特定于 Delphi 的问题；我正在使用德尔福.所以它被标记为Delphi.如果你抱怨我会噎死你的舌头.

<块引用>

注意:这不是 ADO.net，而是 ADO.它不是托管的 .NET Framework 类库，而是本机 Win32 COM OLE DB API.

解决方案

BrakNicku 给出了答案.

<块引用>

将参数的 NumericScale 属性设置为 1-7 范围内的任何值.

更改代码:

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

到

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

有效.

它甚至适用于 SQL Server 2000 的 SQLOLEDB 驱动程序.

不同数据类型的精度和规模

从包含不同数据类型的SQL Server返回行集，我可以问OLEDB各种T-SQL数据类型的Precision和NumericScale是什么:

SQL Server 类型 ADO 类型 Precision NumericScale DefinedSize---------------- --------------------- --------- ----————————————int adInteger (3) 10 255 4真实广告单 (4) 7 255 4货币 adCurrency (6) 19 255 8位 adBoolean (11) 255 255 2tinyint adUnsignedTinyInt (17) 3 255 1bigint adBigInt (20) 19 255 8唯一标识符 adGUID (72) 255 255 16char(35) adChar (129) 255 255 35nchar(35) adWChar (130) 255 255 35十进制(15,5)和数字(131)15 5 19日期时间 adDBTimeStamp (135) 23 3 16varchar(35) adVarChar (200) 255 255 35文本 adLongVarChar (201) 255 255 2147483647varchar(max) adLongVarChar (201) 255 255 2147483647nvarchar(35) adVarWChar (202) 255 255 35nvarchar(max) adLongVarWChar (203) 255 255 1073741823xml adLongVarWChar (203) 255 255 1073741823图像 adLongVarBinary (205) 255 255 2147483647varbinary(max) adLongVarBinary (205) 255 255 2147483647

因为 SQL Server 返回一个 datetime 字段，NumericScale 为 3；可能改变是一种美德:

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

到

参数 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) - 失败

正确答案是将所有 datetime 值参数化为字符串(例如 adVarChar) 使用 ODBC 24 小时格式":

yyyy-mm-dd hh:mm:ss.zzz
2021-03-21 18:16:22.619

Short Version

Trying to pass the datetime value 12/30/1899 to SQL Server, fails with Invalid date format - but only for the native client drivers, and only in DataTypeCompatiblity mode.

Long Version

When trying to use parameterized queries in ADO, against SQL Server:

SELECT ?

I parameterize the datetime value as an 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);
}

And that works fine when the date is 3/15/2020.

You create a VARIANT, with a VType of 7 (VT_DATE), and a value that is an 8-byte floating point value:

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

But it fails on 12/30/1899

If I do the same test code with one particular datetime, it fails:

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);
}

The ADO OLEDB provider throws an exception (i.e. before it even reaches SQL Server):

Invalid date format

But it doesn't happen with all SQL Server OLEDB providers

When debugging this issue, I realized it doesn't happen with all of the SQL Server OLEDB providers. Microsoft generally has 4 OLE DB Providers for SQL Server:

SQLOLEDB: Microsoft OLE DB Provider for SQL Server (has shipped with Windows since Windows 2000)
SQLNCLI: SQL Server Native Client (shipped with SQL Server 2005)
SQLNCLI10: SQL Server Native Client 11.0 (shipped with SQL Server 2008)
SQLNCLI11: SQL Server Native Client 12.0 (shipped with SQL Server 2012)
MSOLEDBSQL: Microsoft OLE DB Driver for SQL Server (shipped with SQL Server 2016)

When trying it with some different providers, it does work fine for some:

SQLOLEDB: Works
SQLNCLI11 (without DataTypeCompatibility): Works
SQLNCLI11 (with DataTypeCompatiility on): Fails

DataTypeCompatibility?

Yes. ActiveX Data Objects (ADO), a friendly COM wrapper around the unfriendly COM OLEDB API, doesn't understand the new date, time, xml, datetime2, datetimeoffset data types. New OLEDB data type constants were created to represents these new types. So any existing OLEDB applications wouldn't understand the new constants.

To that end, a new keyword is supported by the "native" OLE DB drivers:

DataTypeCompatibility=80

which you can add to your connection string:

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

This instructs the OLEDB driver to only return OLEDB data types that were in existance when OLEDB was first invented:

SQL Server data type	SQLOLEDB	SQLNCLI	SQLNCLI (w/DataTypeCompatibility=80)
Xml	adLongVarWChar	141 (DBTYPE_XML)	adLongVarChar
datetime	adDBTimeStamp	adDBTimeStamp	adDBTimeStamp
datetime2	adVarWChar	adDBTimeStamp	adVarWChar
datetimeoffset	adVarWChar	146 (DBTYPE_DBTIMESTAMPOFFSET)	adVarWChar
date	adVarWChar	adDBDate	adVarWChar
time	adVarWChar	145 (DBTYPE_DBTIME2)	adVarWChar
UDT		132 (DBTYPE_UDT)	adVarBinary (documented,untested)
varchar(max)	adLongVarChar	adLongVarChar	adLongVarChar
nvarchar(max)	adLongVarWChar	adLongVarWChar	adLongVarWChar
varbinary(max)	adLongVarBinary	adLongVarBinary	adLongVarBinary
timestamp	adBinary	adBinary	adBinary

And there's the failure

When:

trying to parameterize a datetime value
with a value of 12/30/1899
when using a "native client" driver
and DataTypeCompatilibty is on
the driver itself chokes on the value
when its value is, in fact perfectly fine.

There's nothing inherently wrong with trying to use a date of '12/30/1899`:

SELECT CAST('18991230' AS datetime) works fine
it works fine in the original OLE DB driver
it works fine in the "native" OLE DB drivers
it just fails in the native driver with DataTypeCompatibility on

Obviously this is a bug in Microsoft OLE DB drivers. But it's an absolute truth that Microsoft will never, ever, ever, EVER, fix the bug.

So how to work-around it?

I can detect this special datetime, and I can try to work around this bug in our data access layers.

But I need a value I can place into a VARIANT structure,
that represents 12/30/1899 12:00:00 AM
that works under SQOLEDB
and under SQLNCLIxx drivers
and under MSOLEDBSQL driver
in DataTypeCompatibilityMode
(and what the hell, even with the mode off - although it's invalid to use ADO without it on)

T-SQL generated by the driver

When the OLE DB driver does bother to actually do what i say, we can profile the RPC generated:

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 (Delphi)

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.

And while this is not a Delphi-specific question; I am using Delphi. So it's tagged as Delphi. If you complain 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.

解决方案

BrakNicku had the answer.

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

Changing the code from:

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

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

works.

It even works with SQLOLEDB driver against SQL Server 2000.

Precision and Scale of different data types

Returning rowsets from SQL Server containing different data types, i can ask OLEDB what the Precision, and NumericScale of various T-SQL data types are:

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

Since SQL Server returns a datetime field with a NumericScale of 3; there may be a virtue in changing:

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

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

Bonus Reading

Don't ever, ever, EVER try to parameterize a datetime as adDBTimestamp. There are data-loss bugs in Microsoft's SQL Server OLEDB drivers (all of them):

SQLOLEDB (1999) - Fails
SQLNCLI (2005) - Fails
SQLNCLI10 (2008) - Fails
SQLNCLI11 (2010) - Fails
MSOLEDBSQL (2012) - Fails

The correct answer is to parameterize all datetime values as a string (e.g. adVarChar) using the "ODBC 24-hour format":

yyyy-mm-dd hh:mm:ss.zzz
2021-03-21 18:16:22.619

相关文章