On Tue, 2005-02-22 at 11:15, Brian Gunning wrote:
> Hi,
>
>
>
> I am currently migrating my database from MS Access to postgreSQL or
> MySQL. I am accessingthe databse using a program written in Java. I
> have all necessary drivers installed/initialised etc. My server
> program starts and connects to the database fine. The problem occurs
> when I start my client program, which runs a SQL command to read the
> database and populate a JTree. When I migrated to MySQL no changes to
> my code/SQL commands were necessary but when the client starts using
> postgreSQL it errors out at the line accessing the DB with the
> following error…
>
>
>
> java.sql.SQLException: ERROR: column "groupid" does not exist …
>
>
>
> The column does exist but the actual name of the column is ‘groupID’
> (with last two chars. in upper case) and it is of type int4. Are there
> any case-sensitive issues I should take into account here?
>
>
>
> This line ( and all SQL commands) cause no problems when using MS
> Access and MySQL. The database was created by exporting the database
> from MS Access.
Both MySQL and PostgreSQL are behaving against the spec, with a slight
difference.
MySQL is NOT folding case (but it should be) to upper, but is keeping
the case that comes in on the query, unquoted:
select groupID from table
SHOULD become, according to the spec:
select GROUPID from TABLE.
PostgreSQL is behaving against spec by folding case (as it should) to
lower (as the spec says it shouldn't) So, for postgresql, the query
above becomes
select groupid form table.
Quoted identifiers, are not case folded (per spec):
select "groupID" from table becomes (in SQL land)
select groupID from TABLE (but in postgresql it becomes
select groupID from table.
While both databases are "getting it wrong" the easy fix would be to
just quote everything. However, MySQL quotes with back ticks, so you'd
need to quote that way for MySQL (unless they allow " quotes nowadays, I
don't know, I don't use it much anymore)
select `groupID` from table for MySQL and
select "groupID" from table for PostgreSQL.
Note that consistent quoting gets rid of this problem. I.e. either
always quote everything, or never quote anything.