dtml-comma for sqlgroup in ZSQL Method

[Alfons Grabher]

For UPDATE a dtml-comma is missing in sqlgroup. I found a patch at http://www.zope.org/Members/natsukashi/Patches/zope-2.2.1-sqlgroup-p1.txt but i hate patching a running system. Would like to have something similar in Zope.

[T. Middleton (timtoo)]

ZSQL is frustrating in that it seems like such a great and convenient idea for templating SQL statements... but as soon as you start doing much besides SELECT statements one finds a lot of things missing. I too noticed this dtml-comma patch floating around for a long time. Why hasn't it been applied to the Zope distribution yet? It adds something, and harms nothing.(And if only ZSQL could handle NULLs on UPDATEs without horrible awkwardness)!

[Florent Guillaume (efge)]

For those of us not familiar with ZSQL, could you expand a bit on
"for UPDATE a dtml-comma is missing in sqlgroup".

Please state the problem in the existing code, and what the solution brings.

[Andrew Veitch (andyv)]

Imagine a ZSQL method called editPerson(firstname, surname, age)

It would be useful if it could be called as editPerson(firstname='Bill') or editPerson(age=99) or editPerson(surname='Gates', age=19) but this is quite tricky to render using the existing ZSQL methods. You would have to do something like:

arguments:firstname surname age
UPDATE person SET
id=id
<dtml-if firstname>,firstname=<dtml-sqlvar firstname type=string></dtml-if>
<dtml-if surname>,surname=<dtml-sqlvar surname type=string></dtml-if>
<dtml-if age>,age=<dtml-sqlvar age type=int></dtml-if>

Note the I'm assuming the table has a field called id which can be used as a dummy variable to get round the comma problem. This gets very confusing. As a further complication the scoping rules for <dtml-if> are slightly different to <dtml-sqlvar>.

This is exactly the same problem that happens with SQL where and is solved with <dtml-sqlgroup>

Using the proposed patch (from August 2000!) the above code could be simplified to:

UPDATE person
<dtml-sqlgroup set>
  firstname=<dtml-sqlvar firstname type=nb>
<dtml-comma>
  surname=<dtml-sqlvar surname type=nb>
<dtml-comma>
  age=<dtml-sqlvar age type=int>
</dtml-sqlgroup>

[Tres Seaver (tseaver)]

Status: Pending => Accepted

 Supporters added: tseaver

[Tres Seaver (tseaver)]

Status: Accepted => Resolved

Uploaded: collector_1118.patch

Patch updated and applied, with tests, on the 2.7 branch:

 http://cvs.zope.org/Zope/lib/python/Shared/DC/ZRDB/Attic/sqlgroup.py.diff?r1=1.9.68.1&r2=1.9.68.2
 http://cvs.zope.org/Zope/lib/python/Shared/DC/ZRDB/tests/Attic/test_sqlgroup.py.diff?r1=1.1&r2=1.1.2.1

the 2.8 branch:

 http://svn.zope.org/Zope/?rev=38487&view=rev

and the trunk:

 http://svn.zope.org/Zope/?rev=38488&view=rev