For those interested, below the solution to the problem:
def users():
user_id = auth.user_id
fields = [db.auth_user.generation, db.auth_user.id,
db.auth_user.first_name]
proc = 'CALL sp_recursive_start(' + str(user_id) + ');'
raw_rows = db.executesql(proc, fields=fields)
while db._adapter.cursor.nextset():
#print db._adapter.cursor.fetchall()
pass
res = DIV()
if raw_rows:
for row in raw_rows:
print row
res.append(DIV(row.first_name))
return dict(form=res, subtitle=T('Users'))
sources:
MySQLdb
(cursor-objects)<http://mysql-python.sourceforge.net/MySQLdb.html#cursor-objects>
MySQLdb
documentation<http://mysql-python.sourceforge.net/MySQLdb-1.2.2/public/MySQLdb.cursors.Cursor-class.html>
Suggestions, warnings, and criticisms are welcome ...
Thanks!
On Tue, Feb 26, 2013 at 2:55 PM, __pyslan__ - Ayslan Jenken <
ayslan.pyt...@gmail.com> wrote:
> Ok. Thanks.
>
>
> On Tue, Feb 26, 2013 at 2:43 PM, Massimo Di Pierro <
> massimo.dipie...@gmail.com> wrote:
>
>> 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> 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/**m**ultiple-result-sets-in-mysql-**c**
>>>>>> onnectorpython/<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/**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.
