[Zope-DB] Using multiple in zsql where clause?

Jeff Kowalczyk jtk@yahoo.com
Tue, 19 Nov 2002 14:36:29 -0500


I have a (with a patch for <dtml-comma> and <dtml-sqltest set> support) and I'm having
trouble using the 'multiple' attribute in my where clause to achieve the final bit of
functionality, batch-applying of update values.

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

Which when passed two items in the 'test' form, generates:
update Shipments set
ShipperChargeAsBilled = 23.45
where ShipmentID = '12345, 54321'

And similarly for a 'A','B' and ['A','B'] fails, as does substituting type="string", and a
call from a simple script

container.sqlupdate(ShipmentID=['12345', '54321'], ShipperChargeAsBilled=54.32)

gives no result. What I need it to do is:

update Shipments set
ShipperChargeAsBilled = 23.45
where ShipmentID IN('12345', '54321')

for multiple values, and for single values:

update Shipments set
ShipperChargeAsBilled = 23.45
where ShipmentID = '12345'

limitation of 'multiple' in the where clause, am I using it wrong, or is the ZSQL Method's
test form not preparing a string list from its input?

Thanks.