[Zope-dev] Z SQL: "optional" isn't?

[jpenny@universal-fasteners.com]

On Fri, May 26, 2000 at 02:59:38PM -0500, Kyler B. Laird wrote:
> 
> I'd like to build a Z SQL method for *many*
> people to use to list courses that we offer.
> I don't want to require that all queries
> using it understand all of the columns, but
> I do want to be able to modify the query in
> many ways.
> 
> So...after reading
> 	http://yyy.zope.org/Documentation/Guides/ZSQL-HTML/ZSQL.1.5.3.html
> 	optional 
> 
> 	A flag indicating if the test is optional.
> 	If the test is optional and no value is 
> 	provided for a variable, or the value
> 	provided is an invalid empty string, then
> 	no text is inserted.
> I thought I could just use "optional"
> with all of my sqltests.
> 	select * from course_catalog
> 	
> 	<dtml-sqlgroup where>
> 		<dtml-sqltest subject type=nb optional>
> 		<dtml-and>
> 		<dtml-sqltest course_nbr type=nb optional>
> 		<dtml-and>
> 		<dtml-sqltest version type=nb optional>
> 		<dtml-and>
> 		<dtml-sqltest campus_code type=nb optional>
> 	</dtml-sqlgroup>

I have never used that corner of ZSQL methods.  I have a guesses.
I think that the method is working fine, but the nature of HTTP
posting is giving you trouble.  Remember, a REQUEST typically only
contains non-null fields.

There are three suggestions on fixing this. 

1)  preseed the calling form by putting in a
<dtml-call "REQUEST.set('subject', '')> BEFORE the form.
This way, normal acquisition will get you an empty string
even if there is no value entered in the form.

2) A bit more of a pain, but certainly doable.
Remember that you can use ordinary <dtml-if> logic
in a ZSQL method.  You can use choose among multiple
cases in a single method.  But, here you have 16 cases,
which is unpleasant.  Is it really true that ALL of these
can be simultaneosly empty?

3) Considerable more pain, with some loss of safety.
Construct the body of your method on the fly.  Pass it
in as an argument, and call that.  For certain forms, this
is necessary (if for example the user can modify the form
generation process).