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

[garry saddington]

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