[Zope] Is it a normal ZSQL Method

[hans]

Terry wrote:

> Hello list,
>
> I had used Zope for months. I found ZSQL MEthod don't have the power to complect my
> expection.
>
> To generate a ZSQL Method to suit my need, I used this, then call by python script
>
> Do you consider it is normal,
> -------------------
> Arguments:
> ar_id
> fields
> updates
>
> UPDATE tt_hold_ar SET
> <dtml-let fields="_.string.split(fields,'\0')" updates="_.string.split(updates,'\0')">
>   <dtml-in expr="_.range(0,_.len(fields))" prefix="loop"><dtml-var
> "fields[loop_item]">=<dtml-sqlvar "updates[loop_item]" type="string"><dtml-unless
> loop_end>,</dtml-unless loop_end>
> </dtml-in>
> </dtml-let>
> <dtml-sqlgroup required where>
> <dtml-sqltest ar_id type=string>
> </dtml-sqlgroup>
>
> ------------------
> Python Script:
> request = container.REQUEST
> RESPONSE =  request.RESPONSE
> ar_id = str(container.ar_id)
> fs=us='' ## fs(filed names) , us (updates)
> fs = container.propertysheets.article.propertyIds()
> us = container.propertysheets.article.propertyValues()
> fs.append('ar_section')
> fs.append('ar_publish')
> us.append(container.ar_section)
> us.append(container.ar_publish)
> fs=string.join(fs,'\0')
> us=string.join(us,'\0')
>
> #print fs
> #print us
> #print ar_id
> container.sql_Update_News(ar_id=ar_id,fields=fs,updates=us)
> --------------------
> or how can I compose a "Query" like:
>
> SELECT * article from articles WHERE ar_head NOT LIKE ('%aa%' OR '%bb%' OR '%cc%')
> AND ar_body LIKE ('%hello%' AND '%world%')
>
> In the Query, the "LIKE" could be "LIKE" or "NOT LIKE", the "AND" could be "OR" or
> "AND", and the total of condiction could change. There could be 1, 2 , or more
> conditions.
> ex:
> SELECT * from article from article WHERE
> condition 1
> AND | OR
> condition 2
> AND | OR
> condition 3
> ..
> ...
> .
>
> I really really wondering, "Is the ZSQL method is the way to solve" or anything else ???

In your case, the best approach might be:
1: create a general ZSQL Method anyQuery , with only one arument myQuery:required
    and a body just <dtml-var myQuery>
2: generate the queries in Python (using string concatenation)
3: call anyQuery(myQuery)
there was at least one discussion abt this technique in the list,
and there were caveats
(like security, when parts of the query derive from user input
and are not apostroph-escaped strings (--> dtml-sqlvar))
hth
--
-------------------------------------------------------------
Who's got only a hammer sees the world as a nail
hans augustin  (software developer)           hans@beehive.de
beehive elektronische medien GmbH       http://www.beehive.de
phone: +49 30 847-82 0                  fax: +49 30 847-82 299