#47471 [Tbd]: MySQLi, SP, loop count and the amount of result sets
| From: | pcdinh at gmail dot com | Date: | Wed, 25 Feb 2009 18:52:59 +0000 |
| Subject: | #47471 [Tbd]: MySQLi, SP, loop count and the amount of result sets | ||
| References: | 1 | Groups: | php.bugs php.doc.bugs |
| Request: | Send a blank email to php-bugs+get-134204@lists.php.net to get a copy of this message | ||
ID: 47471
User updated by: pcdinh at gmail dot com
Reported By: pcdinh at gmail dot com
Status: To be documented
Bug Type: MySQLi related
Operating System: Windows XP SP3
PHP Version: 5.3.0beta1
New Comment:
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
Previous Comments:
------------------------------------------------------------------------
[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/?id=47471&edit=1