[Zope] RE: ZMySQLDA and aggregate functions on Win

[Markus Kemmerling]

Thank you Dave for your answer. But what do you mean by you don't expect a SELECT returning a number to work?
The error you got clearly is a mysql syntax error: If I have a table 'shop' and make a SELECT on 'show' I really wouldn't expect that to work ;-)
But I don't get any mysql error, I get a python error.

Let me repeat my problem and try to be a little bit more precise (I use your example):

SELECT COUNT(*) FROM show GROUP BY article;

works from the mysql command line, works from within Zope on linux, but breaks from within zope on windows. SELECT COUNT(*) FROM show; works from within Zope on windows, too.

So the problem is to have a SELECT with COUNT() and GROUP BY-clause from within Zope on Windows. Since it works on the mysql command line, it is definitely not an SQL error.

I use Zope 2.2.4, mysql 3.23gamma, ZMySQLDA 1.1.3 and the libmySQL.dll linked from the HowTo explaining the installation of ZMySQLDA on Win32.

The error I get is of type 'KeyError' with value 'unhandeld' and occurs on line 191 of ZMySQLDA's db.py. This and the next lines read as follows:

        for d in desc:
            item={'name': d[0],
                  'type': defs[d[2]],
                  'width': d[3],
               }
            func(item)

where 'desc' is the fieldlist of the result set returnded by the query and 'defs' is some kind of type-mapping defined at the top of db.py, that I don't really understand:

    defs={
        "short": "i", "long": "i", "char": "s", "double": "n", "decimal": "n",
        "float": "n", "tiny blob": "t", "medium blob": "t", "long blob": "t",
        "blob": "t", "date": "d", "time": "s", "datetime": "d",
        "timestamp": "d", "varchar": "t", "string": "t",
        }

The following little ugly hack makes my query work:

        for d in desc:
            try:
                item={'name': d[0],
                      'type': defs[d[2]],
                      'width': d[3],
                   }
                func(item)
            except KeyError:
                item={'name': d[0],
                      'type': 'i',
                      'width': d[3],
                   }
                func(item)

So it seems to me that COUNT returns some field-type unknown to 'defs'. (What still confuses me is, that this only happens in combination with a GROUP BY-clause.)

I wonder what's the value of d[2] for the COUNT()-column, but since I am quite new to python and Zope I still don't know how to debug.

I also wonder if anybody running Zope with mysql on windows can reproduce this error. Or maybe someone with a good knowledge of ZMySQLDA and the python mysql-module has some hint?

Sorry that this message got so long ...

Markus Kemmerling

> This is curious to me - I tried this on my Linux platform from 
> mysql and it
> doesn't work.
> Perhaps I misunderstand what you are trying to do:
> 
> SELECT COUNT(*) FROM mytable - returns a number - so I really don't expect
> it to work.
> 
> I have a table - shop and a column in that table - article:
> 
> mysql> select count(*) from show group by article;
> ERROR 1064: You have an error in your SQL syntax near 'show group by
> article' at line 1
> 
> Sorry if I misunderstand what you are trying to do -
> 
> =================================
> Dave Woolston   davew@digicool.com
> Digital Creations  www.digicool.com
> (716) 262-4715
> 
> 
> > I have a problem with ZMySQLDA and aggregate functions in 
> combination with
> > grouping on Windows.
> > When I say:
> >   SELECT COUNT(*) FROM mytable GROUP BY myfield;
> > I get an KeyError 'Unhandeld'.
> >
> >    SELECT COUNT(*) FROM mytable;
> > works fine, so does
> >   SELECT myfield FROM mytable GROUP BY myfield;
> > but not the combination of both.
> > Also on a linux machine the error doesn't occur. Of course I tested the
> > queries from the mysql command line.
> >
> > Any help?
> >
> > Markus Kemmerling
> 
>