[Zope] Problems with mysql and sql methode

[Oliver Bleutgen]

> At 09:41 23/10/00, you wrote:
>>Hmm, I'm not very clear about differences between handling of (table,
>>column) names
>>in sql-databases, but postgres IS case sensitive (in some sense).

> Well I'm confused !!!

> I have two databases , one MySQL (3.23.25) one PostgreSQL (7.01), both
> with
> the same schema. Both have a table 'artist'. The following SQL Method
> (Zope
> 2.2.1) works with both

> select a.artist_id,a.known_name,a.initials
>>from artist a
> where <dtml-sqltest surname type=string>

> Change this to

> select a.artist_id,a.known_name,a.initials
>>from ARTIST a
> where <dtml-sqltest surname type=string>

> it still works with PostgreSQL but with MySQL I get

> "Table 'tunedb.ARTIST' doesn't exist"

> Change this to

> select a.artist_id,a.known_name,a.initials
>>from ARTIST a
> where <dtml-sqltest SURNAME type=string>

> Then it still works with PostgreSQL, provided you also change the argument
> to the SQL Method from surname to SURNAME.

> When you click on the Test tab of an SQL Method it shows the SQL generated
> and you can see, in the above example, that the uppercase words are passed
> through unchanged to the database

> viz.

> select a.artist_id,a.known_name,a.initials from ARTIST a where SURNAME =
> 'Molloy'

> In what circumstances is PostgreSQL case-sensitive ?

First, sorry for my (nearly) full-quote, but I think it makes this discussion
more readable.
To your question, from my testing (I did this with column names only, but I 
suspect the same behavior for table-names), postgres is case 
sensitive, but - as I wrote - in another sense.
If you don't quote the column-names, they are converted to or interpreted as
lowercased names. _But_ if the real name of the table/column is _uppercase_, 
it will not be matched by this lowercased name - in this sense postgres 
is case-sensitive.
Postgres needs quoted names in sql-queries to respect capitalization (sp?) -
and I couldn't <dtml-sqltest ...> get to generate them.
This was a major problem for me, because I had to work with a bunch of tables
which were exported from foxpro and dbase, and their names were uppercased
in the process.

cheers,
oliver