At 04:00 PM 8/16/99 -0700, Bradford Hull wrote:
Try this... let's say your INSERT or UPDATE method is called 'do_update'... Your DTML would look like:
<!--#call "do_update(param1=var1,param2=var2)"-->
That's basically it. Use a #call tag and put double quotes around a function call to the ZSQL method, passing in as keyword parameters all the variables that it needs to do the update or insert.
This is probably the information I need right now, except that there's severe ambiguity in it if I don't already know how to refer to the args. That is to say, at the point in the routine where I have to do this (which you didn't tell me I need to write, by the way, and neither did the docs, but Martijn Faassen did) where I put this call, there are only 2 ways I can think of to try to get the value for my parameter: <dtml-call "add_resp(email=email)"> or <dtml-call "add_resp(email=<dtml-var email>)">
The first fails when I run it, which seems reasonable as silly as it looks, and the other gets a compile-time error when I try to store the edited script. No other expression in 50-75 tries has actually stored a value in my database yet, though many have run without throwing an exception or error.
What error does it fail with? I must have missed your original question on this. Could you repost the methods in question? By the way, your first example is the 'correct' one (i.e., the one that 'should' work). You cannot nest DTML tags within themselves. Two more questions: does add_resp work if you use its 'Test' tab? Is 'email' the only variable referenced in the SQL, or are there others?
See how easy it is to be ambiguous, when you know the turf and your reader does not? Thanks for the tips, and if you can clarify the point above I'll thank you again, because now I'm so close I can smell database update...
It does work, believe me. Up until about this time last year, I had a pretty major app running under an older Zope that did everything through SQL methods: insert, update, delete, you name it. (I eventually had to move it off to a custom ZPublisher-based engine due to Zope's single-threaded, single-process nature at the time.)
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
At 04:38 17/08/99 , Evan Simpson wrote:
UPDATE "addressbook" SET "email" = '<!--#var email sql_quote-->' WHERE "name" = <!--#sqlvar name type=string-->
Wow, don't confuse poor Bradford, who has been hassled enough I am sure, with erroneous code as well! Above line should read: UPDATE addressbook SET email = <!--#var email sql_quote--> WHERE name = <!--#sqlvar name=name type=string--> because sql_quote will add the quotes for you, you don't have to do this yourself, and 'name' is not only the name of the variable you want to insert, but also the name of an attribute of the sqlvar tag. Better would be using sqltest in stead of name = <!--#sqlvar-->: UPDATE addressbook SET email = <!--#sqlvar email type=string--> WHERE <!--#sqltest name=name type=string--> because sqltest also knows how to handle the case where 'name' contains a list (it will do a "name IN ('list', 'of' 'values')" instead of a "name='1value'"). -- Martijn Pieters, Web Developer | Antraciet http://www.antraciet.nl | Tel: +31-35-7502100 Fax: +31-35-7502111 | mailto:mj@antraciet.nl http://www.antraciet.nl/~mj | PGP: http://wwwkeys.nl.pgp.net:11371/pks/lookup?op=get&search=0xA8A32149 ------------------------------------------
At 12:08 PM 8/17/99 +0200, Martijn Pieters wrote:
At 04:38 17/08/99 , Evan Simpson wrote:
UPDATE "addressbook" SET "email" = '<!--#var email sql_quote-->' WHERE "name" = <!--#sqlvar name type=string-->
Wow, don't confuse poor Bradford, who has been hassled enough I am sure, with erroneous code as well!
Above line should read:
UPDATE addressbook SET email = <!--#var email sql_quote--> WHERE name = <!--#sqlvar name=name type=string-->
because sql_quote will add the quotes for you, you don't have to do this yourself, and 'name' is not only the name of the variable you want to insert, but also the name of an attribute of the sqlvar tag.
I'm afraid you're BOTH wrong. It's: UPDATE addressbook SET email = '<!--#var email sql_quote-->' WHERE name = <!--#sqlvar name=name type=string--> or: UPDATE addressbook SET email = '<!--#var email sql_quote-->' WHERE name = '<!--#var name sql_quote-->' 'sql_quote' doesn't add quotes; it only escapes embedded quotes.
Martijn Pieters wrote: [Corrections to my sea of mis-coding] Urgh. That should teach me to send off untested, off-the-top-of-my-head code.
Better would be using sqltest in stead of name = <!--#sqlvar-->:
UPDATE addressbook SET email = <!--#sqlvar email type=string--> WHERE <!--#sqltest name=name type=string-->
Sure enough. The 'sql.*' tags should be used wherever possible, for the reason you mention, among others. And thanks for the kind word, Brad. I hope this means you've reconsidered withdrawing from the Zope community. back-to-my-PythonMethods-ly-y'rs Evan Simpson
On Mon, 16 Aug 1999, Evan Simpson wrote: [lot of useful things snipped]
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:
I want to write a general ZSQL method like this: insert into <!--#var table sql_quote upper --> ( <!--#in EinAusgabe --> <!--#unless sequence-start -->,<!--#/unless --> <!--#var name --> <!--#/in -->) values <!--#in EinAusgabe --> <!--#unless sequence-start -->,<!--#/unless --> <!--#var expr="_[name]" fmt=sql-quote-->" <!--#/in -->) Where EinAusgabe is a TinyTable describing the field for the form like this: columns: name label ftype length:int default auto data: "event_id", "Event-Nr", "int", 10, "", "max" "art", "Event-Art", "string", 20, "kino", NULL "veran_id", "Veranstalter-ID", "string", 8, NULL, NULL "veran_ort", "Ort", "string", 40, NULL, NULL "veran_title", "Titel", "string", 80, NULL, NULL "veran_link", "Link", "string", 40, "http://", NULL "datum_von", "von", "date", 10, NULL, NULL "datum_bis", "bis", "date", 10, NULL, NULL "zeiten", "Zeiten", "string", 60, NULL, NULL and nothing in the parameter list. This actually works, except, that I have to name the arguments in the list. How can I avoid that ? Can I use dtml-tags in the argument list ? Gruß Joachim Schmitz WWW-Consultant email: js@ac-copy.net tel: +49-241-89491-0 fax: +49-241-89491-29
participants (4)
-
Evan Simpson -
Joachim Schmitz -
Martijn Pieters -
Phillip J. Eby