agesys-dev team mailing list archive

[Stephen Thorne <stephen@xxxxxxxxxxxx>]

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