ID: 39858
Comment by: ile at suomi dot finland
Reported By: develar at gmail dot com
Status: Assigned
Bug Type: PDO related
Operating System: Windows XP SP2
PHP Version: 5.2.0
Assigned To: wez
New Comment:
This bug occurs with Apache/2.0.63 (Win32) PHP/5.2.6 and MySQL
5.0.51b.
I don't use PDO. I use mysqli calls.
The work-around by bepoteat helps though.
Previous Comments:
------------------------------------------------------------------------
[2008-05-02 16:48:43] bepoteat at yahoo dot com
After looking at the PHP manual, I realized I could reduce the loop to
a single line:
while (mysqli_next_result($conn));
I didn't even have to use mysqli_free_result (but it is good practice).
------------------------------------------------------------------------
[2008-05-02 16:24:57] bepoteat at yahoo dot com
I believe paulsidekick is correct. I am working on a page that
repeatedly calls a stored proc that returns a resultset. After using
the expected results from the first call, I tried moving to the next
resultset and displaying the column values. I got nothing. Then I
tried using
mysqli_free_result($rs);
while (mysqli_more_results($conn)) {
mysqli_next_result($conn);
echo count($row);
mysqli_free_result($rs);
}
and got "0". So I decided to just get rid of the empty resultset.
Below is the basic structure of the code that worked for me (note the
while loop).
$conn = mysqli_connect("server", "username", "password");
for ($i=1; $i<$someNumber; $i++) {
$rs = mysqli_query($conn, "CALL spMyProc(param1, param2)");
if ($rs && $row = mysqli_fetch_asoc($rs)) {
//some code to use results
} else {
//error handler
}
mysqli_free_result($rs);
//Add this section to dispose of extra resultset.
while (mysqli_more_results($conn)) {
mysqli_next_result($conn);
mysqli_free_result($rs);
}
}
mysqli_close($conn);
I know this doesn't exactly fix the problem, but it is a workaround
that involves a minimal amount of code.
(By the way, why doesn't the CAPTCHA box show up in Firefox? I had to
use Internet Exploiter to post this. I thought PHP was all about being
open source!)
------------------------------------------------------------------------
[2008-04-17 14:44:34] james dot lewis at americanmobileventures dot com
Also having this error. PHP 5.2.5 on XP SP2, with Apache2 (XAMPP 1.6.6)
------------------------------------------------------------------------
[2008-04-05 21:04:45] mgrdinic at sledxchange dot com
One last thing:
It should be noted that you can "workaround" this issue by simply
instantiating a whole new PDO object after every call that returns a
result set.
// create a PDO instance up here somewhere and perform your query...
// get the return values you need...
$result = $sth->fetchAll ();
// and just create a new object...
try {
$dbh = new PDO ( $dsn, $user, $pass );
$dbh->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, 1);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
} catch ( PDOException $e ) {
die ( 'PDO Connection Failed: ' . $e->getMessage () );
}
// now you can query again without the error.
It seem like the solution is simple to do. In fact, this guy seems to
be on the right track:
http://bugs.php.net/bug.php?id=42499
Why isn't this done!
Oh well, hopefully the above, if not totally wrong : ) will help
someone else.
------------------------------------------------------------------------
[2008-04-05 20:53:08] mgrdinic at sledxchange dot com
Same problem here-I'm on Vista Ultimate SP1 and IIS 7 MySQL 5.0.45
The trick is regular NON-Select queries work as expected. It's when
your Stored Procedures return result sets the problems show up.
So for example, if the first query performs a one off select and
closes, the second query is hit with the "SQLSTATE[HY000]: General
error: 2014 Cannot execute queries while other unbuffered queries are
active error". And yes, that's with using the fetchAll.
However, if I remove the select statement from the first procedure and
run the code again, both stored procedures work fine.
I've tried the latest snap-shots, but unfortunately I couldn't get pdo
to even load. Any ideas? Updates?
------------------------------------------------------------------------
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/39858
--
Edit this bug report at http://bugs.php.net/?id=39858&edit=1
