vba ADO 数据类型的限制是什么？

What are the limits for ADO data types?

提问by Kenny Evitt

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.

我正在尝试确定用于调用 SQL Server (2005) 存储过程的适当 ADO 命令参数数据类型。我特别首先尝试确定与 SQL Server 数据类型对应的适当 ADO 数据类型varchar(MAX)。我想可能是adVarChar，但我不确定。

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 millionsof questions related to variants of "Why is my data being truncated?" ...

为什么文档中没有列出每种数据类型的大小（例如，“字符串”类型的字符数，数字类型的范围）？！为什么似乎不可能找到一个方便的表格，列出每种数据类型以及您可以在其中填充的最大信息量？！您可能会认为有人会注意到与“为什么我的数据被截断？”的变体相关的数百万个问题。...

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.

澄清——以上信息只是一个具体的例子，说明了解 ADO 数据类型的限制的效用，例如选择合适的 ADO 数据类型来处理各种数据源的特定数据类型。

回答by GSerg

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.

varchar(MAX)可以从 ADO 用作输入参数。
在这种情况下，数据类型为adLongVarCharmax length is &h7FFFFFFF，如此处所述。

It cannot be used as an output parameter though.
Nor can it be consumed as a field type in a returned recordsed (funny -- .Valueis Empty, because it's actually a long type, but GetChunkmay not be called to retrieve the actual data because ADO thinks it's not a long type).

但它不能用作输出参数。
它也不能在返回的记录中作为字段类型使用（有趣 -.Value是Empty，因为它实际上是一个长类型，但GetChunk可能不会被调用来检索实际数据，因为 ADO 认为它不是一个长类型）。

If you need to consume varchar(MAX)as an output parameter using VBA/ADO, you will have to selectit to return a recordset to the client, and you will have to cast it to textwhile doing that:

如果您需要varchar(MAX)使用 VBA/ADO 作为输出参数使用，则必须将select记录集返回给客户端，并且text在执行此操作时必须将其转换为：

select cast(@var as text) as data;
return 0;

Then you would say s = .Fields(0).GetChunk(.Fields(0).ActualSize)to get the data from the opened recordset.

然后你会说s = .Fields(0).GetChunk(.Fields(0).ActualSize)从打开的记录集中获取数据。

---

---

Abstract part

摘要部分

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.

ADO 的真正意义在于抽象出不同数据源之间的差异。只要有一个支持接口的数据访问驱动程序，您（理想情况下）就可以与它交谈而不必担心它是什么。

As any abstraction, this one is also leaky.

与任何抽象一样，这个也是有漏的。

The exact knowledge of what data types of what servers map to which ADO data types comes from experience. That is.

哪些服务器的哪些数据类型映射到哪些 ADO 数据类型的确切知识来自经验。那是。

Some rules of thumb, hovewer, may be developed quite quickly:

然而，一些经验法则可能会很快发展起来：

• 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(although here you might be forced into some trial and error)

• 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 Longin its name, which, in ADO world, means "BLOB" (adLongVarBinary, adLongVarChar, adLongVarWChar).

• 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:

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

  you are going to consult the corresponding data source, not ADO.

• 通过将它们的名称与特定服务器的数据类型名称相匹配，不难找出可能的 ADO 数据类型：

  • int - adInteger
  • datetime - adDBDate（虽然在这里你可能会被迫进行一些试验和错误）

• 某些数据类型称为 BLOB（二进制大对象）。它们被设计为包含大量数据，通常在数据源文档中呈现。对于这些，相应的 ADO 数据类型可能包含Long在其名称中，在 ADO 世界中，它的意思是“BLOB”（adLongVarBinary, adLongVarChar, adLongVarWChar）。

• 有关数据类型确切长度的任何信息都可以在数据源的文档中找到，而不是在 ADO 文档中。对于这样的事情：

  • 开发人员为此特定表中的特定列设置的最大长度（例如varchar(10)）
  • BLOB 数据类型的最大理论长度（例如varchar(max)）

  您将要咨询相应的数据源，而不是 ADO。