[Zope] sqlgroup problems

[Dario Lopez-Kästen]

Hello!

I need help with the following SQL-method, which I don't know if it is a
bug, or if it is my mistake.

I am building a search interface to a database (Zope 2.2.4 w all HF's,
Oracle 8.1.7, Solaris), and I want to use the following sqlmethod, to let
the user enter any combination of search parameters, including none:

select * from ladok.kurs
<dtml-sqlgroup where>
  <dtml-sqltest kurs_kod column="kod"
   op="like" type="string" optional>
<dtml-and>
  <dtml-sqltest kurs_namn column="benamns"
   op="like" type="string" optional>
</dtml-sqlgroup>

From the Zope Book, Chapter 10, I got the impression that the "optional"
part of the sqltest tag would render the parts that where not null; i.e. if
I only specify the kurs_namn variable (first sqltest), the method would
render the following SQL:

  select * from ladok.kurs
  where
  (kod like 'XX')

or, if no search parameters where specified, it would render the following
SQL:

  select * from ladok.kurs

However this is not the case, what I get if I enter XX for kurs_kod is the
following SQL:

select * from ladok.kurs
where
(kod like 'XX'
 and benamns like ''
)

Is this expected behaviour or have I misunderstood the docs? I can get
around this problem by doing something like this (notice the op tag and the
extra ",'%')" string at the end of each sqltest line) :

  <dtml-sqltest kurs_kod column="kod"
   op="like nvl(" type="string" optional>, '%')

which renders the following SQL (the "%" is the Oracle wildcard operator):

select * from ladok.kurs
where
(kod like nvl( 'XX', '%')
 and benamns like nvl( '', '%')
)

however, i'd much rather that it didn't render the null variables at all.

If this is known behaviour then the examples in Chapter 10

Any help appreciated. I searched the archives but didn't find any answers,

TIA,

/dario

- --------------------------------------------------------------------
Dario Lopez-Kästen     Systems Developer  Chalmers Univ. of Technology
dario@ita.chalmers.se  ICQ will yield no hits    IT Systems & Services