DAL calls fail after first stored procedure call

[Yarin]

Once we call a stored procedure through the DAL, any subsequent DAL calls are returning None results.

Basic stored procedure:

BEGIN
  SELECT *  FROM people;
END

Works:

def test():
 
 sql = "CALL GetPeople();"
 sproc_results = db_test.executesql(sql)
 
 return str(sproc_results)

Works:

def test():
 
 sql = "SELECT * FROM people;"
 reg_results = db_test.executesql(sql)
 
 return str(reg_results)

Returns None:

def test():
 
 sql = "CALL GetPeople();"
 sproc_results = db_test.executesql(sql)
 
 sql = "SELECT * FROM people;"
 reg_results = db_test.executesql(sql)

 return str(reg_results)

Returns None:

def test():
 
 sql = "CALL GetPeople();"
 sproc_results = db_test.executesql(sql)
 
 sql = "CALL GetPeople();"
 sproc_results = db_test.executesql(sql)
 
 return str(sproc_results)

[Yarin]

Pretty desperate on this one- brought everything to a screeching halt. We need to be able to call more than one stored procedure per request. Anybody got any ideas?

On Monday, August 27, 2012 10:37:45 AM UTC-4, Yarin wrote:

Once I call a stored procedure through the DAL, any subsequent calls are returning None results.

[villas]

Just a thought - does db.commit() help?
Rgds, D

On Monday, August 27, 2012 3:37:45 PM UTC+1, Yarin wrote:

Once we call a stored procedure through the DAL, any subsequent DAL calls are returning None results.

[Yarin]

Villas- Thanks but no it doesn't- tried that..

[Massimo Di Pierro]

I do not think this is a DAL issue. This looks database issue or a driver issue. Web2py does not do another else but pass your SQL to the driver and fetch responses.

Is this SQLITE? In any case, I suggest you try the native driver APIs.

[Yarin]

This is MySQL using the default driver, whatever that is. I'm going to test on other drivers/dbs and will report back..

[Massimo Di Pierro]

Try use mysqldb too. The default pymysql has some problems. We even have to monkeypatch it for security.

Massimo

[Yarin]

This is an issue with pymsql: http://code.google.com/p/pymysql/issues/detail?id=72, https://github.com/petehunt/PyMySQL/blob/master/pymysql/cursors.py

Running against pymysql's api directly, I can execute sproc calls on on two different connections, but not on the same connection.

So...

• What's the best way to emulate closing/opening a connection through the DAL- does it mean instantiating a new DAL object? Is working with multiple DAL objects a bad idea, or is that a good workaround?
• If I switch to mysqldb, does that mean i've got to write my own adapter? If there's already adapters/recipes ready for using mysqldb I'll try it, otherwise I can't spare the time right now..

[Anthony]

The existing adapter should work with mysqldb. Try:

import mysqldb
from gluon.dal import MySQLAdapter
MySQLAdapter.driver = mysqldb
db=DAL('mysql://....')

Anthony

[Yarin]

Thanks Anthony- ill give it a try

[Josh L]

This still seems to be an issue for me running 2.9.5-stable+timestamp.2014.03.16.02.35.39. I tried both mysqldb and the default pymysql driver. I want to use a stored procedure to run a lengthy query that is run quite often by my application. This behavior should be easily reproducable using a simple stored procedure:

CREATE PROCEDURE `SimpleSP`()
BEGIN
SELECT 'hello';
END

Running "db.executesql('CALL SimpleSP()')" twice in a row on command line with -S -M options:
>>> db.executesql('CALL SimpleSP()')
((u'hello',),)
>>> db.executesql('CALL SimpleSP()')
None

This behavior alternates back and forth between these two results.

With mysqldb on the second and all subsequent tries I get:
ProgrammingError: (2014, "Commands out of sync; you can't run this command now")

Even worse though is that you can no longer run db(query).select() type commands after just one CALL. Something in the DAL is getting screwed up because when I try to run a normal query after running the CALL, I get results I should have received from the CALL, or no results, or "TypeError: 'NoneType' object is not iterable" and other strange artifacts.

The mysqldb manual states:

Compatibility note: It appears that the mere act of executing the CALL statement produces an empty result set, which appears after any result sets which might be generated by the stored procedure. Thus, you will always need to use nextset() to advance result sets.

So it appears that maybe executesql() is not handling the multiple result sets gracefully? Is there any resolution to this issue?

[Josh L]

For now I'm just using a mysqldb.connect(options) object directly with a cursor to call my SP, bypassing the web2py DAL completely. Of course this means I have to create a new DB connection for every SP call, and I'm thinking performance wise this might be worse off than just not using an SP altogether. Hopefully the devs can look at this and let us know if this is a bug or if a workaround exists.

[Alfonso Serra]

I have exactly the same issue. Version 2.12.3-stable+timestamp.2015.08.19.00.18.03

Call a procedure once and works.
Call another procedure again, returns None and from this point on, simple db(..).select() are broken.

Its definetly a DAL issue since using mysql connector does works well.

This is an example:
Download mysql connectors for python

import mysql.connector as mysql
cnn = mysql.connect(user="root", password ="root", database="mydb")
cur = cnn.cursor()

cur.callproc("grp_groups_checkin", ('2013-01-01', '2013-12-31'))
for res in cur.stored_results():
    rows = res.fetchall()

cur.callproc("grp_subgroups_checkin", ('2013-01-01', '2013-12-31'))
for res in cur.stored_results():
    rows2 = res.fetchall()

cur.close()
cnn.close()

Maybe advancing the cursor or creating a new DAL method for procedures might do the trick.

[Alfonso Serra]

Ive solved it by creating a function as:

def callproc(name, args):
    cur = db._adapter.cursor
    cur.callproc(name, args)
    if hasattr(cur, "stored_results"):
        for r in cur.stored_results():
            return r.fetchall()
    else:
        return cur.fetchall()

It works on the console but it doesnt work on controllers. This might be because db.commit gets auto called.