----- Original Message -----
From: "Andreas Tille" <[EMAIL PROTECTED]>
To: <zope-db@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
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db
