[Zope-DB] Dynamically generate sql-query in ZSQL Method

[Peter Sabaini]

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.

very true

  - peter.

> Jim
> 
> 
>>-----Original Message-----
>>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 
>>
>>less horrible.
>>
>>>>These are the unfortunate choices foisted upon us by the 
>>
>>environment.
>>
>>>>(Actually, it's Z SQL plus the ZMI plus DTML that is the 
>>
>>real poison,
>>
>>>>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:
>>>>
>>>>## select_all
>>>>## parameters: table_name
>>>># re isn't available in the default Zope installation; 
>>
>>I'd actually
>>
>>>>usually # do this with an extension method...
>>>>import re
>>>>assert re.search(r'^[a-zA-Z_][a-zA-Z0-9_]*$', 
>>
>>table_name), "Invalid
>>
>>>>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
>>
>>>>SQL analog.
>>>
>>>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 
>>
>>dynamically
>>
>>>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 
>>methods.
>>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.
>>regards
>>garry
>>
>>
>>_______________________________________________
>>Zope-DB mailing list
>>Zope-DB at zope.org
>>http://mail.zope.org/mailman/listinfo/zope-db
>>
> 
> 
> _______________________________________________
> Zope-DB mailing list
> Zope-DB at zope.org
> http://mail.zope.org/mailman/listinfo/zope-db
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/x-pkcs7-signature
Size: 3216 bytes
Desc: S/MIME Cryptographic Signature
Url : http://mail.zope.org/pipermail/zope-db/attachments/20040407/6860d4a2/smime-0001.bin