Re: ODBC driver manager mangles delimited column names

From: Sheila (Sheila_at_discussions.microsoft.com)
Date: 06/10/04 

Date: Thu, 10 Jun 2004 14:17:01 -0700

Thanks Robert, I'm beginning to think you are right. I've just finished running some more tests with the trace logging turned on for the ODBC driver manager. It seems to show that MSQuery is responsible for stripping off my double-quotes.

This is what I get in the log for the automatically generated query:

msqry32 2d0-7b0 ENTER SQLPrepare
                HSTMT 00881E70
                UCHAR * 0x0019DD68 [ 69] "SELECT analog.name, analog."flag.msgtxt"\ d\ aFROM RTDB.DBO.analog analog"
                SDWORD 69

See how nicely my column name is delimited? Naturally this query executes quite properly. My tests also showed that if I bypassed MSQuery altogether and edited the Saved Query file directly (it's readable by Notepad), there was no problem adding another delimited column name, and it ran just fine too.

But... this is what MSQuery has mangled it to after I use it manually edited the query and add an analog."flag.offscan" column:

msqry32 2d0-7b0 ENTER SQLPrepare
                HSTMT 00881E70
                UCHAR * 0x001A0C70 [ 88] "SELECT analog.name, analog.flag.msgtxt, analog.flag.offscan\ d\ aFROM RTDB.DBO.analog analog"
                SDWORD 88

I haven't done that much with MSQuery, but I get the distinct impression from the user comments I've read that it's pretty brain-dead. And I don't suppose my chances of getting Microsoft to fix it are that great... so, bleah. A pox on all of it.

Thanks again for your time.

Sheila

"Robert Sundström" wrote:

> On Thu, 10 Jun 2004 10:09:01 -0700, Sheila
> <Sheila@discussions.microsoft.com> wrote:
> > I've created a table in a datasource that has a column name with an
> > embedded dot in it, so I know that I need to delimit the column name
> > when trying to query the table.
> >
> > When I used Excel and MS Query to automatically construct a query of the
> > table, it appears to construct the query correctly, and adds double
> > quotes to the field names, e.g.
> >
> > SELECT analog.ptnum, analog."flag.msgtxt"
> > FROM RTDB.DBO.analog analog
> >
> > However, if I try to edit the query manually - in any way, even deleting
> > the analog.ptnum column - it suddenly fails. When I turned debug tracing
> > on for my datasource server and odbc driver, I could see that in the
> > case of an auto-generated query, the double-quote delimiters were passed
> > on intact to my datasource server. However, if the query was edited
> > manually, then the query was stripped of the delimiters.
> >
> > Initially I thought it was a bug in the ODBC driver, but I tried the
> > same thing with a table in a MS SQL Server 2000 database, and MS Query
> > exhibited the same behaviour.
> >
> > This sounds like a ODBC driver manager problem. Is anyone out there
> > familiar with it, or are there any settings or configurations that I
> > would play with to force the driver/driver manager to leave the
> > delimiters alone?
>
> I am pretty sure your problem is related to the application you are using
> (Microsoft Query). The Driver Manager has no code to parse and make
> changes to SQL language string supplied by the application. You may rule
> this possibility out by trying to execute that query using any other
> ODBC-based query tool.
>
> Also, I know for sure that all client applications I have tried on Windows
> to access a Mimer SQL backend database through the Microsoft Driver
> Manager has no problems at all with quoted identifiers.
> --
> Robert Sundström, Mimer SQL Development
> Mimer Information Technology AB, http://www.mimer.com
> Validate your SQL statements/procedures at
> http://developer.mimer.com/parser
>

Relevant Pages

  • ODBC driver manager mangles delimited column names
    ... I've created a table in a datasource that has a column name with an embedded dot in it, so I know that I need to delimit the column name when trying to query the table. ... Initially I thought it was a bug in the ODBC driver, but I tried the same thing with a table in a MS SQL Server 2000 database, and MS Query exhibited the same behaviour. ... This sounds like a ODBC driver manager problem. ...
    (microsoft.public.data.odbc)
  • Re: MS SQL Server/ODBC package for Python
    ... Running your benchmark, I ran into a couple of interesting points. ... I changed the query to ... If adodbapi avoids this (which we'll also integrate into mxODBC 2.1), then this would explain the differences you see. ... ODBC driver doesn't provide proper size information - each ...
    (comp.lang.python)
  • AHHH M$Sql DBD unixODBC freetds
    ... Is there some secert handshake to get dbi to work with a microsoft sql 2000 ... The DBD::ODBC module needs to link with an ODBC 'Driver Manager'. ... If you do not have an ODBC Driver Manager you can try building the free iODBC Driver Manager in the iodbcsrc directory. ... Won't test Running make install ...
    (perl.beginners)
  • Re: ODBC SQLColums is very slow
    ... explored one such query involving V$ACCESS: ... While expecting the suggestion to collect fixed object statistics to ... might be other hidden problems lurking in the database. ... If the ODBC driver is the ...
    (comp.databases.oracle.server)
  • Re: Accessing a paradox table from a C# application...
    ... SELECT TOP 1 * FROM ModelSer ORDER BY Timestamp ... How much faster is the query if you strip off the PX? ... The ODBC driver that I am using is the Microsoft Paradox ...
    (comp.databases.paradox)