[Zope-DB] Managing Connections with SQL Methods

[Chris Withers]

Why don't you just use ZSQL Methods?!

Chris

Seth Gottlieb wrote:
> 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
>>
> 

-- 
Simplistix - Content Management, Zope & Python Consulting
            - http://www.simplistix.co.uk