[zen@shangri-la.dropbear.id.au: Re: [Zope] Building SQL statements dynamically]

[Jim Penny]

On Monday, May 13, 2002, at 09:40  PM, Tille, Andreas wrote:

>Hello,
>
>I have to port a complex database application which has no fix SQL
>statements.  The user builds the statement while interacting with
>some menus and after having done some selections a database query
>is builded depending from several options.
>
>Any hint how this can be performed using Zope?

See
http://www.zope.org/Members/jpenny/Variable_SQL_statements

Note:
I strongly recommend that you NOT use this approach.  It is far too easy
to open database secureity problems.  You have to make sure that you
sqlquote every argument, without fail.  It also makes debugging far
harder, as depending on DA and database configuration, you may have
trouble seeing the actual SQL used.

Usually you can do something using dtml-sqlgroup, particularly if the
query is naturally conjunctive (or naturally disjuctive, mixed usage can
be more confusing).  See the DTML API reference of the Zope book.  Most
of the examples are pretty readable.  But, note that ordinary dtml may
be mixed with dtml-sqlgroup code.  In my opinion, this can lead to
readability improvement.  For an example, see Pam's ZSQL User Guide, in
particular, 
http://www.zope.org/Documentation/Guides/ZSQL-HTML/ZSQL.1.5.4.html

The advantages the sqlgroup approach are two-fold.  1)  quoting is
handled more reliably.  2)  You can test the method methodically.  But,
balance in all things.  I have seen sqlgroup queries that were so
complex that the original author could not figure out what they did, in
such cases it makes a lot of sense to split the query into separate
methods and use program logic to determine which to call.

Jim Penny