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

Jeff Kowalczyk jtk@yahoo.com
Fri, 20 Dec 2002 14:35:18 -0500

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-sqltest ShipperChargeAsBilled type="float" op="eq" optional>
<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

    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

    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
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?