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 <ykess...@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',Fiel**d('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.Di**ctCursor)
>>> 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
--
