[Zope] confused about optional args to ZSQL methods

[Dieter Maurer]

Skip Montanaro writes:
 > I think I may have encountered this problem before and asked about it.  If
 > so, any solutions offered at that time escape me now.
The mailing lists are archived and searchable via Google ;-)

   This means, when you miss something, you can easily find it again...

 > I have this ZSQL
 > method (get_workorder):
 > 
 >     <params>id
 >     worker_id
 >     end_date</params>
 >     select * from work_order
 >       <dtml-sqlgroup where>
 >         <dtml-sqltest name=id op=eq type=int optional>
 >       <dtml-and>
 >         <dtml-sqltest name=worker_id op=eq type=int optional>
 >       <dtml-and>
 >         <dtml-sqltest name=end_date op=ge type=string optional>
I answered previously:
    When you use "sqltest" with "optional", you should not
    use "type=string" but "type=nb" (non blank).

    "optional" is implemented as:
       convert the value to the required type;
       if that fails, omit the "sqltest".

    There is no problem to convert a value to a string.
    Converting to "nb" will fail, when the value is the empty string.

 > ....
 > In a Python script I want to call it like so:
 > 
 >     workorders = context.sql.get_workorder(worker_id=worker_id,
 >                                            end_date='2003-04-23')
 > 
 > that is, omit the presumably optional id parameter.
I had answered this question as well:

  Apparently, ZSQL methods acquire explicitely named parameters.
  "id" is easy to acquire (because it is almost obiquitous),
  but the problem is general.

  I consider this a bug. You may want to file a bug report
  to <http://Collector.zope.org>.

 > However, when the
 > Python script is run, the ZSQL method complains "Invalid integer value for
 > id".
Strange, this should not happen with my understanding of "optional".

I checked the implementation (Shared/DC/ZRDB/sqltest.py)
and found out that "optional" works as follows:

  *  when the requested value does not exist, "sqltest" returns ''

  *  when the value is false *and* it is a string, "sqltest" returns ''

  *  otherwise, "optional" is not effective.

Needless to say: I think, this is broken.


As a work around: pass "id=''" in your "get_workorder" method.


Dieter