問題描述

短版

嘗試將 datetime 值 12/30/1899 傳遞給 SQL Server，失敗，日期格式無效 - 但僅適用于本機客戶端驅(qū)動程序，并且僅在 DataTypeCompatiblity 模式下.

長版

嘗試在 ADO 中使用參數(shù)化查詢時，針對 SQL Server:

SELECT ?

我將 datetime 值參數(shù)化為 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 時效果很好.

您創(chuàng)建了一個 VARIANT，帶有 VType，共 7 個(VT_DATE)，以及一個 8 字節(jié)浮點值:

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 提供程序都會發(fā)生這種情況

在調(diào)試此問題時，我意識到并非所有 SQL Server OLEDB 提供程序都會發(fā)生這種情況.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 驅(qū)動程序(隨 SQL Server 2016 提供)

當與一些不同的提供商一起嘗試時，它確實對某些人來說效果很好:

• SQLOLEDB:有效
• SQLNCLI11(無數(shù)據(jù)類型兼容性):有效
• SQLNCLI11(啟用數(shù)據(jù)類型兼容性):失敗

數(shù)據(jù)類型兼容性?

是的.ActiveX 數(shù)據(jù)對象 (ADO)，一個圍繞不友好 COM OLEDB API 的友好 COM 包裝器，不理解新的 date、time、xml、datetime2、datetimeoffset 數(shù)據(jù)類型.創(chuàng)建了新的 OLEDB 數(shù)據(jù)類型常量來表示這些新類型.因此，任何現(xiàn)有的 OLEDB 應用程序都不會理解新的常量.

為此，一個新的關(guān)鍵字由本機" OLE DB 驅(qū)動程序支持:

• DataTypeCompatibility=80

您可以將其添加到連接字符串中:

"Provider=SQLNCLI11;數(shù)據(jù)源=螺絲刀；用戶 ID=hatguy；Password=hunter2;DataTypeCompatibility=80;"

這會指示 OLEDB 驅(qū)動程序僅返回首次發(fā)明 OLEDB 時存在的 OLEDB 數(shù)據(jù)類型:

失敗了

什么時候:

• 嘗試參數(shù)化 datetime 值
• 值為 12/30/1899
• 使用本地客戶端"驅(qū)動程序時
• 和 DataTypeCompatilibty 已開啟
• 驅(qū)動程序本身因值而窒息
• 當它的價值實際上非常好時.

嘗試使用12/30/1899"這個日期并沒有本質(zhì)上的錯誤:

• SELECT CAST('18991230' AS datetime) 工作正常
• 它在原始 OLE DB 驅(qū)動程序中運行良好
• 它在本機" OLE DB 驅(qū)動程序中運行良好
• 它只是在帶有 DataTypeCompatibility 的本機驅(qū)動程序中失敗

顯然這是 Microsoft OLE DB 驅(qū)動程序中的錯誤.但微軟永遠不會、永遠、永遠、永遠修復這個錯誤，這是一個絕對的事實.>

那么如何解決這個問題?

我可以檢測到這個特殊的日期時間，我可以嘗試解決我們數(shù)據(jù)訪問層中的這個錯誤.

• 但我需要一個可以放入VARIANT結(jié)構(gòu)的值，
• 表示12/30/1899 12:00:00 AM
• 在 SQOLEDB
下工作
• 在 SQLNCLIxx 驅(qū)動程序
下
• 和MSOLEDBSQL驅(qū)動程序
• 在DataTypeCompatibilityMode
• (這到底是怎么回事，即使關(guān)閉了模式 - 雖然在沒有打開它的情況下使用 ADO 是無效的)

驅(qū)動生成的T-SQL

