FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

> From: Kelley, Peter
> Sent: Wednesday, September 08, 2004 1:39 PM
> 
> I recently built a new server (RedHat ES 3.0) upgrading Freetds from
> version 0.61.2 to version 0.62.4.  I'm using Freetds with PHP, and the
> PHP version remains the same at 4.3.4.
> 
> After upgrading, accessing MSSQL Server from PHP, the connection
> settings changed.  Specifically ansi_nulls and ansi_warnings were no
> longer set.
> 
> Checking connection settings:
> DBCC USEROPTIONS
> FROM PHP 4.3.4/Freetds 0.62.4 ansi_nulls and ansi_warnings NOT set 
> FROM PHP 4.3.4/Freetds 0.61.2 ansi_nulls and ansi_warnings set
> 
> FROM tsql, Freetds 0.62.4 ansi_nulls and ansi_warnings set
> FROM tsqsl, Freetds 0.61.2 ansi_nulls and ansi_warnings set

You did everything right.  It's an implementation change.  See the
ChangeLog entry for Tue Jun 24 16:58:35 EDT 2003.  

The 7.0 login packet contains a bit telling the server whether or not
the client is an ODBC driver.  If set, the server reacts by enabling its
ODBC default settings, among which are ansi_nulls and ansi_warnings.
db-lib connections, including PHP using the mssql extension, get
different defaults that match Sybase's original choices.  

Prior to my change, a db-lib connection (incorrectly) would set the ODBC
bit, leading to behavior inconsistent with the vendors' db-lib
implementations.  

I'm sorry this change wasn't made clear in the README.  While it's a
move in the direction of greater compatibility, it's also inconsistent
with prior versions, in a subtle way that could easily introduce errors.
It should have been highlighted.  

> Is there a way to change the connection settings in PHP or
> freetds? (I know I can set them on the server)?

I don't know.  If not, you might consider undoing the change in login.c,
bearing in mind that db-lib applications typically assume
non-ANSI-compliant NULL handling, and maybe reminding your SQL folks to
be wary of assumptions about ANSI compliance.  (If you have any fans of
ISQL.EXE -- a db-lib app -- in your shop, relying on default setttings
is apt to bite sooner or later.)  But this patch should do the trick:

--- src/tds/login.c     24 Jun 2003 21:07:14 -0000      1.101
+++ src/tds/login.c     10 Jun 2003 07:22:24 -0000      1.100
@@ -748,6 +748,9 @@
        if (domain_login)
                option_flag2 |= 0x80;   /* enable domain login security
*/

+       option_flag2 |= 0x02;   /* client is an ODBC driver
*/
+       option_flag2 |= 0x01;   /* change to initial language must
succeed          */
+
        tds_put_byte(tds, option_flag2);

        tds_put_byte(tds, sql_type_flag);

(Of course, I'm not going to undo the change in CVS.)  

Hope that helps.  

--jkl

-----------------------------------------
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.
If you, as the intended recipient of this message, the purpose of which is to 
inform and update our clients, prospects and consultants of developments 
relating to our services and products, would not like to receive further 
e-mail correspondence from the sender, please "reply" to the sender 
indicating your wishes.  In the U.S.: 1345 Avenue of the Americas, New York, 
NY 10105.