Heres one solution: In a python script / external method it is very easy to prepare sql in python and then add it into the REQUEST as a string called say sql_extra. I prepare the a skeletal ZSQL statement along the lines of: UPDATE Foo SET <dtml-var sql_extra> sql_extra will be pulled from the REQUEST. Doing it in python is your best bet. Cheers. -- Andy McKay. ----- Original Message ----- From: "Bruce Eckel" <Bruce@EckelObjects.com> To: "password horpz" <zope@zope.org> Sent: Monday, May 14, 2001 9:55 AM Subject: [Zope] Separating assignments in Z SQL Updates
I'm trying to write a ZSQL method that will do a general update to a record. I started out attempting to do what I thought was the 'normal' thing:
update Table set name1='value1', name2='value2', name3='value3' [...] where id='idvalue';
I wanted to use dtml to automatically build a statement that would only assign values if new values existed. The problem with this is the commas: you have to have commas between the assignments, but you can have a comma after the last assignment. And I couldn't figure out how to conditionally insert the commas.
After fooling around and hunting for awhile, the best I could come up with is a separate 'update' statement inside an 'if', for each field. So it looks like this:
<dtml-if due_date> update Invoices set due_date = <dtml-sqlvar due_date type="string"> where <dtml-sqltest invoice_id op=eq type=string><dtml-var sql_delimiter> </dtml-if> <dtml-if purchase_order_id> update Invoices set purchase_order_id = <dtml-sqlvar purchase_order_id type="string"> where <dtml-sqltest invoice_id op=eq type=string><dtml-var sql_delimiter> </dtml-if> <dtml-if ship_name> update Invoices set ship_name = <dtml-sqlvar ship_name type="string"> where <dtml-sqltest invoice_id op=eq type=string><dtml-var sql_delimiter> </dtml-if>
This works, but it seems awfully awkward to me. Is there a way to insert commas instead, or a better solution in general for this problem?
Thanks. Bruce
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )