Doc #47471 [Tbd->Csd]: MySQLi, SP, loop count and the amount of result sets

Edit report at http://bugs.php.net/bug.php?id=47471&edit=1 ID: 47471 Updated by: abedford@php.net Reported by: pcdinh at gmail dot com Summary: MySQLi, SP, loop count and the amount of result sets -Status: To be documented +Status: Closed Type: Documentation Problem Package: MySQLi related Operating System: Windows XP SP3 PHP Version: 5.3.0beta1 Assigned To: abedford Block user comment: N Private report: N New Comment: There is already a suitable example showing use of mysqli_more_results() and mysqli_next_result(). It is linked to from both these functions, and can be found under mysqli_multi_query(). Previous Comments: ------------------------------------------------------------------------ [2011-01-06 15:32:57] uw@php.net That's how the MySQL client server protocol works. Nothing can be changed about it. Its a server matter and reflected in the C API which in turn is the basis for mysqli. At some point in the manual the loop for fetching results should be shown. Always use that loop. Tony, can you handle it? ------------------------------------------------------------------------ [2009-02-25 18:52:59] pcdinh at gmail dot com So why did MySQli iterate 3 times meanwhile there were 2 result sets? Why are there always an additional loop when all of the result sets are retrieved? According to your explanation, the additional loop should go first, not last, because the sequence of queries should go as follows: 1 - CALL foo : true 2 - SELECT 'foo' FROM DUAL; : true 3 - SELECT 'bar', 'bar2' FROM DUAL; : true ------------------------------------------------------------------------ [2009-02-25 13:31:55] johannes@php.net The interface is a bit weird, yes. mysqli_next_result() returns false in case the query is wrong. It will always return true in case the query was ok, independent from the number of fetched/available result sets. To check whether there are more result sets use mysqli_more_results() or check the return value of storing it. ------------------------------------------------------------------------ [2009-02-22 10:14:11] pcdinh at gmail dot com Description: ------------ When executing a stored procedure that returns multiple result sets, MySQLi driver always does an additional loop after all the result set are returned. The last loop always returns a FALSE without any error, which indicates as if there is a query that does not return a result set in the SP. PHP Manual does not mention about the additional special loop. SP: CREATE PROCEDURE foo() BEGIN SELECT 'foo' FROM DUAL; SELECT 'bar', 'bar2' FROM DUAL; END $$ Reproduce code: --------------- <?php $mysqli = new mysqli("localhost", "root", "123456", "test"); /* check connection */ if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } $sql = " DELIMITER $$ DROP PROCEDURE IF EXISTS foo $$ CREATE PROCEDURE foo() BEGIN SELECT 'foo' FROM DUAL; SELECT 'bar', 'bar2' FROM DUAL; END $$"; $rsCount = 0; $loopCount = 0; $hasNext = null; if ($mysqli->real_query("CALL foo()")) { do { $loopCount++; if ($hasNext === null) { echo 'Loop begins before any check. The first result set is available. '."\n"; } else { echo "Check if there is any result set: ".(int)$hasNext." at loop ".$loopCount.". \n"; } $result = $mysqli->store_result(); if ($mysqli->errno > 0) { echo "Error: ".$mysqli->error." <<<<<<<<"; continue; } if (is_object($result)) { $rsCount++; // $result->free_result(); echo 'Result set at loop '.$loopCount.'.'."\n"; } else { // No result set returns echo 'No result set at loop '.$loopCount.'.'."\n"; } } while ($hasNext = $mysqli->next_result()); // mysqli_next_result always returns true if an error occured. } echo "Total loop: $loopCount; Total result sets: $rsCount;"; /* close connection */ $mysqli->close(); ?> Expected result: ---------------- I created a stored procedure named foo() that is expected to return 2 result sets DELIMITER $$ DROP PROCEDURE IF EXISTS foo $$ CREATE PROCEDURE foo() BEGIN SELECT 'foo' FROM DUAL; SELECT 'bar', 'bar2' FROM DUAL; END $$ The PHP code should print out: Loop begins before any check. The first result set is available. Result set at loop 1. Check if there is any result set: 1 at loop 2. Result set at loop 2. Total loop: 2; Total result sets: 2; Actual result: -------------- The PHP code prints out: Loop begins before any check. The first result set is available. Result set at loop 1. Check if there is any result set: 1 at loop 2. Result set at loop 2. Check if there is any result set: 1 at loop 3. No result set at loop 3. Total loop: 3; Total result sets: 2; ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/bug.php?id=47471&edit=1