[Zope-DB] Re: parameter to force NULL update in dtml-sqltest?

[Jeff Kowalczyk]

update Shipments
<dtml-sqlgroup set noparens>
<dtml-sqltest ShipperInvoiceID type="nb" op="eq" optional>
<dtml-comma>
<dtml-sqltest ShipperChargeAsBilled type="float" op="eq" optional>
</dtml-sqlgroup>
where
<dtml-sqltest ShipmentID type="string" op="eq" multiple>

[DM] Do not use "sqltest" when you want to assign "NULL" values
(you are abusing "sqltest" inside the "update ... where").

[JTK] My attempt to shorten the example ZSQL may have obscured its
true purpose. In actual use, it is a general update statement for
one or more fields of a 30-column table. The sqltest is there to
allow the parameters which are not set in the method call to simply
fall out of the set clause, and receive no update at all. This is
the way I prefer to re-use update statements for wide tables. Works
perfectly, but I haven't figured out this Null update case yet in
ZSQL.

The origin of this approach is that as I worked with wider tables,
I found it necessary to deviate from the 'compare field_update
to field_original' approach used in many SQL server update stored
prodedures, it was entirely too much work to pass 2*Columns number
of parameters to a Data Adapter method from C#, which lacks default
keyword arguments. (I still can't believe that they left that out!?)

I'm trying to formulate an approach, further customizing ZSQL if
necessary (in addition to the dtml-comma tag in sqlgroup.py), that
would support this idiom with a clear minimalist syntax. I *can*
do an if-else 30 times in one method, but it seems like I should be
thinking of a better adaptation for the tool to do all that
boilerplate for me.

Thanks.


[DM]
<dtml-sqlgroup set noparens>
ShipperInvoiceID =
<dtml-if expr="ShipperInvoideID is None">null
<dtml-else><dtml-sqlvar ShipperInvoiceID type=string>
</dtml-if>
<dtml-comma>
...
</dtml-sqlgroup>
...
when you want to assign "null" values.
Dieter