[Zope] Problems with mysql and sql methode

[Richard Moon]

At 09:41 23/10/00, you wrote:
> > MySQL is case-sensitive over table names, so MERCHANTPAYMENT is not the
> > same as merchantpayment.
>
> > Other databases (Postgresql, Informix) are not.
>
> > Perhaps the Access/ODBC interface hides the case-sensitivity while the
> > direct ZMySQLDA does not ?
>
>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 ?

Richard

>Few days ago I wrote to messages to this list describing my problems
>with postgres and capitals in column-names. The handling of these will
>render <dtml-sqltest ..> useless, because it doesn't quote the name of the
>vars. I that case postgres will not respect capital letter (will turn them
>lowercase) but also won't find the column which name is written uppercase.
>
>I think it would be usefull to collect the behaviour of different databases
>and try to find a solution which will work with all of them.
>The solution we have now clearly doesn't do that.
>
>cheers,
>oliver
>
>
>
>
>
>
>
>_______________________________________________
>Zope maillist  -  Zope@zope.org
>http://lists.zope.org/mailman/listinfo/zope
>**   No cross posts or HTML encoding!  **
>(Related lists -
>  http://lists.zope.org/mailman/listinfo/zope-announce
>  http://lists.zope.org/mailman/listinfo/zope-dev )


Richard Moon
richard@dcs.co.uk