[Zope-DB] Easier updates with <dtml-sqlgroup set> and <dtml-comma>?

Jeff Kowalczyk jtk@yahoo.com
Tue, 19 Nov 2002 11:55:05 -0500


I'm writing some ZSQL methods to update tables with optional keyword parameters. This
example works, but it doesn't seem like a very efficient way to write these types of
statements:

#ShipmentID=12345
#ShipperInvoiceID
#ShipperChargeAsBilled
#ShipperChargeAsPaid

update Shipments
<dtml-sqlgroup>set
<dtml-sqltest ShipperInvoiceID type=nb optional><dtml-if ShipperInvoiceID>, </dtml-if>
<dtml-sqltest ShipperChargeAsBilled type=float optional><dtml-if ShipperChargeAsBilled>,
</dtml-if>
<dtml-sqltest ShipperChargeAsPaid type=float optional>
</dtml-sqlgroup>
where ShipmentID = <dtml-sqlvar ShipmentID type=nb>

Is there any way to do this sort of thing more directly using DTML?
Something like this (wishful) would be nice:

update Shipments
<dtml-sqlgroup set>
<dtml-sqltest ShipperInvoiceID type=nb optional><dtml-comma>
<dtml-sqltest ShipperChargeAsBilled type=float optional><dtml-comma>
<dtml-sqltest ShipperChargeAsPaid type=float optional>
</dtml-sqlgroup>
where ShipmentID = <dtml-sqlvar ShipmentID type=nb>

Or even better, if the 'set' attribute of dtml-sqlgroup did the right comma separating
thing on its own:

Also, what's the best way to guard against the possibility that all parameters are empty,
which would be a SQL syntax error (no 'set' between 'update' and 'where'. My calling
python script guards this presently. Thanks.