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.. On Monday, August 27, 2012 3:52:06 PM UTC-4, Massimo Di Pierro wrote: > > Try use mysqldb too. The default pymysql has some problems. We even have > to monkeypatch it for security. > > Massimo > > On Monday, 27 August 2012 14:20:00 UTC-5, Yarin wrote: >> >> This is MySQL using the default driver, whatever that is. I'm going to >> test on other drivers/dbs and will report back.. >> >> On Monday, August 27, 2012 3:03:15 PM UTC-4, Massimo Di Pierro wrote: >>> >>> 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. >>> >>> On Monday, 27 August 2012 09:37:45 UTC-5, Yarin wrote: >>>> >>>> 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) >>>> >>>> >>>> --
