[Zope-DB] Managing Connections with SQL Methods

[Seth Gottlieb]

Hi,

I have a Python class that my Zope application uses to talk to a 
relational database (MySQL for now).  I am using a Z SQL Connection 
(ZMySQLDA) object to handle DB connectivity.  When I initialize, I get a 
handle of the connection object like this:

import Zope
setattr(self, CONNECTION_NAME, getattr(Zope.app(),CONNECTION_NAME))

Where CONNECTION_NAME is a string that matches the ID of the 	connection 
object that I have in the Root folder of Zope.

Then I run queries like this:

from Products.ZSQLMethods.SQL import SQL

setattr(self, '_selectProperties',
             SQL('_selectProperties', '', CONNECTION_NAME, 'schemaId',
             'SELECT * FROM ' + TABLE_NAME[self._context.id] + ' 
WHERE 			     SCHEMA_ID = <dtml-sqlvar schemaId type="int">'))

The life of this class should be Zope transaction (I am hooking into 
Shared.DC.ZRDB.TM.TM to manage these objects).

The problem I am running into is I seem to be running out of 
connections.   Everytime I inititialize and run "show processlist;" 
within MySQL, the number of connections increases.  I get to a maximum 
of 3 and then my Zope instance hangs.

Is there any reason why these DB connections do not get reused or 
destroyed after queries are made?

Any ideas?

Thanks,

Seth

-- 
Seth Gottlieb
Optaros, Inc.
60 Canal Street
Boston, MA 02114
e: sgottlieb at optaros.com
v: 617.227.1855 ext. 107
f: 617.227.1755
m: 617.852.2956