Calling a stored procedure says rows returned but won't let me fetch results.
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
oursql |
Confirmed
|
Undecided
|
Unassigned |
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.
curs = conn.cursor()
curs.execute("CALL db.foo(?, ?, ?)", (1, 2, 3))
print("Row count: " + str(curs.rowcount))
print(curs.
res = curs.fetchone()
print(res)
curs.close()
Output:
Row count: 189
Traceback (most recent call last):
File "test.py", line 8, in <module>
print(
File "cursor.pyx", line 169, in oursql.
File "cursor.pyx", line 161, in oursql.
oursql.
description: | updated |
description: | updated |
Changed in oursql: | |
status: | New → Confirmed |
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, bugs.mysql. com/bug. php?id= 42490 bugs.mysql. com/bug. php?id= 19008 bugs.mysql. com/bug. php?id= 11918
http://
http://
http://
The relevant section from the manual: dev.mysql. com/doc/ refman/ 5.5/en/ c-api-multiple- queries. html
"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://
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)