multiple optional args to zsql method?
I have this ZSQL method: <params> id zope_username </params> select * from worker <dtml-sqlgroup where> <dtml-sqltest name="id" op="eq" type="int" optional> <dtml-or> <dtml-sqltest name="zope_username" op="eq" type="string" optional> </dtml-sqlgroup> ; When I test it but give no parameters it executes this SQL: select * from worker where zope_username = '' ; Since neither id nor zope_username were given shouldn't it have executed select * from worker ; ? I also tried using <dtml-and> instead of <dtml-or>. Same-o, Same-o. In addition, if I call it as context.sql.get_worker(zope_username=user.getUserName()) from a Python script, it complains "Invalid integer value for id". I'm flummoxed. -- Skip Montanaro - skip@pobox.com http://www.mojam.com/ http://www.musi-cal.com/
Hello I'm never using this sqlgroup parameter. Depending on your SQL dialect (I'm using Oracle), you can do something like this: SELECT columna FROM table WHERE columnb = NVL(<dtml-sqlvar columnb type=int optional>,columnb) OR columnc = NVL(<dtml-sqlvar columnc type=int optional>,columnc) So it will produce (when both columns are empty) SELECT columna FROM table WHERE columnb = columnb OR columnc = columnc which will give the same result as SELECT columna FROM table HTH Dieter
-----Ursprüngliche Nachricht----- Von: zope-admin@zope.org [mailto:zope-admin@zope.org]Im Auftrag von Skip Montanaro Gesendet am: Montag, 25. November 2002 20:02 An: zope@zope.org Betreff: [Zope] multiple optional args to zsql method?
I have this ZSQL method:
<params> id zope_username </params> select * from worker <dtml-sqlgroup where> <dtml-sqltest name="id" op="eq" type="int" optional> <dtml-or> <dtml-sqltest name="zope_username" op="eq" type="string" optional> </dtml-sqlgroup> ;
When I test it but give no parameters it executes this SQL:
select * from worker where zope_username = '' ;
Since neither id nor zope_username were given shouldn't it have executed
select * from worker ;
? I also tried using <dtml-and> instead of <dtml-or>. Same-o, Same-o.
In addition, if I call it as
context.sql.get_worker(zope_username=user.getUserName())
from a Python script, it complains "Invalid integer value for id".
I'm flummoxed.
-- Skip Montanaro - skip@pobox.com http://www.mojam.com/ http://www.musi-cal.com/
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Hi, I usually use a dml-if construct to do this
<dtml-sqlgroup where> <dtml-sqltest name="id" op="eq" type="int" optional> <dtml-or>
<dtml-if zope_username>
<dtml-sqltest name="zope_username" op="eq" type="string" optional>
</dtml-if>
</dtml-sqlgroup>
only problem with this approach is, that you can not test for a blank user name. In that case <dtml-if "_.has_key('zpe_username')"> should help. Ulli -- Expand to Sweden? Get local prospects. http://www.PublisherSEO.com/2 World Wide Web Publisher, Ulrich Wisser, Odensvag 13, S-14571 Norsborg http://www.publisher.de Tel: +46-8-53460905 Fax: +46-8-534 609 06
Hi, On Mon, 2002-11-25 at 20:02, Skip Montanaro wrote:
I have this ZSQL method:
<params> id zope_username </params> select * from worker <dtml-sqlgroup where> <dtml-sqltest name="id" op="eq" type="int" optional> <dtml-or> <dtml-sqltest name="zope_username" op="eq" type="string" optional> </dtml-sqlgroup> ;
I use this kind of syntax without a problem (excepted the fact that I use "<dtml-sqltest zope_username type=string optional>", so without double quotes and the 'name' field). Are you sure that "zope_username" isn't defined anywhere else in your environment ??
In addition, if I call it as
context.sql.get_worker(zope_username=user.getUserName())
from a Python script, it complains "Invalid integer value for id".
I had the same problem. I think that if a parameter is missing but exists into the environment, the SQL template is retrieving the given value from it. As "id" is always defined, changing the parameter name from "id" to anything else may solve the problem, using something like : <dtml-sqltest newid column=id type=int optional> Thierry
thierry.florac@onf.fr) wrote :
I had the same problem. I think that if a parameter is missing but exists into the environment, the SQL template is retrieving the
given
value from it. As "id" is always defined, changing the parameter name from "id" to anything else may solve the problem, using something like :
<dtml-sqltest newid column=id type=int optional>
Thierry
un grand 'merci', Thierry! I've been bashing my head against the wall all day today with this problem - a mysterious Attribute error: __int__ from the bowels of the ZRDB code, which happened in a vital piece of my code, but which I couldn't recreate. I can't even find the culprit names in my code or environment anywhere. Should this be reported as some kind of bug, or at least issue? I'd expect *only* the parameters explicitly passed into the ZSQL method to have effect, as I use generic zsql methods to handle as many different queries as possible. I really don't want to have to worry about what else could be in my environment overriding optional values deliberately not passed. thanks again Ben Avery
Skip Montanaro writes:
I have this ZSQL method:
<params> id zope_username </params> select * from worker <dtml-sqlgroup where> <dtml-sqltest name="id" op="eq" type="int" optional> <dtml-or> <dtml-sqltest name="zope_username" op="eq" type="string" optional> </dtml-sqlgroup> ;
When I test it but give no parameters it executes this SQL:
select * from worker where zope_username = '' ;
Since neither id nor zope_username were given shouldn't it have executed
select * from worker ; "optional" suppresses the "sqltest" when the value cannot be converted into the target type.
The empty id cannot be converted into "int" (--> suppression) but the empty "zope_username" is a perfect string. Use "type=nb" (for "non-blank") to suppress emty string tests.
.... In addition, if I call it as
context.sql.get_worker(zope_username=user.getUserName())
from a Python script, it complains "Invalid integer value for id". As you do not pass the "id" parameter, it acquires it.
As you can see, the acquired "id" is not an integer, which is not really a surprise. Either define a default value for "id" or pass it explicitely. I agree that Z SQL methods should not implicitly acquire parameters: You should have gotten a "Bad request" exception: "missing parameter id". Please file a bug report to the collector (<http://collector.zope.org>). Dieter
participants (6)
-
ben@thesite.org -
Dieter Fischer -
Dieter Maurer -
Skip Montanaro -
Thierry Florac -
Ulrich Wisser