well, I doubt that workbench uses a python dbapi. >From where I stand, you should try to make that work on the adapter (i.e. without web2py) and see if there it works....
in my pov, mysql returns different resultsets for that kind of stored proc: one is the result of the line inserted in a loop, and the other one is the resultset that is fetched back from the temporary table. I'm no dbapi expert but I'd say that this is not supported and has to be managed on the adapter side. http://geert.vanderkelen.org/multiple-result-sets-in-mysql-connectorpython/ On Monday, February 25, 2013 5:55:16 PM UTC+1, __pyslan__ wrote: > > But when I make these calls in MySQL Workbench, the result is correct. > > Look the attached image, please... > > > > > On Mon, Feb 25, 2013 at 1:24 PM, Massimo Di Pierro > <massimo....@gmail.com<javascript:> > > wrote: > >> The code below is equivalent to: >> >> sql> CALL sp_recursive_start(1); >> sql> ROLLBACK; >> >> web2py is not adding anything to it. >> This demonstrates that the problem is completely with the code in >> "sp_recursive_start". >> You are getting an OperationError from the database on rollback. >> >> My guess is that there is one of these two problems or both: >> >> 1) you cannot declare the same stored procedure at every request. You >> should declare it once outside of web2py. >> 2) you need to explicitly commit after you declare a stored procedure. >> You cannot roll it back. >> >> Let us know if this helps. >> >> Massimo >> >> >> On Monday, 25 February 2013 08:38:30 UTC-6, __pyslan__ wrote: >> >>> I got it: >>> >>> In [1]: fields = [db.auth_user.generation, db.auth_user.id, >>> db.auth_user.first_name] >>> >>> In [2]: raw_rows = db.executesql('CALL sp_recursive_start(1);', >>> fields=fields) >>> >>> In [3]: db.rollback() >>> ------------------------------**------------------------------** >>> --------------- >>> ProgrammingError Traceback (most recent call >>> last) >>> /home/ctx/PROJECTS/WEB/web2py/**applications/myapp/models/006_**menu.py >>> in <module>() >>> ----> 1 db.rollback() >>> >>> /home/ctx/PROJECTS/WEB/web2py/**gluon/dal.pyc in rollback(self) >>> 7286 >>> 7287 def rollback(self): >>> -> 7288 self._adapter.rollback() >>> 7289 >>> 7290 def close(self): >>> >>> /home/ctx/PROJECTS/WEB/web2py/**gluon/dal.pyc in rollback(self) >>> 1668 >>> 1669 def rollback(self): >>> -> 1670 if self.connection: return self.connection.rollback() >>> 1671 >>> 1672 def close_connection(self): >>> >>> ProgrammingError: (2014, "Commands out of sync; you can't run this >>> command now") >>> >>> >>> >>> On Mon, Feb 25, 2013 at 11:24 AM, Massimo Di Pierro < >>> massimo....@gmail.com> wrote: >>> >>>> This is a database issue more than a web2py issue but I am interested >>>> to get to the bottom of it. >>>> >>>> Can you ty open a web2py shell >>>> >>>> $ python web2py.py -S yourapp -M >>>> >>> fields = [db.auth_user.generation, db.auth_user.id, db.auth_user. >>>> first_name] >>>> >>>> >>> raw_rows = db.executesql('CALL sp_recursive_start(1);', fields=fields) >>>> >>>> >>> db.rollback() >>>> >>>> What do you get? >>>> >>>> >>>> >>>> On Monday, 25 February 2013 07:39:24 UTC-6, __pyslan__ wrote: >>>> >>>>> No, Massimo, is not ... With or without the try... except... the error >>>>> is the same ... I've tried this several times. >>>>> >>>>> The code of gist: >>>>> https://gist.github.com/**pyslan**/5007364<https://gist.github.com/pyslan/5007364> >>>>> (without >>>>> try... except...) print it on the terminal server: >>>>> >>>>> <Row {'generation': 1, 'first_name': 'Root', 'id': 1}> >>>>> <Row {'generation': 2, 'first_name': 'Teste', 'id': 2}> >>>>> ERROR:web2py:Traceback (most recent call last): >>>>> File "/home/ctx/PROJECTS/WEB/**web2py**/gluon/main.py", line 632, >>>>> in wsgibase >>>>> BaseAdapter.close_all_**instance**s('rollback') >>>>> File "/home/ctx/PROJECTS/WEB/**web2py**/gluon/dal.py", line 543, >>>>> in close_all_instances >>>>> db._adapter.close(action) >>>>> File "/home/ctx/PROJECTS/WEB/**web2py**/gluon/dal.py", line 523, in >>>>> close >>>>> getattr(self, action)() >>>>> File "/home/ctx/PROJECTS/WEB/**web2py**/gluon/dal.py", line 1670, >>>>> in rollback >>>>> if self.connection: return self.connection.rollback() >>>>> ProgrammingError: (2014, "Commands out of sync; you can't run this >>>>> command now") >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> On Sat, Feb 23, 2013 at 5:58 PM, Massimo Di Pierro < >>>>> massimo....@gmail.com> wrote: >>>>> >>>>>> This is causing the problem. What if you remove the try... except? >>>>>> What ticket do you get? >>>>>> >>>>>> try: >>>>>> >>>>>> raw_rows = db.executesql('CALL sp_recursive_start(1);', >>>>>> fields=fields) >>>>>> >>>>>> >>>>>> except Exception, e: >>>>>> print 'ERROR NOW:', e >>>>>> >>>>>> >>>>>> db.rollback() >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> On Saturday, 23 February 2013 12:58:44 UTC-6, __pyslan__ wrote: >>>>>>> >>>>>>> After reading this >>>>>>> thread<http://en.usenet.digipedia.org/thread/16137/52728/>, >>>>>>> as Anthony suggested, I tried this: >>>>>>> >>>>>>> import MySQLdb >>>>>>> from gluon.dal import MySQLAdapter >>>>>>> MySQLAdapter.driver = MySQLdb >>>>>>> >>>>>>> After that, even commenting this block of code I get the result >>>>>>> correctly because the print rows, but the error below is thrown anyway: >>>>>>> >>>>>>> Traceback (most recent call last): >>>>>>> File "/media/sda3/pyslan/PROJECTS/**w****eb2py/gluon/main.py", line >>>>>>> 632, in wsgibase >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> BaseAdapter.close_all_**instance****s('rollback') >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> File "/media/sda3/pyslan/PROJECTS/**w****eb2py/gluon/dal.py", line >>>>>>> 543, in close_all_instances >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> db._adapter.close(action) >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> File "/media/sda3/pyslan/PROJECTS/**w****eb2py/gluon/dal.py", line >>>>>>> 523, in close >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> getattr(self, action)() >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> File "/media/sda3/pyslan/PROJECTS/**w****eb2py/gluon/dal.py", line >>>>>>> 1670, in rollback >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> if self.connection: return self.connection.rollback() >>>>>>> >>>>>>> >>>>>>> >>>>>>> ProgrammingError: (2014, "Commands out of sync; you can't run this >>>>>>> command now") >>>>>>> >>>>>>> >>>>>>> >>>>>>> The code: >>>>>>> https://gist.github.com/**pyslan****/5007364<https://gist.github.com/pyslan/5007364> >>>>>>> >>>>>>> Something related with this? >>>>>>> https://groups.google.com/**foru****m/#!msg/web2py/**1s7vDix8QC8/** >>>>>>> mgv**ot4PEvBgJ<https://groups.google.com/forum/#!msg/web2py/1s7vDix8QC8/mgvot4PEvBgJ> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> -- >>>>>> >>>>>> --- >>>>>> You received this message because you are subscribed to the Google >>>>>> Groups "web2py-users" group. >>>>>> To unsubscribe from this group and stop receiving emails from it, >>>>>> send an email to web2py+un...@**googlegroups.com. >>>>>> >>>>>> For more options, visit >>>>>> https://groups.google.com/**grou**ps/opt_out<https://groups.google.com/groups/opt_out> >>>>>> . >>>>>> >>>>>> >>>>>> >>>>> >>>>> -- >>>> >>>> --- >>>> You received this message because you are subscribed to the Google >>>> Groups "web2py-users" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to web2py+un...@**googlegroups.com. >>>> For more options, visit >>>> https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out> >>>> . >>>> >>>> >>>> >>> >>> -- >> >> --- >> You received this message because you are subscribed to the Google Groups >> "web2py-users" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to web2py+un...@googlegroups.com <javascript:>. >> For more options, visit https://groups.google.com/groups/opt_out. >> >> >> > > -- --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
