At this point, I've traced this down to 'mysql_stmt_result_metadata(stmt)' returning NULL when a stored procedure is called.
According to some bug reports, http://bugs.mysql.com/bug.php?id=42490 http://bugs.mysql.com/bug.php?id=19008 http://bugs.mysql.com/bug.php?id=11918 The relevant section from the manual: "The multiple statement and result capabilities can be used only with mysql_query() or mysql_real_query(). They cannot be used with the prepared statement interface. Prepared statement handles are defined to work only with strings that contain a single statement. See Section 22.8.4, “C API Prepared Statements”." http://dev.mysql.com/doc/refman/5.5/en/c-api-multiple-queries.html So the answer is "you cannot call a stored procedure from mysql_stmt_query() if you want any results" :( In order to do what you want to do (use CALL) you have to do the escaping manually yourself, and use curs.execute("CALL foo(1,2,3)", plain_query=True) ** Bug watch added: MySQL Bug System #42490 http://bugs.mysql.com/bug.php?id=42490 ** Bug watch added: MySQL Bug System #19008 http://bugs.mysql.com/bug.php?id=19008 ** Bug watch added: MySQL Bug System #11918 http://bugs.mysql.com/bug.php?id=11918 -- You received this bug notification because you are a member of Agesys Team, which is subscribed to oursql. https://bugs.launchpad.net/bugs/1004991 Title: Calling a stored procedure says rows returned but won't let me fetch results. Status in oursql: Confirmed Bug description: Calling a stored procedure shows a positive row count but won't let me fetch results. Client running Ubuntu 12.04 LTS, Python 2.7.3, OurSQL 0.9.3, and libmysqlclient-dev 5.5 Server is an Amazon RDS MySQL instance running MySQL 5.5.12 Create Table: delimiter $$ CREATE TABLE `test` ( `idtest` int(11) NOT NULL AUTO_INCREMENT, `data` int(11) DEFAULT NULL, PRIMARY KEY (`idtest`), UNIQUE KEY `idtest_UNIQUE` (`idtest`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1$$ Populate this table. I have 189 entries all with random numbers for `data`. Create Procedure: delimiter $$ CREATE DEFINER=`db`@`%` PROCEDURE `foo`( IN a INT ,IN b INT ,IN c INT ) BEGIN SELECT SQL_CACHE `data` FROM `db`.`test` ORDER BY a ASC; END$$ Python Code (test.py): import oursql conn = oursql.connect(host='www.example.com', user='foo', passwd='bar', port=3306) curs = conn.cursor() curs.execute("CALL db.foo(?, ?, ?)", (1, 2, 3)) print("Row count: " + str(curs.rowcount)) print(curs.nextset()) res = curs.fetchone() print(res) curs.close() Output: Row count: 189 Traceback (most recent call last): File "test.py", line 8, in <module> print(curs.nextset()) File "cursor.pyx", line 169, in oursql.Cursor.nextset (oursqlx/oursql.c:16436) File "cursor.pyx", line 161, in oursql.Cursor._check_statements (oursqlx/oursql.c:16392) oursql.ProgrammingError: (None, 'no results available', None) To manage notifications about this bug go to: https://bugs.launchpad.net/oursql/+bug/1004991/+subscriptions _______________________________________________ Mailing list: https://launchpad.net/~agesys-dev Post to : [email protected] Unsubscribe : https://launchpad.net/~agesys-dev More help : https://help.launchpad.net/ListHelp
