Geek Answers Handbook

What are the limits for ADO data types?

I am trying to determine the appropriate data types of the ADO command parameters that will be used to invoke the SQL Server (2005) stored procedure. At first, I tried to determine the appropriate ADO data type that matched the SQL Server varchar(MAX) data type. I think it might be adVarChar , but I'm not sure.

Why not size (for example, the number of characters for 'string' types, the range for numeric types) for each of these data types listed in the documentation ?! And why, apparently, it is impossible to find a convenient table that lists all data types and the maximum amount of information that you can write in each of them ?! You think that someone will probably notice millions of questions related to the options “Why are my data truncated?” ...

Explanation . The above information is just a concrete example illustrating the usefulness of knowing the limits of ADO data types, for example. Select the appropriate ADO data type to process specific data types for various data sources.

+4
source share
1 answer

Specific part

varchar(MAX) can be used from ADO as an input parameter.
The data type in this case will be adLongVarChar , the maximum length is &h7FFFFFFF , as described here .

It cannot be used as an output parameter. It also cannot be used as a field type in returned records (funny - .Value is Empty , because it is actually a long type, but GetChunk may not be called to retrieve the actual data, since ADO considers it to be not a long type).

If you need to use varchar(MAX) as the output parameter using VBA / ADO, you will need to select return it the recordset for the client, and you will have to pass it to text , doing this

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

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

Abstract part

The point of ADO itself is to abstract the differences between different data sources. Once there is a data access driver that supports the interface, you (ideally) can talk to it without worrying about what it is.

Like any abstraction, it is also leaky .

Accurate knowledge of which data types about which servers map to ADO data types is from experience. I.e.

Some rules of thumb, hovwer, can be developed quickly enough:

  • It is easy to imagine the possible ADO data types by matching their names with the data type names of a specific server:

    • int - adInteger
    • datetime - adDBDate (although you may be involved in some trial versions and errors here)

  • Some data types are called BLOBs (binary large objects). They are designed to store a huge amount of data and are usually presented in the documentation of the data source as such. For them, the corresponding ADO data type probably contains Long in its name, which in the ADO world means "BLOB" ( adLongVarBinary , adLongVarChar , adLongVarWChar ).

  • Any information on the exact length of the data type is contained in the documentation for the data source, and not in the ADO documentation. For things like:

    • The maximum length specified by the developer for a specific column in this particular table (e.g. varchar(10) )
    • Maximum theoretical length of a BLOB data type (e.g. varchar(MAX) )

    You are about to access the appropriate data source, not ADO.

+9
source

More articles:


All Articles