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

[Dieter Maurer]

Jeff Kowalczyk wrote at 2002-12-20 14:35 -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-comma>
 >   <dtml-sqltest ShipperChargeAsBilled type="float" op="eq" optional>
 >  </dtml-sqlgroup>
 > where
 > <dtml-sqltest ShipmentID type="string" op="eq" multiple>
Do not use "sqltest" when you want to assign "NULL" values
(you are abusing "sqltest" inside the "update ... where").
Use:

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