ZMySQLDA problems after upgrade
I recently moved my product from zope 2.2.5 with ZMySQLDA 1.1.4 and MySQLDB 0.1.2 to a Zope 2.3.0b3 with new mysql code (MySQL-python-0.3.0, and ZMySQLDA 2.0.1). With these changes, I've found a couple problems, and was wondering if others where familiar with any of these problems, if some might be configuration issues. 1) All field values appear as strings in python This is the issue I hope is my own configuration mistake. Using the old product, field values where auto-converted to compatible python types; so int fields would be int's in python, not strings. This has broken many things in my product, and although I can patch all my queries to call int()/float()/etc on the results, there is code in MySQL-python to auto-convert field values. 2) select ... limit a[,b] This doesn't work anymore, and that just appears to be a bug in ZMySQLDA. I have a patch if anyone is interested. (Problem is that DB.query() adds a limit clause to the query, based on the max_rows argument. This makes windowing (using two arguments with limit) impossible.) Thanks, -Randy
On Thu, 25 Jan 2001, Randall F. Kern wrote:
1) All field values appear as strings in python
Use ZMySQLDA-2.0.4. There was some confusion on my part as to whether or not the DA should return string values to Zope. All values are now converted to the correct Python types, or DateTime objects.
2) select ... limit a[,b] This doesn't work anymore, and that just appears to be a bug in ZMySQLDA.
It's a feature.
I have a patch if anyone is interested. (Problem is that DB.query() adds a limit clause to the query, based on the max_rows argument. This makes windowing (using two arguments with limit) impossible.)
Here's the problem as I see it. First is that LIMIT is MySQL-specific, AFAIK, so using it in Z SQL Methods is going to be non-portable between databases, which you may or may not care about. Second is that the whole reason LIMIT exists is because with MySQL you have two options: Store the entire result set on the client side (mysql_store_result), or keep it on the server side (mysql_use_result) and retrieve it row-by-row. But since Zope requires that the entire result set be returned to it by the query method, mysql_store_result is the way to go. And since Zope passes max_rows as a separate parameter, it makes sense (to me) to make use of it. Third relates to SQL syntax, which is very large, and case-insensitive. While it's not too hard to determine that the statement is a SELECT, it is very hard to determine with certainty that it has a LIMIT clause. Solution: This could be solved by extending the DA interface to include an additional parameter: skip_rows=0 Then ZMySQLDA could make use of this in adding the LIMIT clause. The problem is, I think it will break a lot of DAs. However, I think they can be very easily fixed. Besides which, I think a lot of DAs are going to be broken by Zope 2.3 for the simple reason that all the DTML code in the base DA product is moving from Shared.DC.ZRDB into a dtml subdirectory. Perhaps the DAs are already fixed, I really haven't messed with Zope 2.3 very much yet. I am cc:ing Brian Lloyd (because he seems like the best bet at this point) to get Digital Creations' attention (obviously they read the list, but this message could easily be overlooked). You can also send me your patch, if you think you have a better way to solve this. -- Andy Dustman PGP: 0xC72F3F1D @ .net http://dustman.net/andy "Normally with carbonara you use eggs, but I used lobster brains instead." -- Masahiko Kobe (Iron Chef Italian): 30-year-old Giant Lobster Battle
-----Original Message----- From: adustman@kenny.comstar.net [mailto:adustman@kenny.comstar.net]On Behalf Of Andy Dustman Sent: Friday, January 26, 2001 12:42 AM To: Randall F. Kern Cc: zope@zope.org Subject: Re: [Zope] ZMySQLDA problems after upgrade
be returned to it by the query method, mysql_store_result is the way to go. And since Zope passes max_rows as a separate parameter, it makes sense (to me) to make use of it.
How about excluding the automatic LIMIT clause if max_rows is set to 0? Would that confuse people? _______________________ Ron Bickers Logic Etc, Inc. rbickers@logicetc.com
-----Original Message----- From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of Ron Bickers Sent: Friday, January 26, 2001 11:28 AM To: Andy Dustman; Randall F. Kern Cc: zope@zope.org Subject: RE: [Zope] ZMySQLDA problems after upgrade
How about excluding the automatic LIMIT clause if max_rows is set to 0? Would that confuse people?
Oh! It already does that. So if you want to make your own LIMIT clause, just set max_rows to 0. Correct me, Andy, if this is a bad thing. _______________________ Ron Bickers Logic Etc, Inc. rbickers@logicetc.com
On Fri, 26 Jan 2001, Ron Bickers wrote:
How about excluding the automatic LIMIT clause if max_rows is set to 0? Would that confuse people?
Oh! It already does that. So if you want to make your own LIMIT clause, just set max_rows to 0. Correct me, Andy, if this is a bad thing.
That sounds very much like a good thing. I will have to add that to the tip I have (really, re-write it). I actually forgot that it did that. Randy, does setting max_rows to zero fix things for you? -- Andy Dustman PGP: 0xC72F3F1D @ .net http://dustman.net/andy "Normally with carbonara you use eggs, but I used lobster brains instead." -- Masahiko Kobe (Iron Chef Italian): 30-year-old Giant Lobster Battle
participants (3)
-
Andy Dustman -
Randall F. Kern -
Ron Bickers