Can't iterate over a PDOStatement object using foreach()

Bug #39527	Can't iterate over a PDOStatement object using foreach()
Submitted:	2006-11-16 05:03 UTC	Modified:	2006-12-02 17:55 UTC
From:	jpokotilow at tuneteller dot com	Assigned:
Status:	Closed	Package:	PDO related
PHP Version:	5.2.0	OS:	Mac OS X 10.4.8
Private report:	No	CVE-ID:	None

[2006-11-16 05:03 UTC] jpokotilow at tuneteller dot com

Description:
------------
I invoke PDO::query() twice, with two different valid 
SQL queries from within a class method. (Note: I make sure to 
invoke fetchAll() on the PDOStatement object returned by the 
first invocation before invoking PDO::query() a second time. 
Update: I just found out that if I set the first PDOStatement 
object to NULL, everything works as intended. HOWEVER, 
invoking fetchAll() should suffice, should it not?) I return 
the PDOStatement object generated by the second PDO::query() 
invocation. I'm unable to iterate over the object with a 
foreach() construct even though there ought to be results. 
Invoking fetchAll() on the object returns an empty array.

If I set the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute to 
true, I don't have this problem, but I'm pretty sure I 
shouldn't have it either way.

Reproduce code:
---------------
http://pastebin.com/825493

Expected result:
----------------
I expect to be able to iterate over rows associated with 
$broken_iterable_results ... or I would expect for 
$broken_iterable_results->fetchAll() to return rows associated 
with the PDOStatement object. At the very least, I expect for 
some error to be generated if I'm doing anything wrong.

Note: With PDO::ERRMODE_EXCEPTION set to true, there is an 
exception thrown.

Actual result:
--------------
Iterating over $broken_iterable_results results does nothing. 
Invoking $broken_iterable_results->fetchAll() returns an 
empty array.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports

[2006-11-16 05:09 UTC] jpokotilow at tuneteller dot com

I feel that I neglected to mention one important point, which 
is that I *can* iterate over the PDOStatement object prior to 
returning it from the class method. The object only becomes 
dysfunctional when it's operated on outside of the class 
method in question.

[2006-11-16 09:45 UTC] tony2001@php.net

Thank you for this bug report. To properly diagnose the problem, we
need a short but complete example script to be able to reproduce
this bug ourselves. 

A proper reproducing script starts with <?php and ends with ?>,
is max. 10-20 lines long and does not require any external 
resources such as databases, etc. If the script requires a 
database to demonstrate the issue, please make sure it creates 
all necessary tables, stored procedures etc.

Please avoid embedding huge scripts into the report.

[2006-11-16 23:14 UTC] jpokotilow at tuneteller dot com

<?php

$db = new PDO('mysql:host=localhost;dbname=pdotest', 'root', 
'pokot0');

$db->exec("CREATE TABLE pdotest_table1 (col1 varchar(16))");
$db->exec("INSERT INTO pdotest_table1 VALUES ('Hello, 
World!')");

class PDOStatementTester {
    function get_results_broken() {
        global $db;
        
        $query = "SELECT COUNT(1) FROM pdotest_table1";
        $count_pdostmt = $db->query($query);

        echo("Getting ".$count_pdostmt->fetchColumn()." 
results.\n");
        $count_pdostmt->fetchAll();
        
        $query = "SELECT * FROM pdotest_table1";
        $pdostmt = $db->query($query);
        
        if (!$pdostmt) var_dump($db->errorInfo());

        return $pdostmt;
    }
}

$tester = new PDOStatementTester();

$broken_iterable_results = $tester->get_results_broken();
foreach ($broken_iterable_results as $res) var_dump
($res); // Nothing gets printed here.

?>

[2006-12-02 17:55 UTC] iliaa@php.net

This bug has been fixed in CVS.

Snapshots of the sources are packaged every three hours; this change
will be in the next snapshot. You can grab the snapshot at
http://snaps.php.net/.
 
Thank you for the report, and for helping us make PHP better.