Re: FreeTDS and nvarchar(MAX) in MSSQL-2005+

Chad Johnson wrote:
> > "[FreeTDS][SQL Server]Unicode data in
> > a Unicode-only collation or ntext data cannot be sent to clients using
> > DB-Library (such as ISQL) or ODBC version 3.7 or earlier.
> > SQLState=42000 ErrorCode=4004]"
> 
> When using a Perl / DBD::ODBC stack I was able to extract nvarchar /
> ntext / nvarchar(max) (in full unicode glory) with 0.82.  Insert /
> update's would not work.  The prerelease of 0.83 fixed that problem
> (i.e. 0.83 now allows you to bind character data as SQL_WVARCHAR).

To add to Chad's very helpful answer, the error message explicitly says
the *server* "cannot" send these data to "clients using DB-Library".  That
means our db-lib has to evolve away from strict compatibility with
Microsoft's implementation.  We knew that would happen sooner or later,
but not when or how.  This would seem to be a shot across the bow.  

This is how Microsoft does things: they introduce bitrot and delete
documetation until you give up and "upgrade" to the latest tooth on the
churn gear.  

(I recently toyed with creating a Win32 service using only the Win32 API. 
I remember doing it years ago by following a recipe in MSDN, which in
those days was delivered on 5 or so CDs.  Today all docs direct you to the
latest .NET side-by-side managed code CLR blather.  Really, is the
shoddiness of everything within 10 feet of Windows any surprise?) 

There's an "I'm an ODBC client" bit in the TDS 7.0 login packet. 
Heretofor it's been used for backward compatibility.  For example, db-lib
clients expect that "select 'a' + NULL" to yield 'a'.  If you turn on the
ODBC bit in the login packet, the server sets session options such that
the query yields NULL, per ANSI specification.  

For some years now, OPENROWSET queries have returned complaints to db-lib
clients stating that they need certain session settings in order to
execute the query.  The client could change the settings and continue.   

Your message is different: The server refuses to send the data.  (It says
"cannot", but "will not" is perhaps more accurate.)  Microsoft hasn't
touched their DB-Library in eight years and knows its implementation can't
deal with Unicode data.  And what other implementations are there, after
all?  So it closed the spigot.  

This presents a FreeTDS with a conundrum: Our db-lib *can* deal with
unicode data and would receive them if only the magic bit were turned on. 
However, to turn on the bit is also to "be" an ODBC driver and be treated
as one by the server, including getting magical session properties set
that otherwise wouldn't be.  

I have a feeling the answer is going to be an explosion of session-option
settings.  A very general approach would be to support entries in
freetds.conf that turn various options on or off according to the client's
need.  That would permit different settings for different servers, for we
can be sure that future releases of SQL Server will invent still more
options.  It would also permit db-lib clients to determine their degree of
"ODBC-ness" with the finest granularity.  

In fact, though, that's not the most general approach.  Better and
probably simpler would be to take a page from e.g. gdb and ssh: we could
have a "freetds.rc".  It would hold SQL to be run immediately after
connecting, before returning control to the application.  Since different
servers are different, I'm inclined to make it a directory with files
named after the server, each one containing its own SQL.  If many servers
are the same, they can all link the same inode.  If clients want different
settings for the same server, well, that situation exist now.  They could
override the defaults by choosing a different rc or simply sending the SQL
themselves.  

Thoughts and comments welcome and encouraged, as ever.  Patches even
welcomer.  

--jkl