[Zope-DB] Easier updates with <dtml-sqlgroup set> and <dtml-comma>?

[Ben Avery]

the product below has a couple of patches to the sqlgroup source code, 
which allows you to do exactly what you're looking for, and which has 
saved me already!

[from my previous answer on the zope@zope.org list:
http://zope.nipltd.com/public/lists/zope-archive.nsf/0dec1f578f18f116802568ab003585d2/a33874797530042880256c3f00370583?OpenDocument
]

http://www.zope.org/Members/adustman/Products/SQLBlender
which is like znolk, but adds a couple of nice things into sqlgroup to
let you do things like

update aardvark
<dtml-sqlgroup set noparens>
<dtml-sqltest name type="string" op="eq">
<dtml-comma>
<dtml-sqltest length type="int" op="eq" optional>
<dtml-comma>
<dtml-sqltest age type="int" op="eq" optional>
</dtml-sqlgroup>
where aard_id = <dtml-sqlvar id type="int">

to build flexible update statements to match whatever parameters you
pass in.


- I just hope you have write access to the source code! :)

Ben


Jeff Kowalczyk wrote:
> I'm writing some ZSQL methods to update tables with optional keyword parameters. This
> example works, but it doesn't seem like a very efficient way to write these types of
> statements:
> 
> #ShipmentID=12345
> #ShipperInvoiceID
> #ShipperChargeAsBilled
> #ShipperChargeAsPaid
> 
> update Shipments
> <dtml-sqlgroup>set
> <dtml-sqltest ShipperInvoiceID type=nb optional><dtml-if ShipperInvoiceID>, </dtml-if>
> <dtml-sqltest ShipperChargeAsBilled type=float optional><dtml-if ShipperChargeAsBilled>,
> </dtml-if>
> <dtml-sqltest ShipperChargeAsPaid type=float optional>
> </dtml-sqlgroup>
> where ShipmentID = <dtml-sqlvar ShipmentID type=nb>
> 
> Is there any way to do this sort of thing more directly using DTML?
> Something like this (wishful) would be nice:
> 
> update Shipments
> <dtml-sqlgroup set>
> <dtml-sqltest ShipperInvoiceID type=nb optional><dtml-comma>
> <dtml-sqltest ShipperChargeAsBilled type=float optional><dtml-comma>
> <dtml-sqltest ShipperChargeAsPaid type=float optional>
> </dtml-sqlgroup>
> where ShipmentID = <dtml-sqlvar ShipmentID type=nb>
> 
> Or even better, if the 'set' attribute of dtml-sqlgroup did the right comma separating
> thing on its own:
> 
> Also, what's the best way to guard against the possibility that all parameters are empty,
> which would be a SQL syntax error (no 'set' between 'update' and 'where'. My calling
> python script guards this presently. Thanks.
> 
> 
> 
> 
> 
> 
> _______________________________________________
> Zope-DB mailing list
> Zope-DB@zope.org
> http://lists.zope.org/mailman/listinfo/zope-db
>