[Zope] ZMySQLDA problems after upgrade

[Andy Dustman]

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