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

[Matthew T. Kromer]

On Apr 7, 2004, at 3:18 PM, Ian Bicking wrote:
>>
>
> Can you explain more of what you're thinking?  I'm having what I 
> believe
> are some transaction-related problems with an application, and I'm to 
> both avoid something stupid, and figure out how I really should be 
> doing this.
>
> In the few times when I've wanted to commit a transaction midway 
> through a request, I've use a SQL method like:
>
> COMMIT;
> BEGIN;
>
> Which has seemed to work.  Is this the kind of thing you are thinking 
> of?  Or is what I am doing a bad idea?
>

Egads!  Gadzooks!  Diverse alarums!

If you do this in Zope you're *asking* for trouble.  Zope's transaction 
machinery can *retry* requests for you, invisibly to the end user.  If 
you get a ConflictError raised during ZODB commit, Zope is going to 
re-run the transaction.  If you're committing to a RDBMS in the middle, 
you've just violated your transactional integrity and probably 
committed bogus data to your database.

I would think long and hard about trying to invoke a database stored 
procedure or something else to avoid explicitly COMMIT-ting in Zope.