當 OLE DB 驅(qū)動程序確實真正按照我說的去做時，我們可以分析生成的 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，數(shù)據(jù)庫，注意，變體；函數(shù) GetConnection(Provider: string; DataTypeCompatibility: Boolean): _Connection;無功連接字符串:字符串；開始{SQLOLEDB - Windows 的默認提供程序SQLNCLI11 - SQL Server 2008 本機客戶端}connectionString := 'Provider='+Provider+';數(shù)據(jù)源=螺絲刀；用戶ID=氫；密碼=hunter2；';如果 DataTypeCompatibility 那么connectionString := connectionString+'DataTypeCompatibility=80';結(jié)果:= CoConnection.Create;Result.Open(connectionString, '', '', adConnectUnspecified);結(jié)尾;過程測試(提供者名稱:字符串；數(shù)據(jù)類型兼容性:布爾值)；無功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);出口;結(jié)尾;結(jié)尾;如果 SameText(ProviderName, 'SQLOLEDB') 那么s := ''否則如果 DataTypeCompatibility thens := '(具有數(shù)據(jù)類型兼容性)'別的s := '(沒有數(shù)據(jù)類型兼容性)';cmd.Set_ActiveConnection(cn);嘗試cmd.Execute({out}recordsAffected, EmptyParam, adExecuteNoRecords);WriteLn('Provider '+ProviderName+s+': 成功.');除了在 E:Exception 上做開始WriteLn('Provider '+ProviderName+s+' 失敗:'+E.Message);結(jié)尾;結(jié)尾;結(jié)尾;程序主要；開始CoInitialize(nil);測試('SQLOLEDB'，假)；//自 2000 年以來隨 Windows 提供的 SQL Server 客戶端測試('SQLNCLI'，假)；//SQL Server 2005 原生客戶端測試('SQLNCLI'，真)；//SQL Server 2005 本機客戶端，具有數(shù)據(jù)類型兼容性測試('SQLNCLI10'，假)；//SQL Server 2008 原生客戶端測試('SQLNCLI10'，真)；//SQL Server 2008 本機客戶端，具有數(shù)據(jù)類型兼容性測試('SQLNCLI11'，假)；//SQL Server 2012 原生客戶端測試('SQLNCLI11'，真)；//SQL Server 2012 本機客戶端，具有數(shù)據(jù)類型兼容性測試('MSOLEDBSQL'，假)；//SQL Server 2016 原生客戶端測試('MSOLEDBSQL'，真)；//SQL Server 2016 本機客戶端，具有數(shù)據(jù)類型兼容性結(jié)尾;開始嘗試主要的;除了在 E: 例外做Writeln(E.ClassName, ': ', E.Message);結(jié)尾;WriteLn('按回車鍵關(guān)閉');讀Ln;結(jié)尾.

雖然這不是特定于 Delphi 的問題；我正在使用德爾福.所以它被標記為Delphi.如果你抱怨我會噎死你的舌頭.

注意:這不是 ADO.net，而是 ADO.它不是托管的 .NET Framework 類庫，而是本機 Win32 COM OLE DB API.

BrakNicku 給出了答案.

將參數(shù)的 NumericScale 屬性設置為 1-7 范圍內(nèi)的任何值.

更改代碼:

參數(shù) p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);

到

參數(shù) p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);p.NumericScale = 1;

有效.

它甚至適用于 SQL Server 2000 的 SQLOLEDB 驅(qū)動程序.

不同數(shù)據(jù)類型的精度和規(guī)模

從包含不同數(shù)據(jù)類型的SQL Server返回行集，我可以問OLEDB各種T-SQL數(shù)據(jù)類型的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)和數(shù)字(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；可能改變是一種美德:

參數(shù) p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);p.NumericScale = 1;

到

參數(shù) p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);p.NumericScale = 3;

獎勵閱讀

永遠不要、永遠、永遠嘗試將datetime參數(shù)化為adDBTimestamp.Microsoft 的 SQL Server OLEDB 驅(qū)動程序中存在數(shù)據(jù)丟失錯誤(所有這些錯誤):

• SQLOLEDB (1999) - 失敗
• SQLNCLI (2005) - 失敗
• SQLNCLI10 (2008) - 失敗
• SQLNCLI11 (2010) - 失敗
• MSOLEDBSQL (2012) - 失敗

正確答案是將所有 datetime 值參數(shù)化為字符串(例如 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:

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

to

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;

to

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

這篇關(guān)于當 DataTypeCompatility 打開時，如何將 12/30/1899 參數(shù)化為 SQL Server 本機客戶端?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！