Fascinating.... I wonder if this logic should be built-in into executesql
somehow but I am not sure it it supported by all adapters and I am not sure
how it could be done.
On Wednesday, 27 February 2013 09:44:09 UTC-6, __pyslan__ wrote:
>
> 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 <
> [email protected] <javascript:>> wrote:
>
>> Ok. Thanks.
>>
>>
>> On Tue, Feb 26, 2013 at 2:43 PM, Massimo Di Pierro
>> <[email protected]<javascript:>
>> > 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 <
>>>> [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/**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 <
>>>>>>>> [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/**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] <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.
