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, 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

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