[freetds] MSSQL gotcha

Lowden, James K LowdenJK at bernstein.com
Wed Jun 25 16:45:41 EDT 2003

> From: Andrew Pimlott [mailto:andrew at pimlott.net]
> Sent: June 25, 2003 3:11 PM
>     create table foo (bar image);
>     insert into foo values (null);
>     Cannot insert the value NULL into column 'bar', table 
> 'pimlott.dbo.foo';
>     column does not allow nulls. INSERT fails.
> The moral is that the protocol version does not just affect what
> features are available, but may affect how the database server
> interprets commands.  


Thanks for the research.  I've added your paragraph to the bottom of that
page in doc/userguide.sgml.

The problem is more even complex, and related to the recent
QUOTED_IDENTIFIER discussion.  Microsoft servers detect an "I'm an ODBC
driver" bit in the logon packet, and alter their behavior to conform with
ODBC's specified defaults.  FreeTDS sets that bit ON, and consequently we
get ODBC semantics.  (My change yesterday turns it OFF for db-lib
connections.)  You tripped over it when you moved to 7.0 because 4.2 login
packets (to our knowledge) don't include any such bit.  It's set only for
7.0 logins.  

What you bumped into is probably a subtle side effect of our ODBC pretense.
The "Create Table" reference document in Books Online contains this note:

"Note The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL
Server both default to having ANSI_NULL_DFLT_ON set to ON."

Whether or not a column defaults to NULL is also a database setting,
overridden by the ODBC behavior.  That is, ODBC drivers by default always
create tables with nullable columns, but tables created via db-lib clients
will have nullability determined by the database's setting.  Cf.

Note I haven't tested the effect of the ODBC driver bit on default
nullability yet.  I'm extrapolating from yesterday's experience and
inferring from my experience with Microsoft's products.  Even though
Microsoft says (as above) that the "driver" has ODBC defaults, we learned
yesterday that (at least for QUOTED_IDENTIFIER) it's actually the server
reacting to the driver's announced type, not any behavior in the driver

sqsh is a ct-lib client.  It's not clear what defaults are appropriate to
ct-lib.  The Principle of Least Astonishment is little help, it seems to me.
Emulate Sybase's ct-lib defaults (on a Microsoft server)?  db-lib behavior?
Or ODBC?  Someone porting a ct-lib application to Microsoft might be glad
for Sybase emulation, but there are severe limits to what we could do.
OTOH, ODBC's defaults are generally more ANSI compliant and "modern".  

If someone wants to compile a list of differences by vendor/version/library,
I'd be happy to add it to the UG.  



It would be nice to add a note about this to
> the User Guide, Choosing a TDS protocol version
> (http://www.freetds.org/userguide/x574.htm).  Perhaps something like
>     The protocol version may also affect how database servers
>     interpret commands.  For example, Microsoft SQL Server 2000 is
>     known to behave differently with versions 4.2 and 7.0.  Version
>     7.0 is recommended for compatibility with SQL Server tools.
> It would be nice if you could auto-detect this and choose a protocol
> version appropriate for the product, but I don't know if that's
> feasible.
> Andrew
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
The information contained in this transmission may contain privileged and confidential information and is intended only for the use of the person(s) named above. If you are not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender immediately by reply e-mail and destroy all copies of the original message. Please note that we do not accept account orders and/or instructions by e-mail, and therefore will not be responsible for carrying out such orders and/or instructions.

More information about the FreeTDS mailing list