FreeTDS Development Group

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

> From: ZIGLIO Frediano [mailto:Frediano.Ziglio AT vodafone.com]
> Sent: December 4, 2003 8:18 AM
> > Martin Spott <Martin.Spott AT uni-duisburg.de> wrote:
> > 
> > > SELECT * FROM "Orgdaten"."dbo"."ZEITERF" WHERE  0 = 1
> > 
> > For obvious reasons the server rejects this query:
> 
> No, this is not obvious... mssql accept this syntax!
> Perhaps we must use "SET QUOTED_IDENTIFIER ON" at beginning...

Background:

SET QUOTED_IDENTIFIER ON causes the server to treat anything in quotes as an
identifier.  Traditionally in SQL Server (both vendors)

        SELECT "Hello"

was OK, likewise:

        SELECT 'Hello'

The ANSI standard dictates that SQL strings use single-quotes, and provide
for quoting identifiers (so that they may contain spaces, for example).
With SET QUOTED_IDENTIFIER ON the following:

        SELECT "Greeting" from "Formal Salutations"

selects a column from a table.  With SET QUOTED_IDENTIFIER OFF, the above
would not compile, because "Formal Salutations" would be treated as a
string.  

End background.  

Microsoft's server implicitly sets SET QUOTED_IDENTIFIER ON for connections
that identify themselves as coming from an ODBC driver.  The query:

        SELECT * FROM "Orgdaten"."dbo"."ZEITERF" WHERE  0 = 1

works on a Microsoft server, because "Orgdaten", "dbo", and "ZEITERF" are
all identifiers.  The username in particular often needs quoting, because
domain logins contain a backslash.  (Here, it's "dbo", but database objects
can be owned by any user.)

> >From Sybase site
> 
> Both Adaptive Server Enterprise and Adaptive Server IQ provide a
> quoted_identifier option that allows the interpretation of delimited
> strings to be changed. By default, the quoted_identifier option is set
> to OFF in Adaptive Server Enterprise, and to ON in Adaptive Server IQ.

I think it's inadvisable to force the server into one mode or another.  It's
better to conform to its defaults, if possible, because that's what the user
will expect.  

The most general solution, ISTM, is to quote only what needs to be quoted.
A function quote_if_required() could scan the string for characters (or
names) that require quoting, and DTRT.  If quoting is needed and not ON,
that's something the user should rectify, IMHO.  

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