[Zope] Separating assignments in Z SQL Updates

[Andy McKay]

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