[Zope-DB] Dynamically generate sql-query in ZSQL Method
peter at sabaini.at
Wed Apr 7 13:58:02 EDT 2004
Jim Abramson wrote:
> I would recommend that if your ZSQL Method stuff isn't broken, don't fix it.
> It is difficult to do complex things with a transactional rdbms in Zope. Along with
> Ian's long list of salient points, I'd add (right up at the top) the complete
> absence of control over transactions - a total nightmare if you have some event
> that you need to trigger a number of write operations that aren't encapsulated in
> a single statement / procedure call.
I've not used this feature much since I try to stay in stored
procedures, but there is the get_transaction().commit() / abort()
functionality. Is this invocation problematic?
> If you need to do this in Zope, the only sane way in my opinion is to externalize -
> write standalone python that takes a reference to your database connection object
> as one of its params, and use it from zope as an external method or product. You can
> run, test, twiddle this all you want from outside zope, and say goodbye to the
> zsql/zmi/dtml hassles (while saying hello to the annoyance of having external, as
> well as internal, assets to manage). You can even pull in database connections from
> some other place than zope, and say goodbye to the nail-biting builtin transaction
> mgmt that zope inflicts. The trade-off is more coding overhead, but you save countless
> hours of hair-pulling trying figure out what zope is doing to your database and
> result data.
I try to do as much as possible in Product code, but use ZSQL Methods
and find that quite usable
Your point about managing internal / external assets is valid of course,
but then its pretty hard to manage code in the ZODB anyway (SCC etc.)
> But of course, you needn't bother with this if zsql methods are doing the trick for
> you! Despite its known flakiness, ZSQL is stable, and certainly known initmately
> by many good folks (on this list for example), and if you've been running an app for
> six months without problems, you probably don't need anything else.
>>From: garry saddington [mailto:garry at joydiv.fsnet.co.uk]
>>Sent: Wednesday, April 07, 2004 12:00 PM
>>To: zope-db at zope.org
>>Subject: Re: [Zope-DB] Dynamically generate sql-query in ZSQL Method
>>On Wednesday 07 April 2004 12:35 am, Peter Sabaini wrote:
>>>Ian Bicking wrote:
>>>>On Apr 6, 2004, at 3:23 PM, Jim Penny wrote:
>>>>>Ian has told you how to do it. Now, I will tell you,
>>don't do it.
>>>>I would disagree with you -- not for some principled
>>reason, but for a
>>>>very practical reason: Z SQL Methods suck. A lot. A huge, huge
>>>>amount. They are horrible. Generating your own SQL
>>inline sucks. A
>>>>lot. It is horrible. *Sometimes* the inline method is
>>>>These are the unfortunate choices foisted upon us by the
>>>>(Actually, it's Z SQL plus the ZMI plus DTML that is the
>>>>IMHO -- they all work together to make things extra unpleasant)
>>>Out of curiosity -- what is so horrible about ZSQL Methods?
>>I use them
>>>quite extensively (though mostly to talk to stored
>>procedures) and may
>>>have some nits but dont find them _that_ horrible...
>>>>Note also that well designed SQL generation in Python
>>isn't horrible. A
>>>>Python script like:
>>>>## parameters: table_name
>>>># re isn't available in the default Zope installation;
>>>>usually # do this with an extension method...
>>>>table name: %s" % table_name
>>>>return container.raw_sql(sql='SELECT * FROM %s' % table_name)
>>>>That isn't so bad. In fact, I would assert it is much
>>better than the Z
>>>Personally, I'd rather encapsulate this in a stored
>>procedure, or, if
>>>this isn't possible, in a Python class if I really _had_ to
>>>determine a table name.
>>> - peter.
>>Now you've got me confused. Being relatively new to Zope this
>>has left me
>>thinking whether I am doing things correctly. i am writing my
>>SQL in a ZSQL
>>method, using DTML for logic and for gathering variables to
>>input to my ZSQL
>>I have a database application running from Zope with
>>Postgresql for the last 6
>>months, programmed as above and have had not one problem with
>>it. Does this
>>discussion mean that I should not do it this way or will
>>things get messy
>>when I add modules to the application, which I plan to.
>>Zope-DB mailing list
>>Zope-DB at zope.org
> Zope-DB mailing list
> Zope-DB at zope.org
-------------- next part --------------
A non-text attachment was scrubbed...
Size: 3216 bytes
Desc: S/MIME Cryptographic Signature
Url : http://mail.zope.org/pipermail/zope-db/attachments/20040407/6860d4a2/smime-0001.bin
More information about the Zope-DB