Re: [Zope] Problems with mysql and sql methode
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). 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 - [EMAIL PROTECTED] 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 )
Re: [Zope] Problems with mysql and sql methode
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 - [EMAIL PROTECTED] 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 [EMAIL PROTECTED] ___ Zope maillist - [EMAIL PROTECTED] 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 )
Re: [Zope] Problems with mysql and sql methode
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 ___ Zope maillist - [EMAIL PROTECTED] 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 )
Re: [Zope] Problems with mysql and sql methode
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 ? Richard At 21:52 20/10/00, you wrote: Ok know I get this problem Error, _mysql.OperationalError: (1109, "Unknown table 'MERCHANTPAYMENT' in where clause") SQL used: select PYNAME, PYPRICE from PAYMENT where MERCHANTPAYMENT.MEID = '418' and MERCHANTPAYMENT.PYID = PYID other ideas? Thanks Jens Grewen - Original Message - From: "Farrell, Troy" [EMAIL PROTECTED] To: "'Jens Grewen'" [EMAIL PROTECTED]; "Zope Maillist" [EMAIL PROTECTED] Sent: Friday, October 20, 2000 10:04 PM Subject: RE: [Zope] Problems with mysql and sql methode You may try: SELECT pyname, pyprice FROM payment WHERE merchantpayment.meid = 418 AND merchantpayment.pyid = payment.pyid I have a feeling mysql is not liking you for not selecting results from table MERCHANTPAYMENT becuase you said you would. Troy -Original Message- From: Jens Grewen [mailto:[EMAIL PROTECTED]] Sent: Friday, October 20, 2000 11:04 AM To: Zope Maillist Subject: [Zope] Problems with mysql and sql methode Hi, I have a problem with an SQL Methode with the this sql statement select PAYMENT.PYNAME, PAYMENT.PYPRICE from MERCHANTPAYMENT, PAYMENT where MERCHANTPAYMENT.MEID = 418 and MERCHANTPAYMENT.PYID = PAYMENT.PYID on the following tables: MERCHANTPAYMENT - MPID int(30) MEID int(30) PYID int(30) - PAYMENT - PYID int(30) PYNAME varchar(50) PYPRICE varchar(50) - the statement runs under MS ACCESS (link over myODBC) and I get the correct resut but under zope sql methode I get no result. (There was no data matching) Any ideas Jens Grewen ___ Zope maillist - [EMAIL PROTECTED] 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 ) ___ Zope maillist - [EMAIL PROTECTED] 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 [EMAIL PROTECTED] ___ Zope maillist - [EMAIL PROTECTED] 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 )
[Zope] Problems with mysql and sql methode
Hi, I have a problem with an SQL Methode with the this sql statement select PAYMENT.PYNAME, PAYMENT.PYPRICEfrom MERCHANTPAYMENT, PAYMENTwhere MERCHANTPAYMENT.MEID = 418 and MERCHANTPAYMENT.PYID = PAYMENT.PYID on the following tables: MERCHANTPAYMENT - MPID int(30) MEID int(30) PYID int(30) - PAYMENT - PYID int(30) PYNAME varchar(50) PYPRICE varchar(50) - the statement runs under MS ACCESS (link over myODBC) and I get the correct resut but under zope sql methode I get no result. (There was no data matching) Any ideas Jens Grewen
RE: [Zope] Problems with mysql and sql methode
You may try: SELECT pyname, pyprice FROM payment WHERE merchantpayment.meid = 418 AND merchantpayment.pyid = payment.pyid I have a feeling mysql is not liking you for not selecting results from table MERCHANTPAYMENT becuase you said you would. Troy -Original Message- From: Jens Grewen [mailto:[EMAIL PROTECTED]] Sent: Friday, October 20, 2000 11:04 AM To: Zope Maillist Subject: [Zope] Problems with mysql and sql methode Hi, I have a problem with an SQL Methode with the this sql statement select PAYMENT.PYNAME, PAYMENT.PYPRICE from MERCHANTPAYMENT, PAYMENT where MERCHANTPAYMENT.MEID = 418 and MERCHANTPAYMENT.PYID = PAYMENT.PYID on the following tables: MERCHANTPAYMENT - MPID int(30) MEID int(30) PYID int(30) - PAYMENT - PYID int(30) PYNAME varchar(50) PYPRICE varchar(50) - the statement runs under MS ACCESS (link over myODBC) and I get the correct resut but under zope sql methode I get no result. (There was no data matching) Any ideas Jens Grewen ___ Zope maillist - [EMAIL PROTECTED] 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 )
Re: [Zope] Problems with mysql and sql methode
Ok know I get this problem Error, _mysql.OperationalError: (1109, "Unknown table 'MERCHANTPAYMENT' in where clause") SQL used: select PYNAME, PYPRICE from PAYMENT where MERCHANTPAYMENT.MEID = '418' and MERCHANTPAYMENT.PYID = PYID other ideas? Thanks Jens Grewen - Original Message - From: "Farrell, Troy" [EMAIL PROTECTED] To: "'Jens Grewen'" [EMAIL PROTECTED]; "Zope Maillist" [EMAIL PROTECTED] Sent: Friday, October 20, 2000 10:04 PM Subject: RE: [Zope] Problems with mysql and sql methode You may try: SELECT pyname, pyprice FROM payment WHERE merchantpayment.meid = 418 AND merchantpayment.pyid = payment.pyid I have a feeling mysql is not liking you for not selecting results from table MERCHANTPAYMENT becuase you said you would. Troy -Original Message- From: Jens Grewen [mailto:[EMAIL PROTECTED]] Sent: Friday, October 20, 2000 11:04 AM To: Zope Maillist Subject: [Zope] Problems with mysql and sql methode Hi, I have a problem with an SQL Methode with the this sql statement select PAYMENT.PYNAME, PAYMENT.PYPRICE from MERCHANTPAYMENT, PAYMENT where MERCHANTPAYMENT.MEID = 418 and MERCHANTPAYMENT.PYID = PAYMENT.PYID on the following tables: MERCHANTPAYMENT - MPID int(30) MEID int(30) PYID int(30) - PAYMENT - PYID int(30) PYNAME varchar(50) PYPRICE varchar(50) - the statement runs under MS ACCESS (link over myODBC) and I get the correct resut but under zope sql methode I get no result. (There was no data matching) Any ideas Jens Grewen ___ Zope maillist - [EMAIL PROTECTED] 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 ) ___ Zope maillist - [EMAIL PROTECTED] 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 )
[Zope] Problems with mysql and sql methode
Hi, I have a problem with an SQL Methode with the this sql statement select PAYMENT.PYNAME, PAYMENT.PYPRICEfrom MERCHANTPAYMENT, PAYMENTwhere MERCHANTPAYMENT.MEID = 418 and MERCHANTPAYMENT.PYID = PAYMENT.PYID on the following tables: MERCHANTPAYMENT - MPID int(30) MEID int(30) PYID int(30) - PAYMENT - PYID int(30) PYNAME varchar(50) PYPRICE varchar(50) - the statement runs under MS ACCESS (link over myODBC) and I get the correct resut but under zope sql methode I get no result. (There was no data matching) Any ideas Jens Grewen
