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

[Ian Bicking]

Matthew T. Kromer wrote:
> 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.

Well, in one case I was commiting a credit card transaction after 
sending it to the processor, but before rendering the result page, so 
that an error in the result page can't keep me from saving the 
transaction to the database.  And in general I'm not putting persistent 
data into the ZODB, so I don't have to worry about integrity across the 
two systems.

And Zope won't really retry a transaction, will it?  That's just... 
well, way to clever for anyone's good.  Plus I do get actual conflict 
errors from the database, which would imply it isn't retrying them.  And 
with some RDBMS's you can't do that at all, like MySQL.  Do I really 
have to worry about rerunning transactions?

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