Advanced
Mailing List Archive

Mailing List Archive

  1. Home >
  2. Zope>
  3. DB
dtml-let variables in sql queries
tillea at rki
Nov 23, 2006, 7:06 AM
Post #1 of 6 (4953 views)
Permalink
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
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db
Re: dtml-let variables in sql queries [ In reply to ]
dev101 at magma
Nov 23, 2006, 7:17 AM
Post #2 of 6 (4822 views)
Permalink
----- Original Message -----
From: "Andreas Tille" <tillea@rki.de>
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
Re: dtml-let variables in sql queries [ In reply to ]
tillea at rki
Nov 23, 2006, 8:03 AM
Post #3 of 6 (4813 views)
Permalink
On Thu, 23 Nov 2006, Jonathan wrote:

>> <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>
>
> First off, i would highly recommend using python scripts instead of DTML for
> the kind of processing you have described.

You are right here and I would definitely prefer Python scripts. The
problem is that I'm using the Formulator Product and despite there are
descriptions how to use it with Python scripts I failed while it was
very straigtforeward to use DTML (in fact, this is the only part of
my application in DTML). I just stripped down the application to a
very simple example and avoided Formulator magic which is obviousely
not the source of the problem.

> That said, you have not described the GetCases method.

I forgot to mention the name. It is the code snipped above.

> If GetCases is the
> name of your SQL method, then you need to pass in the parameter 'nameid'

Well, nameid is not explicitely passed but taken from the parent
out of

<dtml-let nameid="REQUEST.form['nameid']">

and this works. The problem is that name which is builded
out of

<dtml-let namepart="REQUEST.form['namepart']">
Namepart = <dtml-var namepart><br />
<dtml-let name="namepart + '%'">

is ignored by the SQL method and I fail to see the difference.
The <dtml-if name> seems to be false in any case.

Kind regards

Andreas.

--
http://fam-tille.de
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db
Re: dtml-let variables in sql queries [ In reply to ]
dev101 at magma
Nov 23, 2006, 8:27 AM
Post #4 of 6 (4848 views)
Permalink
----- Original Message -----
From: "Andreas Tille" <tillea@rki.de>
To: "Jonathan" <dev101@magma.ca>
Cc: <zope-db@zope.org>
Sent: Thursday, November 23, 2006 11:03 AM
Subject: Re: dtml-let variables in sql queries


> On Thu, 23 Nov 2006, Jonathan wrote:
>
>>> <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>
>>
>> First off, i would highly recommend using python scripts instead of DTML
>> for the kind of processing you have described.
>
> You are right here and I would definitely prefer Python scripts. The
> problem is that I'm using the Formulator Product and despite there are
> descriptions how to use it with Python scripts I failed while it was
> very straigtforeward to use DTML (in fact, this is the only part of
> my application in DTML). I just stripped down the application to a
> very simple example and avoided Formulator magic which is obviousely
> not the source of the problem.
>
>> That said, you have not described the GetCases method.
>
> I forgot to mention the name. It is the code snipped above.
>
>> If GetCases is the name of your SQL method, then you need to pass in the
>> parameter 'nameid'
>
> Well, nameid is not explicitely passed but taken from the parent
> out of
>
> <dtml-let nameid="REQUEST.form['nameid']">
>
> and this works. The problem is that name which is builded
> out of
>
> <dtml-let namepart="REQUEST.form['namepart']">
> Namepart = <dtml-var namepart><br />
> <dtml-let name="namepart + '%'">
>
> is ignored by the SQL method and I fail to see the difference.
> The <dtml-if name> seems to be false in any case.

It may be due to the fact that the 'let' statement creates a variable in
another part of the 'namespace' (ie. not in the REQUEST namespace). You
could test this theory by replacing the dtml-let with a dtml-call
REQUEST.set

eg.
<dtml-let namepart="REQUEST.form['namepart']">
Namepart = <dtml-var namepart><br />
<dtml-call "REQUEST.set('name', namepart + '%')">


hth

Jonathan



_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db
Re: Re: dtml-let variables in sql queries [ In reply to ]
charlie at egenix
Nov 23, 2006, 8:35 AM
Post #5 of 6 (4833 views)
Permalink
Am 23.11.2006, 17:03 Uhr, schrieb Andreas Tille <tillea@rki.de>:

> You are right here and I would definitely prefer Python scripts. The
> problem is that I'm using the Formulator Product and despite there are
> descriptions how to use it with Python scripts I failed while it was
> very straigtforeward to use DTML (in fact, this is the only part of
> my application in DTML). I just stripped down the application to a
> very simple example and avoided Formulator magic which is obviousely
> not the source of the problem.

It's easy enough to call Formulator from within a script and extract the
results from it:

result = context.MY_FORMULATOR_THINGY.validate_all(context.REQUEST)

Make it work with a Script so you can forget all the evil DTML stuff, you
won't regret it!

Charlie
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db
Re: dtml-let variables in sql queries [ In reply to ]
dieter at handshake
Nov 23, 2006, 11:25 AM
Post #6 of 6 (4823 views)
Permalink
Andreas Tille wrote at 2006-11-23 16:06 +0100:
> ....
>Unfortunately
> <dtml-let namepart="namepart + '%'">
>does not seem to work transparently in the SQL-Method

Right.

"ZSQL Method"s take their parameters either from explicitly passed
keyword arguments or from the request but not from the DTML namespace.



--
Dieter
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

©2024 GT.net. A Gossamer Threads company.
5th Floor, 455 Granville St., Vancouver, BC V6C 1T1, Canada | Legal