SQL Server ODBC driver: SQLColumns returns TYPE_NAME "text" for varchar(max)

198 views
Skip to first unread message

Mukesh

unread,
Mar 22, 2012, 3:50:09 AM3/22/12
to
Hi,

For varchar(max) columns, SQL Columns API returns TYPE_NAME as "text"
for SQL Server ODBC driver, whereas SQL Native Client ODBC driver
returns "varchar".

In this case is SQL Server ODBC Driver working as per design ?

If yes how do we differentiate between "text" and "varchar(max)" from
ODBC application prospective ?



Thanks,

Mukesh

ralph

unread,
Mar 25, 2012, 9:50:00 AM3/25/12
to
Not sure I truly understand the problem or the question - however
since I've never let "not knowing all the facts" deter me from
offering an opinion, I'll offer the following, likely useless
information which you can safely ignore. <g>

"ODBC Drivers" have always been some what varied, perhaps even lazy,
in their response to "textual data". You didn't mention the specific
version/vendor of "ODBC Driver", nor the version of SQL Server, ( or
exactly what the data type is in the database), but it doesn't matter
except to note that if you changed out any of those participants
you'll likely get a different result.

Most ODBC Drivers have a vague generic sense of "text", and often only
an ANSI view. Anything it sees that could be text is captured as text.
Databases, on the other hand, have a very well defined sense of exact
datatypes. Char, VarChar, NChar, NVArChar, LChar, ad nauseam .... and
also a well-defined encoding scheme.

When it comes to fetching data from a textual field ODBC drivers will
capture essentially a 'string' from anything the dataserver or odbc
server decides gets 'interpreted' as a string. It might set the
datatype attribute to whatever the data type the database calls that
field, but in the end, the data itself is just a "string". Different
drivers will do different things. (ie, some drivers do recognize
different 'text' datatypes, but seldom as many as the database
itself.)

This has cause all sorts of problems and surprises over the years with
different drivers and different databases. For example encoding
changes - in some cases the data in the database might be changed from
Unicode to the local ANSI code page on the server, then back to
whatever ANSI code page the client is using - thus not a true
reflection of was actually there. Some 'text' datatypes will be
changed to something that a query "byte sorts", others to normal
lexicon sorting. It may get truncated, it may get packed. Again the
point is - what you get depends on a joint-psychosis between a
specific Driver and a specific Database and how it is configured.

If in actually viewing this data you are working at an application
level, through a data library, then you can get yet another layer of
indirection (or miss-direction <smile>). For example, comparing
results in a TextBox using DAO against ADODB - even though using an
identical ODBC driver at the lowest level in a stack.

So in the midst of all this maddness what does a developer do? Well
you basically test and see what YOUR driver does with YOUR database
via YOUR data library and that is *The Rule*. And then you just live
with it, with the full understanding that if anything changes in the
stack - then the "rule" will likely change as well.

There is some light on the horizon. Most available ODBC drivers and
its later 'cousin' OLE DB providers were all designed from the point
of view of being generic or universal. To present a consistent view of
data to an application, data library, regardles of the actual
underlying data source. That has changed. Now most ODBC drivers take a
more active view of the specific database they are designed for. This
is reflected mostly in the data types they support, thus are more
accurate in intrepreting correctly the actual data. This is why you
should always use the "SQL Native Driver" whenever you can.

Whew! Hope this helped. <g>

-ralph
Reply all
Reply to author
Forward
0 new messages