Re: ODBC driver manager mangles delimited column names

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Robert Sundström (robert_at_nnoossppaamm.mimer.se)
Date: 06/10/04

  • Next message: Dave: "Re: ODBC failure - strange behavior" 
    Date: Thu, 10 Jun 2004 22:36:33 +0200

    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
  • Next message: Dave: "Re: ODBC failure - strange behavior"

    Relevant Pages

    • Re: mailmerge and sql
      ... You stated "You may also be able to create your query/view ... using an ADO recordset as a datasource (if it could be ... Query imposes but the ... try to make your SQL as short as possible. ...
      (microsoft.public.word.mailmerge.fields)
    • Re: Mail merge not working properly
      ... I can't figure out how to use this query as the datasource. ... > Doug Robbins - Word MVP ... the SQL when applied includes records ...
      (microsoft.public.word.mailmerge.fields)
    • 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)
    • Re: ODBC driver manager mangles delimited column names
      ... 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. ... This is what I get in the log for the automatically generated query: ...
      (microsoft.public.data.odbc)
    • 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)