Skip to Content.
Sympa Menu

freetds - RE: [freetds] MSSQL gotcha

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Lowden, James K" <LowdenJK AT bernstein.com>
  • To: 'FreeTDS Development Group' <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] MSSQL gotcha
  • Date: Wed, 25 Jun 2003 16:45:41 -0400

> 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.

Andrew,

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.
sp_dboption.

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
itself.

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.

Regards,

--jkl

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.






Archive powered by MHonArc 2.6.24.

Top of Page