[Zope] RE: ZMySQLDA and aggregate functions on Win

15 Jan 2001

      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