#42548 [Com]: PROCEDURE xxx can't return a result set in the given context (works in 5.2.3!!)

ID: 42548 Comment by: uwendel at mysql dot com Reported By: garethjo at usc dot edu Status: Assigned Bug Type: MySQLi related Operating System: Windows XP, Windows 2003 PHP Version: 5.2.4 Assigned To: georg New Comment: Does using mysqli_multi_query() work for you? Currently you are using mysqli_real_query() to call the SP. See also, http://dev.mysql.com/doc/refman/5.1/en/call.html. If a stored procedure produces result sets, you must use mysqli_multi_query(). Ulf Previous Comments: ------------------------------------------------------------------------ [2007-09-09 10:42:13] jani@php.net Georg (or whoever maintains mysqli nowadays), check this out. Seems like some regression bug between 5.2.3 / 5.2.4 crept in.. ------------------------------------------------------------------------ [2007-09-07 18:00:17] al dot smith at aeschi dot ch dot eu dot org I'm seeing this exact bug as well. Rolling back to 5.2.3 fixes the problem. For me, executing the CALL() statement within a mysql> client session works just fine... ------------------------------------------------------------------------ [2007-09-07 14:50:18] garethjo at usc dot edu Yes I have seen those bugs and they are not what I am experiencing. Any stored procedure that would normally return a resultset whether it be the first or not produces the "PROCEDURE procedure.Name can't return a result set in the given context" error not a lost connection. In my example code, the first query is just used to create the database table and stored procedure. So even if it is separated out and ran separately so that the bug test is in a separate file and runs by itself after the database tables and proc are created, it will still produce the same result even though it is the first proc that was run. ------------------------------------------------------------------------ [2007-09-06 22:31:42] jani@php.net Have you seen bug #32882 and bug #35203 ?? ------------------------------------------------------------------------ [2007-09-05 00:10:23] garethjo at usc dot edu <?php //------------ DATABASE SETUP------------------------ $mysqli = mysqli_init(); $mysqli->real_connect('localhost', 'root', 'root_pass', 'test'); if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } $strDatabaseCreation = 'CREATE DATABASE IF NOT EXISTS test; USE test; DROP TABLE IF EXISTS products; CREATE TABLE products ( intProductId int(10) unsigned NOT NULL auto_increment, strProductName varchar(45) NOT NULL, douProductPrice double NOT NULL, intQuantity int(10) unsigned NOT NULL, PRIMARY KEY (intProductId) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO products (intProductId,strProductName,douProductPrice,intQuantity) VALUES (1,\'Mugs\',10,5), (2,\'Boots\',75,12); CREATE PROCEDURE test.spGetProducts() BEGIN SELECT * FROM Products; END '; printf ("Connection: %s<br>\r\n.", $mysqli->host_info); if($mysqli->multi_query ($strDatabaseCreation)) { print "Databse created successfully<br>\r\n"; } else { print "failed to create database<br>\r\n".$mysqli->error; $mysqli->close(); die; } $mysqli->close(); //------------ BUG TEST START -------------------------- $mysqli = mysqli_init(); $mysqli->real_connect('localhost', 'root', 'root_pass', 'test'); if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } if($mysqli->real_query ("CALL spGetProducts();")) { if($objResult = $mysqli->store_result()) { while($row = $objResult->fetch_assoc()) { print $row["strProductName"]." ".$row["strProductName"]."<br>\r\n"; } $objResult->free_result(); } else { print "no results found"; } } else { print $mysqli->error; } $mysqli->close(); ?> ------------------------------------------------------------------------ The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/42548 -- Edit this bug report at http://bugs.php.net/?id=42548&edit=1