Re: Problem with updateRow() -- Revisited

Ok, that's all good to know. I've already abstraced many of the
differences between the different databases into different classes... so
I suppose I could add some case folding code there as well.

However, in my system I currently force *all* table names to always be
uppercase. So what I currently have works fine in all cases... except
for this updateRow() problem with PostgreSQL. That smells like a bug.

-----Original Message-----
From: Barry Lind [mailto:blind(at)xythos(dot)com]
Sent: Tuesday, March 25, 2003 2:29 PM
To: David Hooker
Subject: Re: [JDBC] Problem with updateRow() -- Revisited

David,

No this isn't a bug (although there may be some bugs here somewhere).

If you look at DatabaseMetaData you will see the following methods:
supportsMixedCaseIdentifiers()
storesUpperCaseIdentifiers()
storesLowerCaseIdentifiers()
storesMixedCaseIdentifiers()

These methods tell the application how the database deals with handling
case for identifiers. So for example Oracle would return true for
storesUpperCaseIdentifiers() whereas Postgres returns false. This means

that if you pass a table name to Oracle's getPrimaryKeys() method you
should upper case the value, and in Postgres you should lower case it.

This is all necessary because of how the SQL spec works. Since you can
create three tables as follows:

create table TEST;
create table "Test";
create table "TEST";

In postgres you will have three tables with the following names:

test, Test, TEST

You would need to pass the correct name (understanding the case folding
rules of postgres) in order to get information about the correct table.
So for example getPrimaryKeys("","","test") will return different
results than getPrimaryKeys("","","Test").

The first TEST because it was not quoted and got folded to lower case
(since all unquoted identifiers in postgres are folded to lower case),
and the other two are case preserved because they were quoted.

In Oracle because it folds to upper case you would see the following:

create table TEST;
create table "Test";
create table "TEST"; --This third one would error because a table by
that name already existed. However if you did:
create table "test"; -- this would work

And you would end up with:

TEST, Test, test

I hope that all makes sense.

thanks,
--Barry

David Hooker wrote:
> Haha... apparently so. I just added sql = sql.toLowerCase() before I
> send, and the problem went away again. The updateRow works in this
> case.
>
> This kinda sucks since other databases (like Oracle) work better when
> things are all uppercase. And all uppercase works for PostgreSQL in
all
> other cases except this one. Seems like a bug to me.
>
> -----Original Message-----
> From: David Hooker
> Sent: Tuesday, March 25, 2003 1:55 PM
> To: Barry Lind
> Cc: Dave Cramer; pgsql-jdbc(at)postgresql(dot)org
> Subject: Re: [JDBC] Problem with updateRow() -- Revisited
>
>
> My table names are all uppercase. Is that a problem?
>