I have not the faintest idea how to do that, and this feature is essential for the project. I can not build a recursive function due to performance. With great respect I ask, should I give up web2py in this case?
On Mon, Feb 25, 2013 at 2:28 PM, Niphlod <niph...@gmail.com> wrote: > 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 >> > 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/**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+unsubscr...@googlegroups.com. > 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.
