Solution found:
db._adapter.cursor.callproc(..)
res = db._adapter.cursor.nextset()
while res != None:
print result
res = db._adapter.cursor.nextset()
In a nutshell you have data waiting to be fetched and DAL skrews it up..
2015. november 21., szombat 18:32:14 UTC+1 időpontban Dániel Finta a
következőt írta:
>
> Hi!
>
> Just want to ask how long this still takes to fix? We like to use stored
> procedures for MySQL queries. After an executesql( "CALL... everything
> gives this error - even commit() and rollback(). This only happens if the
> procedure wants to give back a result. I have tried to fetch it manually
> through the cursor but no luck.
>
> Could you point me to a solution or workaround other than write all
> queries by hand? Maybe it could be implemented as an execute_multiple or
> call_procedure function aside from the normal workings to not mess things
> up in the current DAL?
>
> Thanks,
> rawbits
>
>
>
> 2013. január 25., péntek 19:44:06 UTC+1 időpontban Massimo Di Pierro a
> következőt írta:
>>
>> True. For now you need to separate calls to db.executesql if you have
>> multiple queries.
>>
>> On Friday, 25 January 2013 11:54:18 UTC-6, AngeloC wrote:
>>>
>>> Hi Massimo,
>>>
>>> I think this is a real problem.
>>>
>>> In a web2py shell you can do:
>>>
>>> rows = db.executesql("select * from charts")
>>>
>>> but you cannot do:
>>>
>>> rows = db.executesql("select * from charts; select * from charts;")
>>>
>>> it raises the error above.
>>>
>>> I think the problem is related to multiple resultsets. That example
>>> query return 2 resultset, as well a stored procedure could return multiple
>>> resultset. I think web2py mess something when a multiquery or a stored
>>> procudere returns more than one resultset.
>>>
>>>
>>> Mysql user guide says here (
>>> http://dev.mysql.com/doc/refman/5.0/en/commands-out-of-sync.html):
>>>
>>> If you get Commands out of sync; you can't run this command now in your
>>> client code, you are calling client functions in the wrong order.
>>>
>>> This can happen, for example, if you are using mysql_use_result()
>>> <http://dev.mysql.com/doc/refman/5.0/en/mysql-use-result.html> and try
>>> to execute a new query before you have called mysql_free_result()
>>> <http://dev.mysql.com/doc/refman/5.0/en/mysql-free-result.html>. It can
>>> also happen if you try to execute two queries that return data without
>>> calling mysql_use_result()
>>> <http://dev.mysql.com/doc/refman/5.0/en/mysql-use-result.html> or
>>> mysql_store_result()
>>> <http://dev.mysql.com/doc/refman/5.0/en/mysql-store-result.html> in
>>> between.
>>>
>>>
>>>
>>> 2012/9/28 Yarin <ykes...@gmail.com>
>>>
>>>> Done: http://code.google.com/p/web2py/issues/detail?id=1037
>>>>
>>>>
>>>> On Thursday, September 27, 2012 4:33:37 PM UTC-4, Massimo Di Pierro
>>>> wrote:
>>>>>
>>>>> Is this issue still open? If so, can you open a ticket about it?
>>>>>
>>>>> Massimo
>>>>>
>>>>> On Monday, 24 September 2012 14:38:14 UTC-5, Yarin wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>> db.py:
>>>>>> db.define_table('mytable',Field('myfield','string'))
>>>>>>
>>>>>> sproc:
>>>>>> DELIMITER //
>>>>>> CREATE PROCEDURE TestSproc()
>>>>>> BEGIN
>>>>>> SELECT COUNT(*) FROM mytable;
>>>>>> END //
>>>>>> DELIMITER;
>>>>>>
>>>>>> tests.py
>>>>>> """NOTE: Tests 1-3 will fail every time in wsgi/linux environments.
>>>>>> From the web2py command line, they will work once, until another
>>>>>> operation is called on the db, INCLUDING commit().
>>>>>>
>>>>>> In web2py 1, you can run these once successfully, but subsequent sql
>>>>>> operations on the current connection return None results.
>>>>>> """
>>>>>>
>>>>>> def test_1():
>>>>>> ''' RESULT: ProgrammingError: (2014, "Commands out of sync; you
>>>>>> cant run this command now") '''
>>>>>>
>>>>>> sql = '''CALL TestSproc();'''
>>>>>> results = db.executesql(sql, as_dict=True)
>>>>>> return str(results)
>>>>>>
>>>>>> def test_2():
>>>>>> ''' RESULT: ProgrammingError: (2014, "Commands out of sync; you
>>>>>> cant run this command now") '''
>>>>>>
>>>>>> sql = """CALL TestSproc();"""
>>>>>> results = db.executesql(sql, as_dict=True)
>>>>>> db.commit()
>>>>>> return str(results)
>>>>>>
>>>>>> def test_3():
>>>>>> ''' RESULT: ProgrammingError: (2014, "Commands out of sync; you
>>>>>> cant run this command now") '''
>>>>>>
>>>>>> sql = """CALL TestSproc();"""
>>>>>> db._adapter.execute(sql)
>>>>>> results = db._adapter.cursor.fetchall()
>>>>>> return str(results)
>>>>>>
>>>>>> def test_4():
>>>>>> ''' RESULT: Works '''
>>>>>>
>>>>>> import contrib.pymysql as pymysql
>>>>>> sql = """CALL TestSproc();"""
>>>>>> conn = pymysql.connect(host='127.0.0.1', port=3306, user='root',
>>>>>> passwd='root', db='test')
>>>>>> cur = conn.cursor(pymysql.cursors.DictCursor)
>>>>>> cur.execute(sql)
>>>>>> results = cur.fetchall()
>>>>>> cur.close()
>>>>>> conn.close()
>>>>>> return str(results)
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Monday, September 24, 2012 1:10:14 PM UTC-4, Massimo Di Pierro
>>>>>> wrote:
>>>>>>>
>>>>>>> can you show us some of your code. This may be a concurrency issues.
>>>>>>> I cannot thing anything we change that would affect the behavior.
>>>>>>>
>>>>>>> On Sunday, 23 September 2012 22:43:28 UTC-5, Yarin wrote:
>>>>>>>>
>>>>>>>> After upgrading to web2py 2.0, we can no longer use the DAL or
>>>>>>>> mysql adapters to make MySQL stored procedure calls.
>>>>>>>>
>>>>>>>> In web2py 1.x I had reported
>>>>>>>> <https://groups.google.com/d/msg/web2py/VaGJjwE3eYQ/GPa3xRe-HVsJ>
>>>>>>>> how mysql stored procedures weren't working for us in certain
>>>>>>>> circumstances- however we were able to circumvent those issues by
>>>>>>>> simply
>>>>>>>> creating a new DAL instance everytime we had to call a sproc. With the
>>>>>>>> new
>>>>>>>> DAL, that's no longer an option- indeed, any call to a stored
>>>>>>>> procedure
>>>>>>>> seems to fail in any attempt through the DAL or mysql adapter.
>>>>>>>>
>>>>>>>> - Fails with both pymysql and mysqldb drivers
>>>>>>>> - Fails when calling commit() immediately after
>>>>>>>> - We did side-by-side comparisons with web2py 1.x on same
>>>>>>>> server, pointing to same db, and confirmed this fails only in 2.0
>>>>>>>> - You can make sproc calls using the DAL straight from the
>>>>>>>> web2py shell, but only if you don't call commit.
>>>>>>>>
>>>>>>>>
>>>>>>>> Stack trace:
>>>>>>>>
>>>>>>>> Traceback (most recent call last):
>>>>>>>> File "/opt/web-apps/web2py/gluon/main.py", line 580, in wsgibase
>>>>>>>>
>>>>>>>>
>>>>>>>> BaseAdapter.close_all_instances('rollback')
>>>>>>>> File "/opt/web-apps/web2py/gluon/dal.py", line 511, in
>>>>>>>> close_all_instances
>>>>>>>>
>>>>>>>>
>>>>>>>> getattr(instance, action)()
>>>>>>>> File "/opt/web-apps/web2py/gluon/dal.py", line 1633, in rollback
>>>>>>>>
>>>>>>>>
>>>>>>>> return self.connection.rollback()
>>>>>>>> ProgrammingError: (2014, "Commands out of sync; you can't run this
>>>>>>>> command now")
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> Right now the only option we've arrive at is to make sproc calls
>>>>>>>> directly through the pymysql driver API, one per connection.
>>>>>>>>
>>>>>>>>
>>>>>>>> --
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>> --
>>> Profile: http://it.linkedin.com/in/compagnucciangelo
>>>
>>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
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/d/optout.
