bug with SQL methods?
Hi, I'm re-posting to this list because it probably is more suited here... thanks in advance, ------- Jordan B. Baker -- jbb@spyderlab.com weaving the web @ http://www.spyderlab.com ---------- Forwarded message ---------- Date: Fri, 5 Mar 1999 12:11:02 -0500 (EST) From: Jordan B. Baker <jbb@spyderlab.com> To: zope-dev@zope.org Subject: [Zope-dev] SQL methods q: Hi guys: I'm trying to write a SQL method though, and its giving me an error when I try to test it.. Error, exceptions.TypeError: keyword parameter redefined SQL used: Could not render the query template! The query I was using: SELECT searches.engineID as engine, queries.param as query, MAX(searches.timestamp) AS time, searches.position AS position, MIN(lastsearch.timestamp) AS lastupdate, lastsearch.position AS lastsearch, searches.URL as URL, 'NA' as change, engines.graphic as engine_gfx FROM searches LEFT JOIN searches as lastsearch USING (queryID,engineID), engines, queries WHERE lastsearch.timestamp BETWEEN <!--#sqlvar pstart type=int --> AND <!--#sqlvar pend type=int --> AND searches.timestamp BETWEEN <!--#sqlvar pstart type=int --> AND <!--#sqlvar pend type=int --> AND queries.clientID = <!--#sqlvar client type=int --> AND queries.queryID = searches.queryID AND engines.engineID = searches.engineID AND searches.timestamp != lastsearch.timestamp GROUP BY searches.engineID, searches.queryID ORDER BY searches.engineID ASC, searches.queryID ASC, searches.timestamp ASC, lastsearch.timestamp DESC ; Any ideas what I could be doing wrong? BTW, Zope is great! I've been kinda messing around with it for a while, also checking out PHP3 and other UNIX-based scripting languages and I've very impressed. As soon as I figure out what is causing this I plan to write something 'cool' with Zope :) TIA, -jordan. _______________________________________________ Zope-Dev maillist - Zope-Dev@zope.org http://www.zope.org/mailman/listinfo/zope-dev
More information on my front.. I've been fiddling around a bit trying to find out what is going wrong.. I'm using ZServer/Zope 1.10.2 with ZMySQLDA that I just downloaded 2 days ago. The traceback is as follows: <!-- Traceback (innermost last): File lib/python/ZPublisher/Publish.py, line 877, in publish_module File lib/python/ZPublisher/Publish.py, line 590, in publish (Info: spyderwatch/sql_period_report/manage_test) File ../lib/python/Shared/DC/ZRDB/DA.py, line 305, in manage_test (Object: CopySource) File ../lib/python/Shared/DC/ZRDB/DA.py, line 284, in manage_test (Object: CopySource) File ../lib/python/Shared/DC/ZRDB/DA.py, line 384, in __call__ (Object: CopySource) TypeError: (see above) --> line 384 of DA.py -- __call__ reads: query=apply(self.template, (p,), argdata) I added a bit of debugging code and was able to a take a look at some of the data being passed around: argdata was {'sql_delimiter': '\000', 'client': '1', 'pend': '1', 'pstart': '1', 'sql_quote__': } This is starting to confuse me because simple queries work fine within Zope, its only the query that I posted in my previous message that seems to be fowling things up. I'll keep slagging away until either I figure it out or someone tells me its not something I've done and that I should submit it to the Collector :) -jordan.
Continuing this great tradition of responding to my own posts :) On Fri, 5 Mar 1999, Jordan B. Baker wrote:
argdata was {'sql_delimiter': '\000', 'client': '1', 'pend': '1', 'pstart': '1', 'sql_quote__': }
This is starting to confuse me because simple queries work fine within Zope, its only the query that I posted in my previous message that seems to be fowling things up.
Okay, now it makes sense. Is 'client' a reserved variable? If so, perhaps a warning would be nice if you attempt to use a reserved variable as an argument to a SQL method. Argh.. so I got that fixed and now I am getting another traceback when testing the SQL method! I can't believe it :) Here it is: Error, exceptions.KeyError: unhandled <!-- Traceback (innermost last): File lib/python/ZPublisher/Publish.py, line 877, in publish_module File lib/python/ZPublisher/Publish.py, line 590, in publish (Info: spyderwatch/sql_period_report/manage_test) File ../lib/python/Shared/DC/ZRDB/DA.py, line 305, in manage_test (Object: CopySource) File ../lib/python/Shared/DC/ZRDB/DA.py, line 286, in manage_test (Object: CopySource) File ../lib/python/Shared/DC/ZRDB/DA.py, line 390, in __call__ (Object: CopySource) File lib/python/Products/ZMySQLDA/db.py, line 191, in query KeyError: (see above) --> -jordan.
On Fri, 5 Mar 1999, Jordan B. Baker wrote:
Error, exceptions.KeyError: unhandled
<!-- Traceback (innermost last): File lib/python/ZPublisher/Publish.py, line 877, in publish_module File lib/python/ZPublisher/Publish.py, line 590, in publish (Info: spyderwatch/sql_period_report/manage_test) File ../lib/python/Shared/DC/ZRDB/DA.py, line 305, in manage_test (Object: CopySource) File ../lib/python/Shared/DC/ZRDB/DA.py, line 286, in manage_test (Object: CopySource) File ../lib/python/Shared/DC/ZRDB/DA.py, line 390, in __call__ (Object: CopySource) File lib/python/Products/ZMySQLDA/db.py, line 191, in query KeyError: (see above) -->
Why does my ZMySQLDA Z SQL Method die with a KeyError: unhandled message? Well, I was looking at the code for the MySQLmodule and it appears that it has problems handling some fields. This appears to be a limitation of the MySQLmodule. It currently does not handle the LONGLONG datatype longer timestamps use. At least I assume its the LONGLONG that is messing it up. I haven't looked into that much detail to be honest. I'm not quite sure if this is something that should be included in the FAQ, or considered a known bug, or submitted to the Collector? Thanks, ------- Jordan B. Baker -- jbb@spyderlab.com weaving the web @ http://www.spyderlab.com
I just ran into this error. When I did a SELECT * on joined tables it would give the error but if I named the fields I needed it worked. I played with it a little and got the error again when I named a field that ended in _num in the SELECT. I have four such INT fields that I don't need, they are used by the JOIN, and have _n counterparts in the JOINed tables (see SQL below). As you can see my SELECT list is long and it would have been nice to just use a * since I need almost all the fields. Everything works fine in MySQL client but from Zope I can produce that error. Maybe there is some conflict with the *_num names. What are your field names? SELECT sonum,cust_name,cust_n,description,kickoff,delivery, comment,partnum,pm_n,pm_initials,cad_n,cad_initials, md_n,md_initials,mm_n,mm_initials,division,custdate,RD, manmeet,steel,exp_del FROM jobinfo LEFT JOIN customer ON cust_n=cust_num LEFT JOIN pm ON pm_n=pm_num LEFT JOIN cad ON cad_n=cad_num LEFT JOIN md ON md_n=md_num LEFT JOIN mm ON mm_n=mm_num WHERE <!--#sqltest division column=division type=string--> AND active='1' ORDER BY sonum DESC
On Fri, 5 Mar 1999, Jordan B. Baker wrote:
Why does my ZMySQLDA Z SQL Method die with a KeyError: unhandled message?
Well, I was looking at the code for the MySQLmodule and it appears that it has problems handling some fields.
This appears to be a limitation of the MySQLmodule. It currently does not handle the LONGLONG datatype longer timestamps use.
At least I assume its the LONGLONG that is messing it up. I haven't looked into that much detail to be honest.
Phil A ------------------------------------------ Philip Aylesworth zopelist@regalint.com Regal International
On Mon, 8 Mar 1999, Philip Aylesworth wrote:
I just ran into this error. When I did a SELECT * on joined tables it would give the error but if I named the fields I needed it worked. I played with it a little and got the error again when I named a field that ended in _num in the SELECT. I have four such INT fields that I don't need, they are used by the JOIN, and have _n counterparts in the JOINed tables (see SQL below). As you can see my SELECT list is long and it would have been nice to just use a * since I need almost all the fields.
Everything works fine in MySQL client but from Zope I can produce that error.
Maybe there is some conflict with the *_num names. What are your field names?
I was able to find a work around. The problem was the handling of the TIMESTAMP datatype by the MySQLmodule. It doesn't know how to handle a long timestamp unfortunately, so you'll want to retrieve it as a UNIX timestamp or as a formatted date using one of MySQL's many date functions. The other thing is that I doubt it handles double long integers since AFAIK Python doesn't handle these without an extension module. If your query is returning any of these you'll most likely get this response from Zope... ------- Jordan B. Baker -- jbb@spyderlab.com weaving the web @ http://www.spyderlab.com
On Mon, 8 Mar 1999, Jordan B. Baker wrote:
The other thing is that I doubt it handles double long integers since AFAIK Python doesn't handle these without an extension module.
It sure does: Python 1.5.2b1 (#2, Jan 18 1999, 11:01:20) [GCC 2.7.2.1] on freebsd3 Copyright 1991-1995 Stichting Mathematisch Centrum, Amsterdam
long("99999999999999999") 99999999999999999L 9999999999999L ** 2 99999999999980000000000001L
Spiffy, eh? I'm pretty sure it's arbritrary-precision, which means you can make numbers as large as you have memory for. Or is this not what you mean? Mike. -- --- | Mike Pelletier Work: 519-746-1607 /opeware! | Software Developer Home: 519-725-7710 --- | mike@zopeware.com Fax: 519-746-7566 http://www.zopeware.com | Zopeware is not endorsed by Digital Creations
Hi All, I fetched FAQ tool from "http://www.zope.org/WebReview/" but the archive contain only : * __init__.py *version.txt *Product.dat No, FAQ.py file. Strange no. Thus, this is maybe why I cant view Faq in the list of objet in Zope!! FR François-Régis Chalaoux Bioinformatics Group Synthélabo Biomoléculaire 67080 Strasbourg Cedex FRANCE Tél : 03 88 60 87 14 Fax : 03 88 45 90 70
At 06:43 PM 3/8/99 +0100, FR Chalaoux wrote:
I fetched FAQ tool from "http://www.zope.org/WebReview/" but the archive contain only :
* __init__.py
*version.txt
*Product.dat
No, FAQ.py file. Strange no.
While this may seem strange, this is in fact the normal contents of a Product distribution file for a "levered product". When you build a product through the web inside the control panel, these are the normal files Zope creates inside the product distribution.
Thus, this is maybe why I cant view Faq in the list of objet in Zope!!
Unfortunately this is not the case. The reason you can't use the FAQ is that there is a bug in Zope which keeps some people from being able to use levered distributions. As discussed on the list, it appears to be related to zlib compression. We are aware of the problem and are working to solve it. Thanks for your patience. -Amos P.S. If you're curious about a bug, you can try searching the Collector. http://www.zope.org/Collector In this case a search under "lever" or "faq" would turn up the existing bug report for this problem.
On Mon, 8 Mar 1999, Mike Pelletier wrote:
On Mon, 8 Mar 1999, Jordan B. Baker wrote:
The other thing is that I doubt it handles double long integers since AFAIK Python doesn't handle these without an extension module.
It sure does:
Python 1.5.2b1 (#2, Jan 18 1999, 11:01:20) [GCC 2.7.2.1] on freebsd3 Copyright 1991-1995 Stichting Mathematisch Centrum, Amsterdam
long("99999999999999999") 99999999999999999L 9999999999999L ** 2 99999999999980000000000001L
Spiffy, eh? I'm pretty sure it's arbritrary-precision, which means you can make numbers as large as you have memory for. Or is this not what you mean?
Hey, I'm glad you proved me wrong :) It looks like there is a limitation in the MySQLmodule when handling MySQL's LONGLONG datatypes that could probably be corrected. I may take a look at this later, but I am thinking I may just store UNIX timestamps in my database since this is more portable across databases anyways. But anyway, this may be a FAQ item as I'm sure other people may run into it. -jbb. ------- Jordan B. Baker -- jbb@spyderlab.com weaving the web @ http://www.spyderlab.com
I found that this problem came up when I used MEDIUMINT in MySQL. I changed my database to INT and it worked fine. Phil A ------------------------------------------ Philip Aylesworth zopelist@regalint.com Regal International
participants (5)
-
Amos Latteier -
chalaouxf＠synbio.tpgnet.net -
Jordan B. Baker -
Mike Pelletier -
Philip Aylesworth