Thread: MSACCESS & Apostrophe in Column Name

    Here's a curious problem. We recently exported an Access table to
PostgreSQL. The table has 3 column names containing apostrophes (or
single-quotes, if you like). This seems to confuse some bit of parsing
logic somewhere in the ODBC path, whenever a dynaset query is sent from
Access that has an odd number of references to these apostrophic names.

    If we issue a query with an odd number of these references, the back
end receives something like this:

> 'SELECT "ID","Month called or rec'd form","FullName"  FROM "GHI-practice2"
>   WHERE "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ?
>   OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ?'

which, of course, it rejects.

    If we issue a query with an even number of such references, the back
end receives something like this, and is very happy:

> SELECT "ID","Month called or rec'd form","Day called or rec'd form","FullName"
>   FROM "GHI-practice2"  WHERE "ID" = 1 OR "ID" = 3 OR "ID" = 4 OR "ID" = 6
>  OR "ID" = 8 OR "ID" = 9 OR "ID" = 11 OR "ID" = 12 OR "ID" = 13 OR "ID" = 14'


    I've captured traces from the ODBC manager and from the pgsql driver,
and can forward them if you like. I haven't yet had time to try to dig
into this any deeper to determine whether the bug is in the Postgres
driver or in MS code.

    Has anyone got any idea where the problem lies? Is it a known problem
with a known workaround, or a new, um, opportunity?


Cheers,
Bob

Bob Woodside wrote:
>
>
>         Here's a curious problem. We recently exported an Access table to
> PostgreSQL. The table has 3 column names containing apostrophes (or
> single-quotes, if you like). This seems to confuse some bit of parsing
> logic somewhere in the ODBC path, whenever a dynaset query is sent from
> Access that has an odd number of references to these apostrophic names.
>
>         If we issue a query with an odd number of these references, the
back
> end receives something like this:
>
> > 'SELECT "ID","Month called or rec'd form","FullName"  FROM
"GHI-practice2"
> >   WHERE "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID"
= ?
> >   OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ?'
>

Psqlodbc driver has been unaware of double-quotes(") .
Probably it's the cause.
Recently I changed the driver to check double-quotes.
Could you try the CVS snapshot ?

regards,
Hiroshi Inoue

> which, of course, it rejects.
>
>         If we issue a query with an even number of such references, the
back
> end receives something like this, and is very happy:
>
> > SELECT "ID","Month called or rec'd form","Day called or rec'd
form","FullName"
> >   FROM "GHI-practice2"  WHERE "ID" = 1 OR "ID" = 3 OR "ID" = 4 OR "ID" =
6
> >  OR "ID" = 8 OR "ID" = 9 OR "ID" = 11 OR "ID" = 12 OR "ID" = 13 OR "ID"
= 14'
>
>         I've captured traces from the ODBC manager and from the pgsql
driver,
> and can forward them if you like. I haven't yet had time to try to dig
> into this any deeper to determine whether the bug is in the Postgres
> driver or in MS code.
>
>         Has anyone got any idea where the problem lies? Is it a known
problem
> with a known workaround, or a new, um, opportunity?
>
> Cheers,
> Bob

Hiroshi Inoue wrote:
>
> Bob Woodside wrote:
> >
> >         If we issue a query with an odd number of these references, the
> > back end receives something like this:
> >
> > > 'SELECT "ID","Month called or rec'd form","FullName"  FROM
> "GHI-practice2"
> > >   WHERE "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID"
> = ?
> > >   OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ?'
> >
>
> Psqlodbc driver has been unaware of double-quotes(") .
> Probably it's the cause.
> Recently I changed the driver to check double-quotes.
> Could you try the CVS snapshot ?

    That's definitely the cause, and thanks for your suggestion. However,
it was more convenient for my immediate needs just to take a quick look
at the source from the 7.1.2 tarball and hack in a little double-quote
patch to the in_quote stuff.

    If I have a little time during the coming week, I'll set up a CVS tree
and take a look at your latest version. I'm sure it's better than my
hasty patch - e.g., I know that mine won't catch anything fancy like an
escaped quote within a quote (you *did* check for that, right?)


Cheers,
Bob

Bob Woodside wrote:
>
> Hiroshi Inoue wrote:
> >
> > Bob Woodside wrote:
> > >
> > >         If we issue a query with an odd number of these references, the
> > > back end receives something like this:
> > >
> > > > 'SELECT "ID","Month called or rec'd form","FullName"  FROM
> > "GHI-practice2"
> > > >   WHERE "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID"
> > = ?
> > > >   OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ?'
> > >
> >
> > Psqlodbc driver has been unaware of double-quotes(") .
> > Probably it's the cause.
> > Recently I changed the driver to check double-quotes.
> > Could you try the CVS snapshot ?
>
>         That's definitely the cause, and thanks for your suggestion. However,
> it was more convenient for my immediate needs just to take a quick look
> at the source from the 7.1.2 tarball and hack in a little double-quote
> patch to the in_quote stuff.
>
>         If I have a little time during the coming week, I'll set up a CVS tree
> and take a look at your latest version. I'm sure it's better than my
> hasty patch - e.g., I know that mine won't catch anything fancy like an
> escaped quote within a quote (you *did* check for that, right?)

Oh I'm about to commit a change about *escape*.
There's a fault in my last change.
Please wait for a while.

regards,
Hiroshi Inoue