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]<javascript:>
> > 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_**instances('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/**
>>>>> mgvot4PEvBgJ<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/**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] <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 [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
