将SQL Server datetime2与TADOQuery.open一起使用 [英] Using sql-server datetime2 with TADOQuery.open
问题描述
我想开始使用SQL Server中的 datetime2 字段，我需要通过Delphi XE5中的ADO将其插入表中.我正在使用 TADOQuery.ExecSQL 插入，一切正常.
I want to start using datetime2 fields in SQL Server and I need to insert into the tables via ADO from Delphi XE5. I am using TADOQuery.ExecSQL to insert and everything works fine.
但是我的大多数表都有标识列，例如
However most of my tables have identity columns, e.g.
id integer identity(1,1) not null
要保存到服务器的往返行程，我通常在查询文本中使用带有两个命令的 Open .
To save round-trips to the server, I usually use Open with two commands in the query text.
- 第一个命令是插入
- 第二个命令是选择scope_identity()作为scope_id"
所以我可以在同一往返中检索新插入的 id .
so I can retrieve the newly inserted id in the same round-trip.
这对我所有的表都有效，但是现在当我添加 datetime2 或 time 列时不起作用-我收到错误消息:
This has worked for all my tables, but not now when I add datetime2 or time column - I get the error:
项目"Foo.exe引发异常类EOleException，并显示消息从字符串转换日期和/或时间时转换失败".
Project "Foo.exe raised exception class EOleException with message 'Conversion failed when converting date and/or time from character string".
如果这很重要，我正在使用SQL Server 2008.有人知道这可能是什么问题吗?
I am using SQL Server 2008 if that matters. Does anyone know what might be the problem?
推荐答案
问题是 datetime2 不会返回到使用SQLOLEDB提供程序作为正确ADO数据类型的ADO客户端( adDBTimestamp ):
The problem is that datetime2 does not come back to ADO clients using the SQLOLEDB provider as the correct ADO data type (adDBTimestamp):
adDBTimeStamp (135)
指示日期/时间戳(yyyymmddhhmmss加上十亿分之一)(DBTYPE_DBTIMESTAMP).
Indicates a date/time stamp (yyyymmddhhmmss plus a fraction in billionths) (DBTYPE_DBTIMESTAMP).
Instead it comes back as a unicode string (adVarWChar):
adVarWChar (202)
表示以空字符结尾的Unicode字符串.
Indicates a null-terminated Unicode character string.
-
2016-11-03 12:06:01.0000000 - SQL Server本机客户端不随操作系统一起提供(您必须自己在客户端PC上安装它)
- 已删除SQL Server本机客户端
- SQLNCLI提供程序将XML列公开为不受支持的ADO DataTypeEnum值(141，DBTYPE_XML)
- SQLNCLI提供程序将TIME列公开为不受支持的ADO DataTypeEnum值(145)
- SQLNCLI提供程序将UDT列公开为不受支持的ADO DataTypeEnum值(132)
您可以尝试切换到本地" OLEDB提供程序之一(例如SQLNCLI，SQLNCLI10，SQLNCLI11).这些问题是:
You could try switching to one of the "native" OLEDB providers (e.g. SQLNCLI, SQLNCLI10, SQLNCLI11). The problem with those is that:
您可以使用 连接字符串中的DataTypeCompatibility = 80 选项导致XML列以 adLongVarWChar 的形式返回(就像在SQLOLEDB中一样)，然后以 datetime2 的形式返回， date 和 time 返回为字符串
You can use the DataTypeCompatibility=80 option in your connection string to cause XML columns to return as adLongVarWChar (as they do in SQLOLEDB), but then datetime2, date, and time return as strings
使用 DataTypeCompatibility = 80 的问题是本机客户端驱动程序中存在错误.它将SQL Server DATE 列从 adDBDate 错误地转换为 adVarWChar :
The problem with use DataTypeCompatibility=80 is that there's a bug in the native client driver. It mistakenly converts an SQL Server DATE column from adDBDate into adVarWChar:
| SQL Server data type | SQLOLEDB | SQLNCLI | SQLNCLI w/DataTypeCompatibilyt=80 |
|----------------------|-----------------|--------------------|-----------------------------------|
| Xml | adLongVarWChar | 141 (DBTYPE_XML) | adLongVarChar |
| datetime | adDBTimeStamp | adDBTimeStamp | adDBTimeStamp |
| datetime2 | adVarWChar | adDBTimeStamp | adVarWChar |
| date | adVarWChar | adDBDate | adVarWChar |
| time | adVarWChar | 145 (unknown) | 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 |
MS Connect上记录了SQL Native Client Provider 中的此错误.但是来自Microsoft的人不了解他的意思，将其关闭为无法修复.
This bug in the SQL Native Client Provider was documented on MS Connect. But the person from Microsoft, not understanding what he was told, closed it as Won't fix.
因此，如果您真的要使用ADO中的 datetime2 ，则必须将其读取为 string 并进行解析你自己.
So, if you really want to use a datetime2 from ADO, you will have to read it as a string, and parse it yourself.
这篇关于将SQL Server datetime2与TADOQuery.open一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
