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

[Peter Sabaini]

Oh ok I see -- DB people hate it when their data gets inconsistent :-) 
One more reason to use stored procedures I guess. But one can still do 
this with ZSQL Methods, right? Not because ZSQL Methods are the One True 
Way, just to have the convenience of some simple caching if need be and 
Brains -- another little convenience that I've come to get used to.

peter.


Jim Abramson wrote:
> Well, a couple posts ago, Matt Kromer provided an explanation 
> of why not to meddle directly with RDBMS commit/rollback, which
> distills the issue far better than I could.  The 
> get_transaction() stuff, to me, is ZODB black magic - a can
> of worms I've avoided opening for a year now, and have no 
> plans to get into at this point if I can help it.  
> 
> I can fairly summarize my own experience like this...the app
> I'm running at first needed only to do simple, one-shot database
> writes, for which ZSQL was perfectly adequate.  Over time the 
> feature set and the database evolved enough that I needed to 
> implement actions involving several write ops, perhaps the 
> results of one being used as input for another, and so on.  
> 
> What happened next is still kind of blurry, but i vaguely 
> remember something about doing 'COMMIT' and 'ROLLBACK' inside 
> ZSQL methods, and noticing that the results in the db were 
> coming out highly inconsistent...last thing I recall is our 
> DBA team inviting me for a meeting in a dark alley outside 
> the office...something about 'deadlock'...everything went 
> black...and I woke up later, badly bruised.
> 
> Since then I've been putting all the transactional stuff 
> inside oracle as stored procedures (wrapped in PRAGMA 
> AUTONOMOUS TRANSACTION to boot) and have had no further 
> problems along these lines.
> 
> Jim
> 
> 
> 
>>-----Original Message-----
>>From: Peter Sabaini [mailto:peter at sabaini.at]
>>Sent: Wednesday, April 07, 2004 3:13 PM
>>To: Jim Abramson
>>Cc: zope-db at zope.org
>>Subject: Re: [Zope-DB] Dynamically generate sql-query in ZSQL Method
>>
>>
>>I did some quick googling and found a thread about problems 
>>with manual 
>>transaction handling in standard_error_message -- did I overlook 
>>something? Could you summarize your experience in a few sentences?
>>
>>Thanks,
>>peter.
>>
>>
>>Jim Abramson wrote:
>>
>>>>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?
>>>
>>>
>>>I was under the impression that this was a per-thread thing, not
>>>per-connection
>>>which is what I'd need.  Furthermore, I've read many stern
>>>admonishments, on this 
>>>here list and elsewhere, never to attempt to mess with 
>>
>>commit/rollback
>>
>>>on an
>>>external RDBMS connection, unless you want to suffer 
>>
>>miserably.  I've
>>
>>>learned
>>>(by firsthand experience) that this is indeed good advice.
>>>
>>>Jim
>>>
>>>
>>>
>>
> 
-------------- 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/20040408/858753dc/smime.bin