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

[Andreas Tille]

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.

-- 
http://fam-tille.de