Now we ruled out web2py completely in this. You need a MySQL expert.
On Tuesday, 26 February 2013 06:14:35 UTC-6, __pyslan__ wrote:
>
> 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
> <massimo....@gmail.com<javascript:>
> > 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 <nip...@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/<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/**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.
