sql-server - 当 DataTypeCompatility 打开时,如何将 12/30/1899 参数化为 SQL Server 本机客户端?
问题描述
精简版
尝试将datetime
值12/30/1899传递给 SQL Server,由于日期格式无效- 但仅适用于本机客户端驱动程序,并且仅在DataTypeCompatiblity模式下。
长版
尝试在 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
,其 aVType
为 7 ( VT_DATE
),以及一个 8 字节浮点值:
VARIANT
Int32 vt = 7; //VT_DATE
Double date = 0;
但它在 1899 年 12 月 30 日失败了
如果我用一个特定的日期时间执行相同的测试代码,它会失败:
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
:用于 SQL Server 的 Microsoft OLE DB 提供程序(自 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
(没有 DataTypeCompatibility):有效SQLNCLI11
(打开 DataTypeCompatiility):失败
数据类型兼容性?
是的。ActiveX 数据对象 (ADO) 是一种围绕不友好 COM OLEDB API 的友好 COM 包装器,它不理解新的date
, time
, xml
, datetime2
,datetimeoffset
数据类型。创建了新的 OLEDB 数据类型常量来表示这些新类型。所以任何现有的 OLEDB 应用程序都不会理解新的常量。
DataTypeCompatibility=80
您可以将其添加到连接字符串中:
"提供者=SQLNCLI11;数据源=螺丝刀;用户ID=hatguy;密码=hunter2;DataTypeCompatibility=80; "
这指示 OLEDB 驱动程序仅返回 OLEDB 首次发明时存在的 OLEDB 数据类型:
SQL Server 数据类型 | SQLOLEDB | SQLNCLI | SQLNCLI (w/DataTypeCompatibility=80) |
---|---|---|---|
xml | adLongVarWChar | 141 (DBTYPE_XML) | adLongVarChar |
约会时间 | adDBTimeStamp | adDBTimeStamp | adDBTimeStamp |
日期时间2 | adVarWChar | adDBTimeStamp | adVarWChar |
日期时间偏移 | adVarWChar | 146 (DBTYPE_DBTIMESTAMPOFFSET) | adVarWChar |
日期 | adVarWChar | 添加数据库日期 | adVarWChar |
时间 | adVarWChar | 145 (DBTYPE_DBTIME2) | adVarWChar |
UDT | 132 (DBTYPE_UDT) | adVarBinary(已记录,未经测试) | |
varchar(最大值) | adLongVarChar | adLongVarChar | adLongVarChar |
nvarchar(最大值) | adLongVarWChar | adLongVarWChar | adLongVarWChar |
varbinary(最大值) | adLongVarBinary | adLongVarBinary | adLongVarBinary |
时间戳 | 二进制 | 二进制 | 二进制 |
还有失败
什么时候:
- 试图参数化一个
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
- 并在
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。如果你抱怨我会掐掉你的舌头。
注意:这不是 ADO.net,它是 ADO。它不是托管的 .NET Framework 类库,它是本机 Win32 COM OLE DB API。
解决方案
布拉克尼库给出了答案。
将
NumericScale
参数的属性设置为 1-7 范围内的任何值。
从以下位置更改代码:
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 返回行集,我可以询问 OLEDBPrecision
和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 返回 a为3datetime
的字段;改变可能有好处:NumericScale
Parameter p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
p.NumericScale = 1;
至
Parameter p = cmd.CreateParameter("", adDBTimeStamp, adParamInput, 0, v);
p.NumericScale = 3;
奖金阅读
永远不要尝试将a参数datetime
化为adDBTimestamp
。Microsoft 的 SQL Server OLEDB 驱动程序(全部)中存在数据丢失错误:
- SQLOLEDB(1999) - 失败
- SQLNCLI(2005) - 失败
- SQLNCLI10(2008) - 失败
- SQLNCLI11(2010) - 失败
- MSOLEDBSQL(2012) - 失败
正确的答案是使用“ODBC 24 小时格式”将所有datetime
值参数化为字符串(例如adVarChar
):
yyyy-mm-dd hh:mm:ss.zzz
2021-03-21 18:16:22.619
推荐阅读
- android - 无法导入 android.support.v4.app.Fragment 但可以导入 FragmentManager
- javascript - 离子 JavaScript 函数在第一次加载时有效,但在第二次加载时无效
- matlab - 使用 Matlab ode45 对弹丸运动进行建模
- point-cloud-library - PCL 特征匹配失败
- rounding - python中不确定性的科学四舍五入?
- vue.js - 箭头函数不绑定数据与 axios 响应
- ruby-on-rails - Rails 使用数据库查询按月分组记录
- node.js - 当我尝试使用 JS 从外部类扩展类时,为什么会出现引用错误
- custom-model-binder - 如何在 ModelBinders 中使用 DefaultModelBindingMessageProvider
- vb.net - 使用联接时查询活页夹显示单元格只读