Databases
StackOverflow2013
Postsdbo
POZend db with prepared statement doesn't fetch all data
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POZend db with prepared statement doesn't fetch all data
    primarykey
    data
    text
    <h3>First the guilty code:</h3> <pre><code>$preparedGetData = $db-&gt;prepare("CALL getData(?)"); foreach($userSet as $r) { $preparedGetData-&gt;execute(array($r['id_user'])); $rs = $preparedGetData-&gt;fetchAll(); $preparedGetData-&gt;closeCursor(); } </code></pre> <h3>Explication</h3> <p><code>getData</code> = stored procedure in mysql<br> <code>$db</code> = instance of Zend_Db_Adapter_Pdo_Mysql (using Zend version 1.10.0 ) </p> <h3>Symptoms</h3> <ul> <li><p>When I leave out <code>closeCursor</code> in the second cycle I already get error: </p> <p>PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.</p></li> <li><p>But I am using <code>fetchAll</code> !!!</p></li> <li><p>When I add the <code>closeCursor</code> the result arrives incomplete. Invoking <code>CALL getData('3872')</code> in query browser returns 1 row with 72 columns. The same done by the code above returns 1 row with only first 41 columns.</p></li> </ul> <p><strong>What I'm doing wrong?</strong></p> <h2>Edit2: Semi-solution</h2> <h3>Code updated to:</h3> <pre><code>$preparedGetData = $db-&gt;prepare("CALL getData(?)"); foreach($userSet as $r) { $preparedGetData-&gt;execute(array($r['id_user'])); $rs=array(); do { try { $partial_rowset = $preparedGetData-&gt;fetchAll(); // When I put fetchAll() after the end of the cycle, I get empty resultset. } catch (PDOException $error) { // The PDOException doesn't get caught here. Why? error_log($error); } catch (Exception $error) { error_log($error); } if ($partial_rowset) { $rs=array_merge($rs,$partial_rowset); } } while ($preparedGetData-&gt;nextRowset()); } </code></pre> <h3>Symptoms</h3> <ul> <li>Getting error:<br> PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error<br> Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[HY000]: General error' in /home/GIT/includes/Zend/Db/Statement/Pdo.php:294<br> on the line with <code>fetchAll</code>.</li> <li>This error I can catch using generic exception.</li> <li>With this code I get all 72 columns.</li> <li>I perceive this nasty because I am deliberately catching generic exception and just turning it into log. Which I guess will also become an performance issue (the cycle runs about 10 000 times).</li> </ul>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USsumid
    1. USsumid
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POZend db with prepared statement doesn't fetch all data
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI seem to remember a bug about stored procedures & trailing status... Does it chance if you call `$preparedGetData->nextRowset()` after `->fetchAll()`? What happens if you test this with pure `PDO` for the time being, does the problem still occur or is it in the `Zend` code?
      singulars
      1. POZend db with prepared statement doesn't fetch all data
      1. USWrikken
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload