Page 1 of 1

PDO, fetch and closeCursor

Posted: Fri Apr 03, 2009 10:11 am
by Rik
In the PHP manual and in some comments is said that result sets must be closed before making a new one or you may got errors.

E.g.
http://it.php.net/manual/en/pdo.query.php
If you do not fetch all of the data in a result set before issuing your next call to PDO::query(), your call may fail. Call PDOStatement::closeCursor() to release the database resources associated with the PDOStatement object before issuing your next call to PDO::query().
or

http://it.php.net/manual/en/pdostatemen ... .php#84495
Be careful with fetch() when you use prepared statements and MySQL (I don`t know how it is with other databases). Fetch won`t close cursor and won`t let you send any other query, even if your result set has only one row, .
If you use $statement->fetch(), you will also have to use $statement->closeCursor() afterwards, to be albe to execute another query.
Alternatively you can use $statement->fetchAll() without $statement->closeCursor().
That means that one has to make more than one connection to DB if multiple result sets are needed at the same time. Is that true? :?:

Let's see:
I test that with mySql 5.0.51a and php PHP Version 5.2.5 under Windows XP with xampp 1.6.2

Code: Select all

 
        $stm1=$dbh->query("SELECT * FROM libri WHERE autore='rik'");
 
        $row1=$stm1->fetch();
        print_r($row1);
 
        $stm2=$dbh->query("SELECT * FROM libri WHERE autore='bubu'");
 
        $row2=$stm2->fetch();
        print_r($row2);
 
        $row1=$stm1->fetch();
        print_r($row1);
 
        $row2=$stm2->fetch();
        print_r($row2);
 
It works correctly despite I mix both query calls and fetch :)
The code above show correctly alternatively 1 item of a result set and of the other one without loosing anything, nor showing errors.

So, what's going on? Maybe that problem is only with some DB drivers? Are there special conditions to got the errors php manual is speaking about? Or the new PDO object corrected the problem and documentation still contains old notes (not updated)?.

It is important because that make the difference in approaching connection to DB: 1 for every resultsets or 1 for all?

Re: PDO, fetch and closeCursor

Posted: Sun Apr 05, 2009 4:23 am
by Rik
I've tried a mix of other multiple calls with only 1 connection plus query without closing the same and the other statements. On MySql everything OK.

So there is no need to closeCursor() to have multiple resultset on MySql, and the warning to use closeCursor() before the next query doesn't even seem to be a global solution because someone complain that closeCursor give problems on store procedure. See http://it.php.net/manual/en/pdostatemen ... .php#65378

So we have a DB abstraction that must be use making slalom. :?

Do you know where to find a list of DB with the problem?

I'd like to have low resource impact at least on MySql, Sqlite, and eventually pgSql... so I'd like to avoid what the PDO manual counsel to do, that is, 1 new connection for every open and resultset.