[Zope] SQL query from "Multiple Selection Field" (was: [Zope] [Newbie] ZSQL problem) (was: [Zope] [Newbie] ZSQL problem)

[Dieter Maurer]

Hi Michal,

Michal Seta writes:
 > ... query
 >    "select ... where col = <dtml-sqlvar multiple_section type=string>"
 > works for single value but not for multiple selection ...

This is difficult indeed, as you have to fight
various strange behaviours.

First, it is easy to explain, why your query does not work:

  When you select more then one value from the selection
  list, then "multiple_section" will be a list.
  Your "sqlvar ... type=string" converts this into a string
  that looks like "[ 'a', 'b', 'c']".
  This string is then compared to your database column.
  You easily see, the result will be false.

Your SQL query is not correct to search for a value
in a list of possible values. You must use something
like

     select ... where col in ( 'a', 'b', 'c' )

How to construct such a query?
If you know, that your values do not contain quotes (i.e. '),
then you can use "_.string.join" like this:

     select ... where
     col in ( <dtml-var "_.string.join(multiple_selection,',')"> )

Now, your code will work, when you select more than one value
from the list. It will fail, if you select a single item
(or none at all).

It fails for a single selected value, because in this case
"multiple_selection" is not a list but a single value.
Fortunately, if you put the ":list" suffix at the form
variable name (like "<select name="multiple_selection:list" multiple ...>"),
then you tell ZPublisher, that you want this to be a list.
In this case, you will get a list with one value.
This works well...

... up to the case, where no value is selected.


In this case, the variable is completely missing from the
request. You probably will get a "Bad request; missing
arguments ['multiple_selection']" from your ZSQL method.

In your case, it is probably best, to catch this case
in the DTML code that handles your form and report a nice
error.


After you became more familiar with Zope and the rough edges
of HTML form processing, you will hit cases where the above
approach is not adequate.
You can then ask again in the list (or search the searchable
list archive).


Dieter