[Zope] INSERT/UPDATE in SQL methods

[Evan Simpson]

Here's a quick brain-dump of some of what I've learned about ZSQL Methods, in the
hope that you (or someone else) will find it useful:

You can include any number of SQL statements, of any kind (INSERT/SELECT/UPDATE)
as long as no more than one of them is a SELECT.  You must separate them with
<!--#var sql_delimiter-->.

Getting data into a ZSQL Method can be the trickiest bit.  Consider one with id
"newmail" and the following body:

UPDATE "addressbook" SET "email" = 'me@here' WHERE "name" = 'evan'

Then <!--#call newmail--> should perform the update.  If something literal like
this doesn't work for you, let use know!  Try it!
Now we parameterize it, like so:

UPDATE "addressbook" SET "email" = '<!--#var email sql_quote-->' WHERE "name" =
<!--#sqlvar name type=string-->

The first thing to note here is that you can use either "var" or "sqlvar" as you
like; "sqlvar" is a convenience which abstracts away some differences between
SQL backends, and should usually be preferred.  The second thing is unless we
list "name" and "email" in the parameter list, request data will *not* be
searched for their values.  This case is only useful if you want to provide the
information from context, say by calling it from two documents with properties
"name" and "email".

Most often, you will want the values for your parameters to come from request
data (a submitted form, environment variables, etc.)  In this case you *must*
include them in the ZSQL Method's parameter list.  If your parameter list is
"name email", and REQUEST contains "name" and "email" values, then you can do the
following:

<!--#call newmail-->
or
<!--#call "newmail(name=name, email=email)"-->

The preceding line may look funny, but it's really straightforward.  The left
side of each '=' is a parameter name, while the right side is an expression
evaluated in the current context, which happens here to be a simple name. If the
values aren't already in REQUEST, you could:

<!--#call "newmail(name='evan', email='me@here')"-->
or
<!--#call "REQUEST.set('name', 'evan')"-->
<!--#call "REQUEST.set('email', 'me@here')"-->
<!--#call newmail-->
or
<!--#with "_.namespace(name='evan', email='me@here')"-->
  <!--#call newmail-->
<!--#/with-->

I personally prefer to load the REQUEST with my data, then perform a simple
<!--#call sql_method-->.  Enough about parameters.

You can use all of the usual DTML tags to construct your ZSQL Method, including
'if', 'in', 'var' and 'call'.  Several common idioms you might wish to construct
are much easier with 'sqlgroup', 'sqltest', and 'sqlvar', though, and they're
pretty well documented.

Well, that's all I care to write for the moment.  If I've made assumptions or
glossed over a subtle area, please let me know.

Evan Simpson