MySQL Temporary Tables & Z SQL Queries
I have a query much like: CREATE TEMPORARY TABLE temp SELECT * FROM FOO, FOO2 ... delimiter ... SELECT * FROM BAR LEFT JOIN temp The problem is that because Zope doesn't close the connection, the temporary table is left around. If I drop the temporary table at the end of the query, I get no query results from Zope. If I don't, it complains on subsequent queries that the temp table exists already. Any suggestions? -- Michael T. Babcock
On Feb 10, 2004, at 3:07 PM, Michael T. Babcock wrote:
I have a query much like:
CREATE TEMPORARY TABLE temp SELECT * FROM FOO, FOO2 ... delimiter ... SELECT * FROM BAR LEFT JOIN temp
The problem is that because Zope doesn't close the connection, the temporary table is left around. If I drop the temporary table at the end of the query, I get no query results from Zope. If I don't, it complains on subsequent queries that the temp table exists already.
A Python script should be able to handle this: res = context.myQuery() context.closeTempTable("temp") return res Just make a separate ZSQL method that closes the table, and call it from your script. ___/ / __/ / ____/ Ed Leafe http://leafe.com/ http://opentech.leafe.com
Michael T. Babcock wrote at 2004-2-10 15:07 -0500:
I have a query much like:
CREATE TEMPORARY TABLE temp SELECT * FROM FOO, FOO2 ... delimiter ... SELECT * FROM BAR LEFT JOIN temp
The problem is that because Zope doesn't close the connection, the temporary table is left around. If I drop the temporary table at the end of the query, I get no query results from Zope. If I don't, it complains on subsequent queries that the temp table exists already.
Are you using the "<dtml-var sql-delimiter>"? If you do, you should be able to delete the temporary table after the final "<dtml-var sql-delimiter>". -- Dieter
participants (3)
-
Dieter Maurer -
Ed Leafe -
Michael T. Babcock