[Zope-DB] Managing Connections with SQL Methods

[Seth Gottlieb]

The problem seemed to be that Zope.app() was returning a new root object 
instance every time a new DAO (data access object) was initialized. 
After 3, boom.

The work around is to pass the context of the calling object with syntax 
like __of__(self) when you call methods of the DAO.  You can also pass 
in a reference to the calling object in the method call dao = 
getDAO(self,....).  Then the method on the DAO you can use

setattr(self, CONNECTION_NAME, getattr(context,CONNECTION_NAME))

Where CONNECTION_NAME is a string with the same name as the Z SQL 
Connection object that the calling object acquired.



Seth Gottlieb wrote:
> 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-----
>>
>>
> 
> 
> _______________________________________________
> Zope-DB mailing list
> Zope-DB at zope.org
> http://mail.zope.org/mailman/listinfo/zope-db
> 

-- 
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