[Zope-DB] Using <dtml-var>s in ZSQL methods?
Ken Winter
ken at sunward.org
Sat Jun 16 18:14:35 EDT 2007
Charlie ~ I tried several experiments based on your suggestion. See results
inserted below. ~ Thanks, Ken
> -----Original Message-----
> From: Charlie Clark [mailto:charlie at egenix.com]
> 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 <ken at sunward.org>:
>
> > 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?
More information about the Zope-DB
mailing list