DTML in ZSql Methods

Created by . Last modified on 2003/08/05.

A Problem

Suppose I have a form that accepts several inputs and has a find operation that can be permitted on the input data. For simplicity, I am going to limit myself to 2 inputs, which I am calling item_class and major_group. The input form looks like:

<form action=edit_item_classes method=post>
	<table>
		<tr><td>Item Class
		<td><input type=text width=20 name=item_class>
		<tr><td>Major Group
		<td><input type=text width=20 name=major_group>
		<tr><input type=button name=action value=find>
	</table>
</form>

Which renders as:

Item Class
Major Group

The Question: How do I write the ZSQL method that acquires the data and the DTML Method that displays it?

I will show two alternatives below, and let you choose according to your taste.

Alternative 1: Simple ZSQL Methods and More Complex DTML Method

Here we will need 3 simple ZSQL Methods and a DTML emthod that looks like:

	<dtml-elif "REQUEST['action'] == 'find'">
	  <dtml-call "REQUEST.set('major_group', _.string.upper(REQUEST['major_group']))">
	  <dtml-call "REQUEST.set('item_class', _.string.upper(REQUEST['item_class']))">
	  <dtml-if "major_group == '' and item_class == ''">
   	    <P>I need a Major Group or an Item Class (or both) before I can
		perform the find.
	  <dtml-elif "major_group <> '' and item_class <> ''">>
	    <dtml-in find_given_both start=query_start>
	      <form action=<dtml-var "URL0"> method=post>
		<table>
	 	  <TR><TD><B>Item Class: </b>
		    <TD><input type=text size=2 name=styleCode value="<dtml-var "item_class">">
		  <TR><TD><B>Major Group: </b>
		    <TD><input type=text size=15 name=major_group value="<dtml-var "lower_post">">
	        </table>
		<table>
		  <TR><TD><B>Actions:</b>
		  <TD><input type=submit name=action value="find">
		</table>
	      </dtml-in>				
	  <dtml-elif "major_group <> ''>
	    <dtml-in find_given_major_group start=query_start>
	      <form action=<dtml-var "URL0"> method=post>
		<table>
	 	  <TR><TD><B>Item Class: </b>
		    <TD><input type=text size=2 name=styleCode value="<dtml-var "item_class">">
		  <TR><TD><B>Major Group: </b>
		    <TD><input type=text size=15 name=major_group value="<dtml-var "lower_post">">
	        </table>
		<table>
		  <TR><TD><B>Actions:</b>
		  <TD><input type=submit name=action value="find">
		</table>
	      </dtml-in>
	  <dtml-else>
	    <dtml-in find_given_item_class start=query_start>
	      <form action=<dtml-var "URL0"> method=post>
		<table>
	 	  <TR><TD><B>Item Class: </b>
		    <TD><input type=text size=2 name=styleCode value="<dtml-var "item_class">">
		  <TR><TD><B>Major Group: </b>
		    <TD><input type=text size=15 name=major_group value="<dtml-var "lower_post">">
	        </table>
		<table>
		  <TR><TD><B>Actions:</b>
		  <TD><input type=submit name=action value="find">
		</table>
	      </dtml-in>				
	</dtml-if>

ZSQL Method find_given_both has arguments item_class and major_group and body:

 
           select * from item_classes
	   where
           item_class = <dtml-sqlvar item_class type=string>
           and
	   major_group = <dtml-sqlvar major_group type=string>

ZSQL Method find_given_item_class has only argument item_class and body:

 
           select * from item_classes
	   where
           item_class = <dtml-sqlvar item_class type=string>

ZSQL Method find_given_major_group has only argument and major_group and body:

 
           select * from item_classes
	   where
	   major_group = <dtml-sqlvar major_group type=string>

Alternative 2: More Complex ZSQL Method and Simple DTML Method

Here I will define a single ZSQL method that contains DTML level logic, and a simpler DTML method. The portion of the DTML method that handles find looks like:

	<dtml-elif "REQUEST['action'] == 'find'">
	  <dtml-call "REQUEST.set('major_group', _.string.upper(REQUEST['major_group']))">
	  <dtml-call "REQUEST.set('item_class', _.string.upper(REQUEST['item_class']))">
	  <dtml-if "major_group == '' and item_class == ''">
   	    <P>I need a Major Group or an Item Class (or both) before I can
		perform the find.
	  <dtml-else>
	    <dtml-in find start=query_start>
	      <form action=<dtml-var "URL0"> method=post>
		<table>
	 	  <TR><TD><B>Item Class: </b>
		    <TD><input type=text size=2 name=styleCode value="<dtml-var "item_class">">
		  <TR><TD><B>Major Group: </b>
		    <TD><input type=text size=15 name=major_group value="<dtml-var "lower_post">">
	        </table>
		<table>
		  <TR><TD><B>Actions:</b>
		  <TD><input type=submit name=action value="find">
		</table>
	      </dtml-in>				
	    </dtml-if>
	</dtml-if>

There is a single ZSQL method in this alternative. In my case, it is simply called find. It has arguments major_group and item_class and body:

	<dtml-if "major_group <> '' and item_class <> ''">
	   select * from item_classes
	   where
           item_class = <dtml-sqlvar item_class type=string>
           and
	   major_group = <dtml-sqlvar major_group type=string>	
	<dtml-elif "major_group <> ''">
	  select * from item_classes
	   where
	   major_group = <dtml-sqlvar major_group type=string>
	<dtml-else>
	  select * from item_classes
	   where
	   item_class = <dtml-sqlvar item_class type=string>	    
    	</dtml-if>

Note how much simpler the logic of the DTML method is. Also note that ZSQL methods do permit DTML inside their body. However, note that only one select statement should be executed under any possible logic combination, you will get back only a single res structure, and at best, the results of previous selects will be discarded.

A warning

This is trickier reading than you may think; item_class and major_group are being heavily overloaded. At times, each refers to an element of a HTML form, a member of the REQUEST dictionary, a formal parameter, and a datum in a column of a database (via a res structure). Read carefully, and you will be far along in the Zen of ZSQL methods when you have a clear grasp of which is which. (Your code will actually be far easier to understand if you take advantage of this overloading and do not use different variable names for each context.)