Charlie ~ I tried several experiments based on your suggestion. See results inserted below. ~ Thanks, Ken
> -----Original Message----- > From: Charlie Clark [mailto:[EMAIL PROTECTED] > Sent: Saturday, June 16, 2007 3:54 PM > To: Ken Winter; 'Zope-DB List' > Subject: Re: [Zope-DB] Using <dtml-var>s in ZSQL methods? > > Am 16.06.2007, 21:19 Uhr, schrieb Ken Winter <[EMAIL PROTECTED]>: > > > p = [9765, 10058, 11333] > > s = ', '.join([str(x) for x in p]) > > " > > > > s > > gets passed to the ZSQL method, and that's where the trouble > > seems > > to be. > > I find this code a bit convoluted and somewhat dangerous if you are > passing data from a web form. What's wrong with repeatedly calling a > delete_person() method that just accepts a single id as a <dtml-sqlvar>? > This stuff is coming from a web form so it probably won't be a huge list > so the speed won't matter. EXPERIMENT #1: I tried this out by rewriting this part of the .cpy script to: " p = context.REQUEST.get('delete_this') for id in p: context.person_delete_by_id(person_id=id) " where person_delete_by_id is defined as follows (note that again I'm using a <dtml-var...> rather than a <dtml-sqlvar> here): " <dtml-comment> title: Method to delete one Person by id connection_id: dhatabase arguments: person_id </dtml-comment> delete from person where person_id = <dtml-var person_id> " When I tried to delete a couple of People using this I got the error message: " ProgrammingError: column "none" does not exist " Here are the details of the error: " Request URL http://localhost/DAgroups/portal_factory/People/people.2007-06-16.3625931808 /people_edit Exception Type ProgrammingError Exception Value column "none" does not exist Traceback (innermost last): * Module ZPublisher.Publish, line 115, in publish * Module ZPublisher.mapply, line 88, in mapply * Module ZPublisher.Publish, line 41, in call_object * Module Products.CMFPlone.FactoryTool, line 369, in __call__ * Module ZPublisher.mapply, line 88, in mapply * Module ZPublisher.Publish, line 41, in call_object * Module Products.CMFFormController.FSControllerPageTemplate, line 90, in __call__ * Module Products.CMFFormController.BaseControllerPageTemplate, line 28, in _call * Module Products.CMFFormController.ControllerBase, line 232, in getNext __traceback_info__: ['id = people_edit', 'status = success', 'button=delete', 'errors={}', 'context=<People at people.2007-06-16.3625931808>', "kwargs={'portal_status_message': 'People allegedly deleted: 68775, 69390'}", 'next_action=None', ''] * Module Products.CMFFormController.Actions.TraverseTo, line 38, in __call__ * Module ZPublisher.mapply, line 88, in mapply * Module ZPublisher.Publish, line 41, in call_object * Module Products.CMFFormController.FSControllerPythonScript, line 104, in __call__ * Module Products.CMFFormController.Script, line 145, in __call__ * Module Products.CMFCore.FSPythonScript, line 108, in __call__ * Module Shared.DC.Scripts.Bindings, line 311, in __call__ * Module Shared.DC.Scripts.Bindings, line 348, in _bindAndExec * Module Products.CMFCore.FSPythonScript, line 164, in _exec * Module None, line 4, in people_delete_control <FSControllerPythonScript at /DAgroups/people_delete_control used for /DAgroups/portal_factory/People/people.2007-06-16.3625931808> Line 4 * Module Shared.DC.ZRDB.DA, line 495, in __call__ <FSZSQLMethod at /DAgroups/person_delete_by_id used for /DAgroups/portal_factory/People/people.2007-06-16.3625931808> * Module Products.ZPsycopgDA.db, line 204, in query ProgrammingError: column "none" does not exist REQUEST form delete_this [68775, 69390] form.button.delete 'Delete Selected People' cookies tree-s 'eJzTyCkw5NLIKTDiClZ3hANXW3WuAmOuRKCECUjWFEnWNTEbJGvGlQgEegAEWRAt' __ac 'YWRtaW46cHAyMTA3' lazy items SESSION <bound method SessionDataManager.getSessionData of <SessionDataManager at /session_data_manager>> other tree-s 'eJzTyCkw5NLIKTDiClZ3hANXW3WuAmOuRKCECUjWFEnWNTEbJGvGlQgEegAEWRAt' __factory__info__ {'stack': ['People', 'people.2007-06-16.3625931808', 'people_edit'], 'People': <TempFolder at /DAgroups/portal_factory/People>} URL5 'http://localhost' URL4 'http://localhost/DAgroups' __ac 'YWRtaW46cHAyMTA3' URL0 'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180 8/people_edit' URL3 'http://localhost/DAgroups/portal_factory' URL2 'http://localhost/DAgroups/portal_factory/People' AUTHENTICATION_PATH '' AUTHENTICATED_USER <PropertiedUser 'admin'> SERVER_URL 'http://localhost' delete_this [68775, 69390] form.button.delete 'Delete Selected People' ACTUAL_URL 'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180 8/people_edit' portal_status_message 'People allegedly deleted: 68775, 69390' URL 'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180 8/people_edit' PUBLISHED <FactoryTool at /DAgroups/portal_factory> controller_state <Products.CMFFormController.ControllerState.ControllerState object at 0x066379F0> TraversalRequestNameStack [] BASE0 'http://localhost' BASE1 'http://localhost/DAgroups' BASE2 'http://localhost/DAgroups/portal_factory' BASE3 'http://localhost/DAgroups/portal_factory/People' BASE4 'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180 8' BASE5 'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180 8/people_edit' URL1 'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180 8' URL0 http://localhost/DAgroups/portal_factory/People/people.2007-06-16.3625931808 /people_edit URL1 http://localhost/DAgroups/portal_factory/People/people.2007-06-16.3625931808 URL2 http://localhost/DAgroups/portal_factory/People URL3 http://localhost/DAgroups/portal_factory URL4 http://localhost/DAgroups URL5 http://localhost BASE0 http://localhost BASE1 http://localhost/DAgroups BASE2 http://localhost/DAgroups/portal_factory BASE3 http://localhost/DAgroups/portal_factory/People BASE4 http://localhost/DAgroups/portal_factory/People/people.2007-06-16.3625931808 BASE5 http://localhost/DAgroups/portal_factory/People/people.2007-06-16.3625931808 /people_edit environ HTTP_COOKIE 'tree-s="eJzTyCkw5NLIKTDiClZ3hANXW3WuAmOuRKCECUjWFEnWNTEbJGvGlQgEegAEWRAt"; __ac="YWRtaW46cHAyMTA3"' SERVER_SOFTWARE 'Zope/(Zope 2.9.6-final, python 2.4.3, win32) ZServer/1.1 Plone/2.5.2' SCRIPT_NAME '' REQUEST_METHOD 'POST' HTTP_KEEP_ALIVE '300' SERVER_PROTOCOL 'HTTP/1.1' channel.creation_time 1182027293 CONNECTION_TYPE 'keep-alive' HTTP_ACCEPT_CHARSET 'ISO-8859-1,utf-8;q=0.7,*;q=0.7' HTTP_USER_AGENT 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.1) Gecko/20061204 Firefox/2.0.0.1' HTTP_REFERER 'http://localhost/DAgroups/portal_factory/People/people.2007-06-16.362593180 8/people_edit' SERVER_NAME 'KenIBM' REMOTE_ADDR '127.0.0.1' PATH_TRANSLATED '\\DAgroups\\portal_factory\\People\\people.2007-06-16.3625931808\\people_ed it' SERVER_PORT '80' CONTENT_LENGTH '120' HTTP_HOST 'localhost' HTTP_ACCEPT 'text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q =0.8,image/png,*/*;q=0.5' GATEWAY_INTERFACE 'CGI/1.1' HTTP_ACCEPT_LANGUAGE 'en-us,en;q=0.5' CONTENT_TYPE 'application/x-www-form-urlencoded' HTTP_ACCEPT_ENCODING 'gzip,deflate' PATH_INFO '/DAgroups/portal_factory/People/people.2007-06-16.3625931808/people_edit' " EXPERIMENT #2: I created and executed the *same* person_delete_by_id ZSQL Method in the ZMI. It deletes the specified Person record and produces no error. EXPERIMENT #3: I tweaked person_delete_by_id to use the standard <dtml-sqlvar...> construct: " <dtml-comment> title: Method to delete one Person by id connection_id: dhatabase arguments: person_id </dtml-comment> delete from person where person_id = <dtml-sqlvar person_id type="int"> " Called by the same .cpy script as above, this version executed without producing an error message - but it failed to delete any People from the database! (I'll spare you the details, but yes I did check to be sure that person_delete_by_id was being executed, and that the 'delete_this' list contained a couple of valid existing person_id values.) EXPERIMENT #4: I tested the revised person_delete_by_id in the ZMI, and it deleted People with no problem. EXPERIMENT #5: I tried something completely different: a ZSQL method that consists of nothing but a single <dtml-var...>. Its filesystem version looks like this: " <dtml-comment> title: Method whose whole body is passed in connection_id: dhatabase arguments: foo </dtml-comment> <dtml-var foo> " I rewrote the .cpy script to pass in the entire SQL statement: " p = context.REQUEST.get('delete_this') if p: s = ', '.join([str(x) for x in p]) t = "delete from person where person_id in (%s);" % s context.empty_q(foo=t) " To my amazement, this worked! - no errors, and the People targeted for deletion were indeed deleted. Can anybody explain these results? ... > > Have you declared id_list explicitly as an argument for your ZSQL method? > Keyword arguments will be ignored by ZSQL methods unless they are > explicitly declared as arguments. I think so. Here's the whole text of (the filesystem version of) people_delete_by_id.zsql: " <dtml-comment> title: Method to delete People by ids given in a comma-separated list connection_id: dhatabase arguments: id_list </dtml-comment> delete from person where person_id in (<dtml-var id_list>) " Isn't that a sufficient argument declaration? _______________________________________________ Zope-DB mailing list [email protected] http://mail.zope.org/mailman/listinfo/zope-db
