Skip to Content.
Sympa Menu

freetds - RE: [freetds] Error in SQLGetInfo for SQL_FILE_USAGE

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] Error in SQLGetInfo for SQL_FILE_USAGE
  • Date: Thu, 4 Dec 2003 10:05:56 -0500

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






Archive powered by MHonArc 2.6.24.

Top of Page