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

[Jeff Kowalczyk]

I need to call a ZSQLMethod from script with parameters that update fields with NULL.
The SQL is designed to update any field that is passed with a non-blank parameter, and it
works great for that purpose.

I'm using Zope 2.5.1, ZODBCDA/mxODBC, SQL Server 2000, sql-comma patch.

ZSQLMethod sqlUpdateShipment:
-----------------------------
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>

Python Script dispatchShipmentList:
----------------------------------
...
if REQUEST.get('cmdApplyShipperInvoiceID',''):
    # call the sql batch update command with a list of ShipmentIDs and a ShipperInvoiceID
to apply to each

container.sqlUpdateShipment(ShipmentID=REQUEST.get('ids',''),ShipperInvoiceID=REQUEST.get(
'ShipperInvoiceID',None))
    return container.shipmentList(REQUEST)
if REQUEST.get('cmdApplyShipperChargeAsBilled',''):
    # call the sql batch update command with a list of ShipmentIDs and a ShipperInvoiceID
to apply to each

container.sqlUpdateShipment(ShipmentID=REQUEST.get('ids',''),ShipperChargeAsBilled=REQUEST
.get('ShipperChargeAsBilled',None))
    return container.shipmentList(REQUEST)
...

Input section of Page Template shipmentList:
--------------------------------------------
<p>Invoice Number: <input type="text" name="ShipperInvoiceID"
tal:attributes="value ShipperInvoiceID|python:None" />
<input type="submit" name="cmdApplyShipperInvoiceID"
value="Apply to Selected" /></p>
<p>Shipper Charge: <input type="text" name="ShipperChargeAsBilled"
tal:attributes="value ShipperChargeAsBilled|python:None" />
<input type="submit" name="cmdApplyShipperChargeAsBilled"
value="Apply to Selected" /></p>

When I try to use the 'Apply to Selected' commands with blank input boxes,
I get the following SQL error.

Error Type: ProgrammingError
Error Value: ('42000', 156, "[Microsoft][ODBC SQL Server Driver][SQL Server]
Incorrect syntax near the keyword 'where'.", 6022)

Because the SQL renders as:
----------------------------
update Shipments
where
ShipmentID = '12345ABCDEF'

Which has little to do with the nonstandard sql-comma, but
rather the (expected) failure of <dtml-sqltest>, afaict.

What kind of parameter value can I pass my ZSQLMethod to explicitly force a NULL update?

Will I need to litter the SQL statement with <dtml-if> tests?

Since I'm using a customized ZSQL (with dtml-comma) anyway, should I be looking
at the possibility of extending 'optional' to recognize a string of NULL?

Thanks.