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

[Jim Abramson]

I'm still of the opinion that ZSQL is fine, as long as its use is
limited to what it is good at.  It doesn't support fine-grained
transaction control, so don't use it for that.  SP's are the way to
go.

Jim

> -----Original Message-----
> From: Peter Sabaini [mailto:peter at sabaini.at]
> Sent: Thursday, April 08, 2004 1:37 PM
> To: Jim Abramson
> Cc: zope-db at zope.org
> Subject: Re: [Zope-DB] Dynamically generate sql-query in ZSQL Method
> 
> 
> 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
> >>>
> >>>
> >>>
> >>
> > 
>