[Zope-DB] dtml-let variables in sql queries

Jonathan dev101 at magma.ca
Thu Nov 23 10:17:32 EST 2006


----- Original Message ----- 
From: "Andreas Tille" <tillea at rki.de>
To: <zope-db at zope.org>
Sent: Thursday, November 23, 2006 10:06 AM
Subject: [Zope-DB] dtml-let variables in sql queries


> Hi,
>
> I have defined the following table
>
> CREATE TABLE names (
>    nameid   int,
>    name     text,
>    fake     int
> );
>
> INSERT INTO names values ( 1, 'Alfons',    0 );
> INSERT INTO names values ( 2, 'Alberto',   0 );
> INSERT INTO names values ( 3, 'Adam',      0 );
> INSERT INTO names values ( 4, 'Anibal',    0 );
> INSERT INTO names values ( 5, 'Bert',      0 );
> INSERT INTO names values ( 6, 'Caesar',    0 );
> INSERT INTO names values ( 7, 'Dagobert',  1 );
> INSERT INTO names values ( 8, 'Kuno',      1 );
>
> GRANT SELECT ON names TO zope ;
>
> The following SQL-Method should extract all names where
> fake = 0 except if there are further parameters like
> name or nameid that should restrict the result set if they
> are given:
>
> <params>name nameid
>
> </params>
> SELECT * FROM names
> WHERE fake = 0
> <dtml-if name>
>   AND <dtml-sqltest name op=like type=nb>
> </dtml-if>
> <dtml-if nameid>
>   AND <dtml-sqltest nameid op=eq type=int>
> </dtml-if>
>
> This works in the ZMI test tab as expected.
>
> The original problem is that I want to design a Form where
> you can specify only first letters like 'Al' and the query
> looks for "name like 'Al%'".  I tried to do this using:
>
>
> <dtml-var standard_html_header>
>
> <dtml-if "REQUEST.form.has_key('nameid')">
>     <dtml-let nameid="REQUEST.form['nameid']">
>        Selected nameid= <dtml-var nameid><br />
>        <dtml-var print_cases>
>     </dtml-let>
> <dtml-else>
>
>   <dtml-if "REQUEST.form.has_key('namepart')">
>     <dtml-let namepart="REQUEST.form['namepart']">
>         Namepart = <dtml-var namepart><br />
>         <dtml-let name="namepart + '%'">
>           Seek for name = '<dtml-var name>'.
>           <dtml-var print_cases>
>         </dtml-let>
>     </dtml-let>
>   <dtml-else>
>     <form action="index_html" name="test" method="POST">
>       <input name="namepart" type="text" size="10" />
>     </form>
>   </dtml-if>
> </dtml-if>
>
> <dtml-var standard_html_footer>
>
>
> where namepart is constructed as name+'%' to enable the like
> query.  Unfortunately
>          <dtml-let namepart="namepart + '%'">
> does not seem to work transparently in the SQL-Method, because
> this does not work and just prints every single name.  If somebody
> wants to try this code here is the print_cases method:
>
> <table>
> <dtml-in GetCases>
>   <tr><td>
>    <a href="<dtml-var URL0>?nameid=<dtml-var nameid>"><dtml-var name></a>
>   </td></tr>
> </dtml-in>
> </table>
>
>
> Any idea why the variable namepart is not known in the SQL-Method
> while nameid is and works perfectly to select one name from the list?
>
> Many thanks
>
>          Andreas.

First off, i would highly recommend using python scripts instead of DTML for 
the kind of processing you have described.

That said,  you have not described the GetCases method.  If GetCases is the 
name of your SQL method, then you need to pass in the parameter 'nameid'

hth

Jonathan




More information about the Zope-DB mailing list