FreeTDS Development Group

["Mark Gruetzner" <mgruetzner AT rw3.com>]

Here are some more details.  We are using dblib and the exact same
database.  In our experiment, we are simply changing the tds version
from 4.2 to 7.0 in the freetds.conf file, under the global section.  We
are not overriding it in any other section of the conf file.  Dave
Krewet, a fellow developer of mine reported the problem to me, and here
is what his comments are, based on what he is observing.  BTW, we are
using FreeTDS version 0.60.

"Currently with 4.2, all queries are run with quoted
identifiers off.  In 7.0, it seems that by default all queries are run
with
quoted identifiers on.  As such you would get an error if you tried to
run
the following query:

insert into tchain (name, notes)
values ("chain name","dave's notes")

The error is:

Server: Msg 128, Level 15, State 1, Line 3
The name 'chain name' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.

This is because, with quoted identifiers on, you have to quote text with
single quotes, not
double quotes."

We have lots of code written this way, using double quotes to enclose
our strings (which sometimes contain single-quotes), so we are hoping to
find a solution that would not require us to change all our code.  We
did try setting the quoted identifiers off on the database itself, but
for some reason, that didn't seem to change the behavior.  However, if
we added the string "SET QUOTED_IDENTIFIERS OFF" before our query, then
the query worked as it did in version 4.2 without having to specify the
"SET QUOTED_IDENTIFIERS OFF" string.

I like the idea you suggested with set 'setup' config option.

Thanks,
Mark

-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Lowden, James K
Sent: Monday, June 23, 2003 4:26 PM
To: 'FreeTDS Development Group'
Subject: RE: [freetds] Config or environment setting for toggling quoted
identifiers setting


> From: Mark Gruetzner [mailto:mgruetzner AT rw3.com]
> Sent: June 23, 2003 4:30 PM
> 
> Is there a configuration option I can specify in freetds.conf or as an
> environment variable whereby I can specify the equivalent of:
> 
> SET QUOTED_IDENTIFIERS OFF
> 
> When using tds version= 7.0 for connecting to MS SQL Server 7.0.

No, there's no such option.  But you might have found a bug.  Which
library
are you using?  

Microsoft's db-lib defaults to SET QUOTED_IDENTIFIER OFF; their ODBC
defaults to ON.  tsql defaults to OFF:

1> sp_dboption 'test',  'quoted identifier'
2> go
OptionName      CurrentSetting
quoted identifier       off

I find no code to affect our default in any of our libraries (meaning,
our
ODBC default setting should be ON, but if it is, I don't know how it
happens).  Our include/sybdb.h doesn't even mention DBQUOTEDIDENT.  

It may be that libtds simply reflects the database's default, rather
than
forcing it one way or the other in accordance with the docs.  It would
very
strange, though, for the same database to exhibit different defaults
depending on the protocol version.  Is that what you're seeing?  

But.... All "set QUOTED_IDENTIFIER OFF" would do is disallow quoted
identifiers, such that "select * from [my table]" would be invalid.
Just
curious, how is that better for you?  

--jkl

P. S.  Idea:  freetds.conf currently controls one default option, "text
size".  Perhaps it should support a "setup query" instead, an arbitary
string that would be sent for every new connection.  That way, Mark
could
have:

        setup = "set textsize 1024 set QUOTED_IDENTIFIER OFF"

or whatever he wanted.  



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


_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds