Thread: postdrv ODBC driver problems

After version 0.95 of PgAccess was released (thanks so much for the
quick response!), I figured my problems with character case were
resolved.  Unfortunately, I tried to use the ODBC driver with MS Excel
97, and encountered the same problem.  Tables with mixed case field
names gave me errors, and tables with all lower case field names worked.

MS Query could see the table fine (named "Present") and reported the
names of the fields fine ("First Name" and "Last Name"), but it failed
when I actually executed the query.  It said: func_get_detail: no such
attribute or function "lastname".

This looks like the same problem I was having with PgAccess.  Am I
missing something?

I'm using version 6.40.0004 of the ODBC driver, and PostgreSQL 6.4.2.

I'm sorry I don't have the logs right now.  I can't get to the machines
I use Windows on until tomorrow.  It shouldn't be too difficult to
recreate the error if you want to look into it.  Just try to access a
table that uses mixed case field names and/or spaces.  If you still want
to see the logs, let me know; I'll try to send them tomorrow.

Thanks,
Ben

Ben Gunter wrote:

> After version 0.95 of PgAccess was released (thanks so much for the
> quick response!), I figured my problems with character case were
> resolved.  Unfortunately, I tried to use the ODBC driver with MS Excel
> 97, and encountered the same problem.  Tables with mixed case field
> names gave me errors, and tables with all lower case field names worked.
>
> MS Query could see the table fine (named "Present") and reported the
> names of the fields fine ("First Name" and "Last Name"), but it failed
> when I actually executed the query.  It said: func_get_detail: no such
> attribute or function "lastname".
>

Yes, it is a known problem with mixed case.  Some Microsoft apps don't
bother to use quotes around table/field names to preserve the case, I guess
cause they assume that every dbms works like Microsoft SQL server.  MSAccess
however does always use quotes so mixed case names are no problem with
MSAccess.

I'm not really sure how to proceed on this one.   Maybe you could just use
all lower case names for now.

It would seem to me that if an identifier (table/field name) is not quoted
in an sql statement, then Postgres should not care about the case and match
on it.  That to me seems like the correct approach.

Any opinions?

Byron

>Yes, it is a known problem with mixed case.  Some Microsoft apps don't
>bother to use quotes around table/field names to preserve the case, I guess
>cause they assume that every dbms works like Microsoft SQL server.  MSAccess
>however does always use quotes so mixed case names are no problem with
>MSAccess.
>
>I'm not really sure how to proceed on this one.   Maybe you could just use
>all lower case names for now.
>
>It would seem to me that if an identifier (table/field name) is not quoted
>in an sql statement, then Postgres should not care about the case and match
>on it.  That to me seems like the correct approach.
>
>Any opinions?
>
>Byron
I have always thought it strange that Access would allow field names with
mixed case and spaces.  I come from the Foxbase side and Visual Foxpro has
maintained the tradition of lower case, no space field names.  It
simplifies things to stick to one naming convention that works for all
databases.

Christopher R. Jones wrote:

> >Yes, it is a known problem with mixed case.  Some Microsoft apps don't
> >bother to use quotes around table/field names to preserve the case, I guess
> >cause they assume that every dbms works like Microsoft SQL server.  MSAccess
> >however does always use quotes so mixed case names are no problem with
> >MSAccess.
> >
> >I'm not really sure how to proceed on this one.   Maybe you could just use
> >all lower case names for now.
> >
> >It would seem to me that if an identifier (table/field name) is not quoted
> >in an sql statement, then Postgres should not care about the case and match
> >on it.  That to me seems like the correct approach.
> >
> >Any opinions?
> >
> >Byron
> I have always thought it strange that Access would allow field names with
> mixed case and spaces.  I come from the Foxbase side and Visual Foxpro has
> maintained the tradition of lower case, no space field names.  It
> simplifies things to stick to one naming convention that works for all
> databases.

MSAccess checks to see if the driver supports quoted identifiers.  If it does, it
allows the spaces and mixed case in field/table names.  If it doesn't, it does
not allow those things.

Byron