[Zope-DB] Managing Connections with SQL Methods

[Seth Gottlieb]

Thanks Michele,

I was thinking of doing that as well.  Did you point the DA to SQLRelay?  Or
did you have your Python code connect directly to SQLRelay?  My application
needs to be able to roll back transactions when told to by the transaction
manager.  Do you know if going around Zope's DB Connectivity would cause a
problem?

--Seth

Michele Marcionelli (michele.marcionelli at math.ethz.ch) wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello Seth,
>
> I ran exactly in the same problem and I installed SQL Relay
> (http://sqlrelay.sourceforge.net/) that manage better (i.e. good) the
> connections than ZMySQLDA. If someone has a solution to the problem...
> ;-))
>
> Regards,
> Michele
>
> On 25.10.2005, at 23:12, Seth Gottlieb wrote:
>
> > 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
> - --
> michele.marcionelli at math.ethz.ch / phone: +41 44 632 6193
> eth zentrum / hg g 14 / raemistrasse 101 - ch-8092 zurich
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.4 (Darwin)
>
> iD8DBQFDXqPbqnOUmIjQmOYRAnAjAJ4stbSaa4Psqb+V2jblDiJQc6MMFQCgwt3n
> wiIJXXEf4+H+021jDqzPSY8=
> =SYIz
> -----END PGP SIGNATURE-----
>
>