[Zope-dev] Dynamic SQL statement construct

[Martijn Pieters]

At 10:46 29/07/99 , Paul Chung Chee Soong wrote:
>As usual, I'm beeen using Zope-1.10.3-src, Zap-1.10, RedHat 5.2 and
>lately ZPyGresql Database Adaptor. I wonder whether we can dymanicaly
>construct an SQL statement. Below is the following situation:
>
>DTML Method 1 :-
>============
>    Contain 3 same combo box as the first, second and third sort key. Each
>combo box contains   3 sort keys,  Aeg. A, B, C.
>    User will choose the combination of keys they want and submit the
>page..
>
>DTML Method 2 :
>============
>Display the information as the sorted manner defined in the earlier page.
>
>
>So how do you guys think? Can Z SQL Method do that? The followings are
>the possible key combinations:
>ABC
>ACB
>BAC
>BCA
>CAB
>CBA
>AB
>AC
>BA
>BC
>CA
>CB
>A
>B
>C
>
>Do we need to BUILD 15 Z SQL Method?? If not, what is the proper way?

Just make one and use DTML constructs to set up the sorting.
Name the three comboboxes 'sorting:list' (yes, all three of them). You 
might need some Javascript or DTML to make sure the user doesn't select A 
twice, or something like that.

Your ZSQL should do something like this:

from mytable select *
<!--#in sorting-->
<!--#if sequence-start-->order by<!--#/if-->
<!--#var sequence-item--><!--#unless sequence-end-->,<!--#/unless-->
<!--#/in-->

This will cause the select tag to have up to three 'order by' columns 
specified, or no order by clause at all if 'sorting' is a non-existing or 
empty list.

Watch it: this is untested, YMMV.

--
Martijn Pieters, Web Developer
| Antraciet http://www.antraciet.nl
| Tel: +31-35-7502100 Fax: +31-35-7502111
| mailto:mj@antraciet.nl http://www.antraciet.nl/~mj
| PGP: http://wwwkeys.nl.pgp.net:11371/pks/lookup?op=get&search=0xA8A32149
------------------------------------------