Re: [Zope] MySQL Select Statements
On Sat, 1 Jul 2000, Dieter Maurer wrote: > I recently found out that Oracle returns column names > converted into all uppercase. I needed about 2 hours > to analyse this weird behaviour. That's actually not too weird. Solid does the same thing. It is a standard "feature" of SQL-89 that column names are case-insensitive and stored as upper case. You can force it to use mixed-case and spaces by quoting the column names with quotation marks. Of course, you have to do this when you CREATE TABLE. i.e. SELECT "weird but legal" FROM t selects the column named "weird but legal" from table t. MySQL, OTOH, is case-sensitive on column names. -- andy dustman | programmer/analyst | comstar.net, inc. telephone: 770.485.6025 / 706.549.7689 | icq: 32922760 | pgp: 0xc72f3f1d "Therefore, sweet knights, if you may doubt your strength or courage, come no further, for death awaits you all, with nasty, big, pointy teeth!" ___ 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] MySQL Select Statements
Michael Blewett writes: > Select Column1, Column2 etc > from TableA. > > Instead I must use the AS statement ie > > Select Column1 AS Column1, Column2 AS Column2 > from TableA. Zope should not be responsible for this strange behaviour. It simply executes the SQL statement and asks the result for the result column names. It then wraps each result row in an object that allows to access a column value as attribute under the respective column name. You could try: to get the list of column names your Z SQL Method "SQLMethod" returns. This may give you a hint how your database names result columns. I recently found out that Oracle returns column names converted into all uppercase. I needed about 2 hours to analyse this weird behaviour. Dieter ___ 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] MySQL Select Statements
On Fri, 30 Jun 2000, Ron Bickers wrote: > It should be as simple as the application designer actually knowing what > they're doing by knowing whether or not they're working with a table that > supports transactions or not, and just not using BEGIN/COMMIT/ROLLBACK when > they're not. Or is it too much to ask that designers know what they're > doing? :-) That's the problem: They don't get control over when BEGIN/COMMIT/ROLLBACK are used. Zope does that if you are using the Transaction Manager. And the only way to control use of the Transaction Manager is by selection of DA. That's why I think it may be necessary to have two ZMySQLDAs: One which is transactional and one which is not. However, they can both be part of the same distribution, and the transactional one can simply subclass the non-transactional one (mixing in the TM base and adding the required methods), so there is not a lot of code duplication. In addition, a lot of people are still going to be using MySQL-3.22, which does NOT support transactions, or even the BEGIN/COMMIT/ROLLBACK statements (these produce syntax errors). The only way to detect this at runtime is to look at the server's version. Anyway, more experimenting is needed. -- andy dustman | programmer/analyst | comstar.net, inc. telephone: 770.485.6025 / 706.549.7689 | icq: 32922760 | pgp: 0xc72f3f1d "Therefore, sweet knights, if you may doubt your strength or courage, come no further, for death awaits you all, with nasty, big, pointy teeth!" ___ 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] MySQL Select Statements
> -Original Message- > From: Andy Dustman [mailto:[EMAIL PROTECTED]] > Sent: Friday, June 30, 2000 11:42 AM > To: Michael Blewett > Cc: Ron Bickers; [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: RE: [Zope] MySQL Select Statements > > It's a matter of perspective. I prefer to think of it as 0.2.2 breaks > ZMySQLDA. This is because ZMySQLDA uses _mysql, and _mysql prior to 0.2.2 > implemented fetch_row(), fetch_rows(), fetch_all_rows(), and the > ..._as_dict() variants. Thanks for clarifying that. I hope the 1.2.0 DA continues to follow your (Andy's) changes to the MySQLdb so that we can have a nice, working, stable db/DA combination. > what the solution is for this. It may be necessary to have the ZMySQLDA > package include another DA: ZMySQLTDA for transactions. The standard DA Please not two DAs just for the difference in transaction support. > Obviously the application designer will have to use the right DA for the > right table type... It should be as simple as the application designer actually knowing what they're doing by knowing whether or not they're working with a table that supports transactions or not, and just not using BEGIN/COMMIT/ROLLBACK when they're not. Or is it too much to ask that designers know what they're doing? :-) ___ Ron Bickers Logic Etc, Inc. [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] MySQL Select Statements
I should point out that 0.2.2 has not been fully released yet. There's a
bug I have to iron out of it first. Probably next week. Also, for general
MySQLdb questions, send them to [EMAIL PROTECTED] (mailing list). Or
subscribe, it's low-volume and won't fatten your mailbox.
On Fri, 30 Jun 2000, Michael Blewett wrote:
> It's funny that you say 0.2.2 is broken. Apart from this one glitch, it
> works fine for me (although from memory I think we had to do a little bit
> of fiddling at install time to get it to work).
It's a matter of perspective. I prefer to think of it as 0.2.2 breaks
ZMySQLDA. This is because ZMySQLDA uses _mysql, and _mysql prior to 0.2.2
implemented fetch_row(), fetch_rows(), fetch_all_rows(), and the
..._as_dict() variants. This was just too much code duplication to put up
with, and the new interface is fetch([n[,how]]) where n is the maximum
number of rows to fetch (default 1), and how is how to return the result
(0: as tuple, 1: as dictionary, other codes may be added at some point).
To fix ZMySQLDA, change fetch_all_rows() to fetch(maxrows) in the query
routine. One other place (to get the tables) uses fetch_all_rows(). If you
insist on using 0.2.2 at this point, change that as well. 0.2.2 is marked
Alpha for a reason... And when I release it, I'll include a very small
patch to make things right with ZMySQLDA again (I'm treating mordred's as
the official one at this point).
> At 00:12 30/06/2000 -0400, Ron Bickers wrote:
> >Why do you say you "must" use the AS statement? Do you get an error
> >otherwise?
> >
> >MySQLdb 0.2.2 is broken when used with ZMySQLDA. But 0.2.1 works okay for
> >me. Andy Dustman (author of the MySQLdb) said he will fix it and hinted
> >that one should use the ZMySQLDA that is on Zope.org (v1.2.0 now I believe).
> >Since that one now uses his MySQLdb, he will likely kill off his own DA.
Quite true. Now for the AS statement: If you are joining tables with
identically-named columns in common, you should use AS in your SQL to
rename them. Otherwise it will return two columns with the same name to
Zope, which is bad. You do not, however, need to do this:
> > > Just need someone to put me straight here. When I use Select
> > > statements in
> > > an SQL Method I cannot simply :
> > >
> > > Select Column1, Column2 etc
> > > from TableA.
> > >
> > > Instead I must use the AS statement ie
> > >
> > > Select Column1 AS Column1, Column2 AS Column2
> > > from TableA.
> > >
> > > - Michael
This is just gratuitious renaming to the original name, which is not
renaming at all. Here's an example of what I'm talking about.
SELECT TableA.Col, TableB.Col from TableA, TableB # bad
SELECT TableA.Col AS ColA, TableB.Col AS ColB from TableA, TableB # good
Note that the first SELECT returned two columns named Col, which will
confuse Zope, and the second returns ColA (TableA.Col) and ColB
(TableB.Col).
Additional compatibility note: One of the 0.2.2 changes has been to
synchronize with MySQL-3.23 evolution (now at 3.23.19-beta, and GPL'd),
and the big feature here is: Transactions! Yes, as of 3.23.15,
transactions are in MySQL, if you use BDB tables. And yes, BDB does mean
Berkeley DB, specificially the Sleepycat DB v3, which supports
transactions (but probably not columns, which I presume MySQL is imposing
on the database).
For ZMySQLDA developers out there; MySQL-3.23 defaults to autocommit.
However, there is a BEGIN SQL statement which starts a transaction and
similarly for COMMIT and ROLLBACK statements. But remember: All this
transactional stuff ONLY works on BDB tables. If you try to BEGIN a
transaction (or turn off autocommit) on another table type, well, I'm not
sure what happens. Maybe you get an error, and maybe you don't. Not sure
what the solution is for this. It may be necessary to have the ZMySQLDA
package include another DA: ZMySQLTDA for transactions. The standard DA
would not be based on TM, and the transactional one could simply subclass
the other one:
# ZMySQLTDA/db.py
from Shared.DC.ZRDB.TM import TM
class DB(ZMySQLDA.DB, TM):
def _begin(self, *ignored): self.db.query("BEGIN")
def _finish(self, *ignored): self.db.query("COMMIT")
def _abort(self, *ignored): self.db.query("ROLLBACK")
Obviously the application designer will have to use the right DA for the
right table type...
--
andy dustman | programmer/analyst | comstar.net, inc.
telephone: 770.485.6025 / 706.549.7689 | icq: 32922760 | pgp: 0xc72f3f1d
"Therefore, sweet knights, if you may doubt your strength or courage,
come no further, for death awaits you all, with nasty, big, pointy teeth!"
___
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] MySQL Select Statements
> If I don't specify my Selects using "AS", I then get the following error > message: > > Error Type: KeyError > Error Value: SubjectName Traceback (innermost last): File /usr/local/Zope-2.1.6-linux2-x86/lib/python/ZPublisher/Publish.py, line 214, in publish_module File /usr/local/Zope-2.1.6-linux2-x86/lib/python/ZPublisher/Publish.py, line 179, in publish ... I overlooked that you are running 2.1.6. I don't know about the error message you're getting so this may be unrelated, but I and many many others have had a lot of problems with 2.1.6 and ZSQLMethods. So many that I stuck with 2.1.4 and I'll skip directly to 2.2. > It's funny that you say 0.2.2 is broken. Apart from this one glitch, it > works fine for me (although from memory I think we had to do a little bit > of fiddling at install time to get it to work). I don't know a whole lot about the problem, but fetch_all_rows() is missing, and for whatever reason that breaks my methods. If it's working for you, then great. I don't guess that helps you with your problem though. Did you have this problem with 2.1.4? ___ Ron Bickers Logic Etc, Inc. [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] MySQL Select Statements
At 14:08 30/06/2000 +1000, Curtis Maloney wrote: > > Is this just an oddity with the MySQLDA/Db adapters or am I overlooking > > something blatantly obvious here? All the examples on zope.org don't have > > the extra "AS Column1" additions on them and I was wondering if this is a > > Zope/mySQL-unique thing. > >Can you try to construct the simplest example of a query that doesn't work? Sure. Here tis: select Subject, Subjectname from test order by Subject > > > > I'm using Zope (2.1.6) with MySQL (3.22.32) with Zope MySQL Database > > Adapter (ZMySQLDA) 2.0.0a1 and > > MySQLdb module release: 0.2.2 . > >Hmm.. I am using the same, except for ZMySQLda 1.1.3, and MySQLdb 0.1.2. So, >perhaps it's new in ZMySQLda 2.0. Quite possibly. I've sent a cc to Andy Dustman, just in case he's interested. > > Could someone please let me know as I don't want to have to type the extra > > bits on all my SQL statements from here on in if I don't need to (I am the > > world's worst and laziest typist). If it is the case that it's a necessary > >What, you've never heard of cut'n'paste? (o8 With two fields it's OK, but if you've got 30 fields in a table it gets pretty tiresome (even using cut & paste) :o) > > evil, then I want to put a HowTo or Tip up on zope.org because it took me a > > while to figure out what I was doing wrong, and if I can save someone else > > that same wasted time then I would like to. However I wanted to get my > > facts straight (via all you experts) first. > >A wise move, and a wonderful gesture. That's how 'collaborative' software should work IMHO. >Have a better one, No - *you* have an even better one, Thanks for the reply... - Michael ~~~ Michael Blewett Computer Support Mgr - Biological Sciences Monash University (Clayton Campus) Victoria Australia 3168 " Can I trade this job for what's behind door #2? " ___ 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] MySQL Select Statements
Hi Ron, If I don't specify my Selects using "AS", I then get the following error message: Error Type: KeyError Error Value: SubjectName Traceback (innermost last): File /usr/local/Zope-2.1.6-linux2-x86/lib/python/ZPublisher/Publish.py, line 214, in publish_module File /usr/local/Zope-2.1.6-linux2-x86/lib/python/ZPublisher/Publish.py, line 179, in publish File /usr/local/Zope-2.1.6-linux2-x86/lib/python/Zope/__init__.py, line 202, in zpublisher_exception_hook (Object: ElementWithAttributes) File /usr/local/Zope-2.1.6-linux2-x86/lib/python/ZPublisher/Publish.py, line 165, in publish File /usr/local/Zope-2.1.6-linux2-x86/lib/python/ZPublisher/mapply.py, line 160, in mapply (Object: dtTestList) File /usr/local/Zope-2.1.6-linux2-x86/lib/python/ZPublisher/Publish.py, line 102, in call_object (Object: dtTestList) File /usr/local/Zope-2.1.6-linux2-x86/lib/python/OFS/DTMLDocument.py, line 166, in __call__ (Object: dtTestList) File /usr/local/Zope-2.1.6-linux2-x86/lib/python/DocumentTemplate/DT_String.py, line 502, in __call__ (Object: dtTestList) File /usr/local/Zope-2.1.6-linux2-x86/lib/python/DocumentTemplate/DT_In.py, line 691, in renderwob (Object: sqlRetrieveTestList) KeyError: (see above) (Where Subjectname was the first field referenced in the DTML document). Whereas, If I put the SQL as Select Subjectname AS Subjectname, etc, then it all works fine. It's funny that you say 0.2.2 is broken. Apart from this one glitch, it works fine for me (although from memory I think we had to do a little bit of fiddling at install time to get it to work). - Michael At 00:12 30/06/2000 -0400, Ron Bickers wrote: >Why do you say you "must" use the AS statement? Do you get an error >otherwise? > >MySQLdb 0.2.2 is broken when used with ZMySQLDA. But 0.2.1 works okay for >me. Andy Dustman (author of the MySQLdb) said he will fix it and hinted >that one should use the ZMySQLDA that is on Zope.org (v1.2.0 now I believe). >Since that one now uses his MySQLdb, he will likely kill off his own DA. > >___ > >Ron Bickers >Logic Etc, Inc. >[EMAIL PROTECTED] > > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of > > Michael Blewett > > Sent: Thursday, June 29, 2000 10:53 PM > > To: [EMAIL PROTECTED] > > Subject: [Zope] MySQL Select Statements > > > > > > Hi Everyone, > > Just need someone to put me straight here. When I use Select > > statements in > > an SQL Method I cannot simply : > > > > Select Column1, Column2 etc > > from TableA. > > > > Instead I must use the AS statement ie > > > > Select Column1 AS Column1, Column2 AS Column2 > > from TableA. > > > > Is this just an oddity with the MySQLDA/Db adapters or am I overlooking > > something blatantly obvious here? All the examples on zope.org don't have > > the extra "AS Column1" additions on them and I was wondering if this is a > > Zope/mySQL-unique thing. > > > > I'm using Zope (2.1.6) with MySQL (3.22.32) with Zope MySQL Database > > Adapter (ZMySQLDA) 2.0.0a1 and > > MySQLdb module release: 0.2.2 . > > > > Could someone please let me know as I don't want to have to type > > the extra > > bits on all my SQL statements from here on in if I don't need to > > (I am the > > world's worst and laziest typist). If it is the case that it's a > > necessary > > evil, then I want to put a HowTo or Tip up on zope.org because it > > took me a > > while to figure out what I was doing wrong, and if I can save > > someone else > > that same wasted time then I would like to. However I wanted to get my > > facts straight (via all you experts) first. > > > > Thanks for any help... > > > > - Michael > > ~~~ > > Michael Blewett > > Computer Support Mgr - Biological Sciences > > Monash University (Clayton Campus) > > Victoria Australia 3168 > > > > " Can I trade this job for what's behind door #2? " > > > > > > ___ > > 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 ) > > > > ~~~ Michael Blewett Computer Support Mgr - Biological Sciences Monash University (Clayton Campus) Victoria Australia 3168 " Can I trade this job for what's behind door #2? " ___ 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] MySQL Select Statements
Why do you say you "must" use the AS statement? Do you get an error otherwise? MySQLdb 0.2.2 is broken when used with ZMySQLDA. But 0.2.1 works okay for me. Andy Dustman (author of the MySQLdb) said he will fix it and hinted that one should use the ZMySQLDA that is on Zope.org (v1.2.0 now I believe). Since that one now uses his MySQLdb, he will likely kill off his own DA. ___ Ron Bickers Logic Etc, Inc. [EMAIL PROTECTED] > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of > Michael Blewett > Sent: Thursday, June 29, 2000 10:53 PM > To: [EMAIL PROTECTED] > Subject: [Zope] MySQL Select Statements > > > Hi Everyone, > Just need someone to put me straight here. When I use Select > statements in > an SQL Method I cannot simply : > > Select Column1, Column2 etc > from TableA. > > Instead I must use the AS statement ie > > Select Column1 AS Column1, Column2 AS Column2 > from TableA. > > Is this just an oddity with the MySQLDA/Db adapters or am I overlooking > something blatantly obvious here? All the examples on zope.org don't have > the extra "AS Column1" additions on them and I was wondering if this is a > Zope/mySQL-unique thing. > > I'm using Zope (2.1.6) with MySQL (3.22.32) with Zope MySQL Database > Adapter (ZMySQLDA) 2.0.0a1 and > MySQLdb module release: 0.2.2 . > > Could someone please let me know as I don't want to have to type > the extra > bits on all my SQL statements from here on in if I don't need to > (I am the > world's worst and laziest typist). If it is the case that it's a > necessary > evil, then I want to put a HowTo or Tip up on zope.org because it > took me a > while to figure out what I was doing wrong, and if I can save > someone else > that same wasted time then I would like to. However I wanted to get my > facts straight (via all you experts) first. > > Thanks for any help... > > - Michael > ~~~ > Michael Blewett > Computer Support Mgr - Biological Sciences > Monash University (Clayton Campus) > Victoria Australia 3168 > > " Can I trade this job for what's behind door #2? " > > > ___ > 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 )
Re: [Zope] MySQL Select Statements
On Fri, 30 Jun 2000, Michael Blewett wrote: > Hi Everyone, > Just need someone to put me straight here. When I use Select statements in > an SQL Method I cannot simply : > > Select Column1, Column2 etc > from TableA. > Depending on the column names, yes you can. I just tested it not, and it works fine. If the column names are not valid zope/python names, you will run into problems. > Instead I must use the AS statement ie > > Select Column1 AS Column1, Column2 AS Column2 > from TableA. > > Is this just an oddity with the MySQLDA/Db adapters or am I overlooking > something blatantly obvious here? All the examples on zope.org don't have > the extra "AS Column1" additions on them and I was wondering if this is a > Zope/mySQL-unique thing. Can you try to construct the simplest example of a query that doesn't work? > > I'm using Zope (2.1.6) with MySQL (3.22.32) with Zope MySQL Database > Adapter (ZMySQLDA) 2.0.0a1 and > MySQLdb module release: 0.2.2 . Hmm.. I am using the same, except for ZMySQLda 1.1.3, and MySQLdb 0.1.2. So, perhaps it's new in ZMySQLda 2.0. > > Could someone please let me know as I don't want to have to type the extra > bits on all my SQL statements from here on in if I don't need to (I am the > world's worst and laziest typist). If it is the case that it's a necessary What, you've never heard of cut'n'paste? (o8 > evil, then I want to put a HowTo or Tip up on zope.org because it took me a > while to figure out what I was doing wrong, and if I can save someone else > that same wasted time then I would like to. However I wanted to get my > facts straight (via all you experts) first. A wise move, and a wonderful gesture. > > Thanks for any help... > > - Michael Have a better one, Curtis Maloney ___ 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 )
