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
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Reply via email to