[web2py] Re: DAL calls fail after first stored procedure call

[Alfonso Serra]

I have exactly the same issue.

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="metrily")
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.




-- 
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.

[web2py] Re: DAL calls fail after first stored procedure call

[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()

Im not sure if its working because ive mysql connectors installed but ye it 
looks like it does.

-- 
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.

[web2py] Re: DAL calls fail after first stored procedure call

[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.

-- 
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.

[web2py] Re: DAL calls fail after first stored procedure call

[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 http://mysql-python.sourceforge.net/MySQLdb.html 
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?

-- 
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.

[web2py] Re: DAL calls fail after first stored procedure call

[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.

 *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)




-- 

[web2py] Re: DAL calls fail after first stored procedure call

[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.

 *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)




-- 

[web2py] Re: DAL calls fail after first stored procedure call

[Yarin]

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

On Monday, August 27, 2012 1:53:32 PM UTC-4, villas wrote:

 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.

 *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)




-- 

[web2py] Re: DAL calls fail after first stored procedure call

[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.

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)




-- 

[web2py] Re: DAL calls fail after first stored procedure call

[Yarin]

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)




-- 

[web2py] Re: DAL calls fail after first stored procedure call

[Massimo Di Pierro]

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)




-- 

[web2py] Re: DAL calls fail after first stored procedure call

[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..





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)




-- 

[web2py] Re: DAL calls fail after first stored procedure call

[Anthony]

The existing adapter should work with mysqldb. Try:

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

Anthony

On Monday, August 27, 2012 4:37:42 PM UTC-4, Yarin wrote:

 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)




-- 

[web2py] Re: DAL calls fail after first stored procedure call

[Yarin]

Thanks Anthony- ill give it a try

On Monday, August 27, 2012 4:58:01 PM UTC-4, Anthony wrote:

 The existing adapter should work with mysqldb. Try:

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

 Anthony

 On Monday, August 27, 2012 4:37:42 PM UTC-4, Yarin wrote:

 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)




--