Doc #47471 [Tbd]: MySQLi, SP, loop count and the amount of result sets
| From: | uw@php.net | Date: | Thu, 06 Jan 2011 14:32:57 +0000 |
| Subject: | Doc #47471 [Tbd]: MySQLi, SP, loop count and the amount of result sets | ||
| References: | 1 | Groups: | php.doc.bugs |
| Request: | Send a blank email to doc-bugs+get-5757@lists.php.net to get a copy of this message | ||
Edit report at http://bugs.php.net/bug.php?id=47471&edit=1
ID: 47471
Updated by: uw@php.net
Reported by: pcdinh at gmail dot com
Summary: MySQLi, SP, loop count and the amount of result sets
Status: To be documented
Type: Documentation Problem
Package: MySQLi related
Operating System: Windows XP SP3
PHP Version: 5.3.0beta1
-Assigned To:
+Assigned To: abedford
Block user comment: N
Private report: N
New Comment:
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?
Previous Comments:
------------------------------------------------------------------------
[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