Thread: Fields being renamed in internal select statements...

Fields being renamed in internal select statements...

From
Benjamin Scherrey
Date:
I'm working on a project that converts an existing Access97 database to Postgres via ODBC.
Unfortunately the Access db was not well designed and has field names with spaces in it. When I
link to a table and bring it up in a table view it lists the field names correctly - however - I get an
ODBC error and reviewing SQL.LOG reveals that it renames some of the fields in a very odd
manner in the internal SQL request. For example: "Cancer Ctr Patient" is accessed as "Cancer
'#S_C_H#' Patient" and "Direct Admit" is accessed as '#S_C_H#' Admit" (without a prefixed "
char) which results in a parse error. This strange insertion/replacment of '#S_C_H#' for part of a
field name has got me very confused. Have you ever seen this occur or know what I can do to fix
it? This only happens internally in the ODBC and is never displayed anywhere. Appreciate any
advice you can give me.

    thanx & later,

        Ben Scherrey



Re: Fields being renamed in internal select statements...

From
Jeff Eckermann
Date:
I recently did a similar exercise, and ended up
changing all of the field names to eliminate the
spaces.  Tedious, because it also required changing
the names wherever referenced by Access.  But it
solved the problem once and for all.  I thought that
worthwhile, because table names with spaces (or other
odd characters, or using reserved words as names,
or...) are just a gotcha waiting to happen, and surely
will get you sooner or later.
$0.02

--- Benjamin Scherrey <scherrey@proteus-tech.com>
wrote:
> I'm working on a project that converts an existing
> Access97 database to Postgres via ODBC.
> Unfortunately the Access db was not well designed
> and has field names with spaces in it. When I
> link to a table and bring it up in a table view it
> lists the field names correctly - however - I get an
>
> ODBC error and reviewing SQL.LOG reveals that it
> renames some of the fields in a very odd
> manner in the internal SQL request. For example:
> "Cancer Ctr Patient" is accessed as "Cancer
> '#S_C_H#' Patient" and "Direct Admit" is accessed as
> '#S_C_H#' Admit" (without a prefixed "
> char) which results in a parse error. This strange
> insertion/replacment of '#S_C_H#' for part of a
> field name has got me very confused. Have you ever
> seen this occur or know what I can do to fix
> it? This only happens internally in the ODBC and is
> never displayed anywhere. Appreciate any
> advice you can give me.
>
>     thanx & later,
>
>         Ben Scherrey
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org


__________________________________________________
Do you Yahoo!?
U2 on LAUNCH - Exclusive greatest hits videos
http://launch.yahoo.com/u2