问题描述
我正在尝试确定用于调用SQL Server(2005)存储过程的适当ADO Command Parameter数据类型。我特别首先尝试确定与 varchar(MAX)
的SQL Server数据类型相对应的适当ADO数据类型。我认为可能是 adVarChar
,但我不确定。
I'm trying to determine the appropriate ADO Command Parameter data types to use for calling a SQL Server (2005) stored procedure. I was specifically first trying to determine the appropriate ADO data type that would correspond to a SQL Server data type of varchar(MAX)
. I think it might be adVarChar
, but I'm not sure.
为什么没有尺寸(例如文档中列出的每种数据类型 的字符串类型的字符数,数字类型的范围)?为何似乎找不到方便的表格来列出每种数据类型以及您可以在其中填充的最大信息量?您可能以为有人会注意到与为什么我的数据被截断?的变体有关的问题,可能有数百万个问题。 ...
Why isn't the size (e.g. number of characters for 'string' types, range for numeric types) for each of these data types listed in the documentation?! And why is it seemingly impossible to find a handy table listing each of the data types and the maximum amount of info you can stuff in each of them?! You'd think someone would notice the probably millions of questions related to variants of "Why is my data being truncated?" ...
说明 –以上信息仅是一个具体示例,说明了了解ADO数据类型(例如,选择合适的ADO数据类型来处理各种数据源的特定数据类型。
Clarification – The above info is merely a concrete example illustrating the utility of knowing the limits of the ADO data types, e.g. to choose an appropriate ADO data type to handle specific data types for various data sources.
推荐答案
特定部分
$ b可以从ADO使用$ b
varchar(MAX)
作为输入参数。在这种情况下,数据类型为
是 adLongVarChar
,最大长度为& h7FFFFFFF
,如。
Specific part
varchar(MAX)
can be used from ADO as an input parameter.
The data type in this case would be adLongVarChar
, max length is &h7FFFFFFF
, as documented here.
不过它不能用作输出参数。
也不能在返回的记录中将其作为字段类型使用(搞笑-。值
为 Empty
,因为它实际上是一个长型,但是由于ADO认为它不是长型,所以可能不会调用 GetChunk
来检索实际数据。
It cannot be used as an output parameter though.
Nor can it be consumed as a field type in a returned recordsed (funny -- .Value
is Empty
, because it's actually a long type, but GetChunk
may not be called to retrieve the actual data because ADO thinks it's not a long type).
如果您需要使用VBA / ADO使用 varchar(MAX)
作为输出参数,则必须 select
它将记录集返回给客户端,您将不得不将其转换为o text
,同时执行以下操作:
If you need to consume varchar(MAX)
as an output parameter using VBA/ADO, you will have to select
it to return a recordset to the client, and you will have to cast it to text
while doing that:
select cast(@var as text) as data;
return 0;
然后您会说 s = .Fields(0).GetChunk(。 Fields(0).ActualSize)
从打开的记录集中获取数据。
Then you would say s = .Fields(0).GetChunk(.Fields(0).ActualSize)
to get the data from the opened recordset.
ADO的重点是抽象掉不同数据源之间的差异。只要有一个支持接口的数据访问驱动程序,您(理想情况下)就可以在不打扰它的情况下与其进行交谈。
The very point of ADO is to abstract away differences between different data sources. As soon as there's a data access driver around that supports an interface, you (ideally) may talk to it without bothering what it is.
作为任何抽象,这个也。
As any abstraction, this one is also leaky.
关于哪些数据的确切知识服务器映射到哪种ADO数据类型的类型来自经验。就是这样。
The exact knowledge of what data types of what servers map to which ADO data types comes from experience. That is.
一些经验法则可能很快发展起来:
Some rules of thumb, hovewer, may be developed quite quickly:
-
通过将它们的名称与特定服务器的数据类型名称进行匹配来找出可能的ADO数据类型并不困难:
It is not difficult to figure possible ADO data types by matching their names with data type names of the particular server:
-
int-adInteger
-
datetime-adDBDate
(尽管您可能会
int - adInteger
datetime - adDBDate
(although here you might be forced into some trial and error)
某些数据类型称为BLOB(二进制大对象)。它们被设计为包含大量数据,并且通常在数据源文档中就这样显示。对于这些,相应的ADO数据类型的名称中可能包含 Long
,在ADO世界中,它的意思是 BLOB( adLongVarBinary
, adLongVarChar
, adLongVarWChar
)。
Certain data types are called BLOBs (binary large objects). They are designed to contain a huge piece of data and usually presented in the data source documentation as such. For these, a corresponding ADO data type is likely to contain Long
in its name, which, in ADO world, means "BLOB" (adLongVarBinary
, adLongVarChar
, adLongVarWChar
).
关于数据类型确切长度的任何信息都可以在数据源的文档中找到,而不是ADO文档。对于类似这样的事情:
Any information on exact length of a data type is to be found in the documentation for the data source, not the ADO documentation. For things like:
- 开发人员为此特定表中的特定列设置的最大长度(例如
varchar (10)
) - BLOB数据类型的最大理论长度(例如
varchar(max)
)
- Maximum length set by a developer for a specific column in this particular table (such as
varchar(10)
) - Maximum theoretical length of a BLOB data type (such as
varchar(max)
)
您将参考相应的数据源,而不是ADO。
you are going to consult the corresponding data source, not ADO.
这篇关于ADO数据类型的限制是什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!