[Zope] Best way to handle SQL update with variable number of fields?

[Dieter Maurer]

chris@ncafe.com writes:
 > The problem - I would like to populate the form with the existing 
 > values and then allow the user to change the ones they want to. Some 
 > of the fields in the database are empty and it will always be that 
 > way. The length of the update statement will change.
I do not understand, why you would want a varying length update.

If you populated the form with the current values and
the user changes some of them, then the form contains the
current values with respect to all fields it shows.
Thus, you can update them in a single fixed length update.

In cases, when I do require (or want) variable length updates
(or inserts) I use code like the following
(the code uses a naming scheme to select form variables that
should update the database: variables with names ending in "_"):

DTML:
	<dtml-let update_list="[]">
	  <dtml-in "REQUEST.form.items()"> <!-- or some other sequence -->
	    <dtml-let name=sequence-key value=sequence-item>
	      <dtml-if "name[-1:] == '_'"> <!-- or a different test -->
	        <dtml-call "update_dict.append((name,value))>
              </dtml-if
	    </dtml-let>
	  </dtml-in>
	  <dtml-call "SQLupdate(items=update_dict)">
	</dtml-let>

SQLupdate:
Arguments:	items
Template:
	<dtml-unless items><dtml-return "0"></dtml-unless>
	update TABLE set
	  <dtml-in items>
	    <dtml-var sequence-key> = <dtml-sqlvar sequence-item type=string>
	      <!-- this works only for string fields
	           for other type, you either may branch according to
		   type or use an external method that presents
		   the value in a correct way based on its type -->
	    <dtml-unless sequence-end>, </dtml-unless>
	  </dtml-in>


Dieter