The result is the same error... I get the result as (1), but the error is always thrown.
On Tue, Feb 26, 2013 at 2:25 AM, Massimo Di Pierro < [email protected]> wrote: > Try: > > import MySQLdb > conn.MySQLdb.connect(db=db, > user=user, > passwd=passwd, > host=host, > port=port, > charset=charset) > curr = conn.cursor() > curr.execute('CALL sp_recursive_start(1);') > print curr.fetchall() # (1) > conn.rollback() > > (1) Try with and without this line. > > On Monday, 25 February 2013 12:06:09 UTC-6, __pyslan__ wrote: > >> 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 <[email protected]> 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/<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 < >>>> [email protected]> 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 < >>>>>> [email protected]> 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 < >>>>>>>> [email protected]> 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/**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 [email protected]. > 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 [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
