ID: 47471
Updated by: [email protected]
Reported By: pcdinh at gmail dot com
-Status: Open
+Status: To be documented
Bug Type: MySQLi related
Operating System: Windows XP SP3
PHP Version: 5.3.0beta1
New Comment:
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.
Previous Comments:
------------------------------------------------------------------------
[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/?id=47471&edit=1
