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: "ZIGLIO Frediano" <Frediano.Ziglio AT vodafone.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 16:56:04 +0100

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

Well, ODBC return a quote identifier character for all identifiers (you
can even get the reserved identifier list). Documentation state that
SQL92 compliant DB should return "\"" as mssql do so perhaps for ODBC is
better to enable quoted identifier by default.

mssql send this query after connection using TDS 4.2 (mssql 6.5)

exec sp_server_info 500
select 501,NULL,1 where 'a'='A'
select 504,c.name,c.description,c.definition from master.dbo.syscharsets
c,master.dbo.syscharsets c1,master.dbo.sysconfigures f where
f.config=1123 and f.value=c1.id and c1.csid=c.id
set textsize 2147483647
set ansi_defaults on
set cursor_close_on_commit off
set implicit_transactions off
set quoted_identifier off

More deeply

exec sp_server_info 500

get some informations (SYS_SPROC_VERSION ??)

select 501,NULL,1 where 'a'='A'

I think it tests NULL behavior and compare behavior (case sensitive
return no row)

select 504,c.name,c.description,c.definition from master.dbo.syscharsets
c,master.dbo.syscharsets c1,master.dbo.sysconfigures f where
f.config=1123 and f.value=c1.id and c1.csid=c.id

it retrieve charset encoding... the format of definition is unknown to
me...

set textsize 2147483647
set ansi_defaults on
set cursor_close_on_commit off
set implicit_transactions off

some settings...

set quoted_identifier off

our questioned setting...

freddy77




Archive powered by MHonArc 2.6.24.

Top of Page