MySQL stored procedures with PDO or mysqli

2011 Aug 21 at 13:48 » Tagged as :mysql, mynt, pdo, mysqli,

So it looks like the whole world is moving from the PHP mysqli api to PDO (mysqli? what the hell is mysqli? guess you are asking cause you picked up PHP only during the last two years). There are millions of lines of PHP code out there that still uses the mysqli api and caveat that used to trip up countless programmers coding with mysqli is now tripping up people using PDO

 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.

  Errors like this pop up because mysql stored procedures return multiple result sets! Even if your stored procedure is not supposed to return anything at all a single resultset will be created. If you do not fetch it your next query will fail! For queries that you expect to return data the first resultset is the one that you want. You can fetch and discard the rest with a for loop that calls PDOStatement::fetchAll() . The alternative is to call PDOStatement::closeCursor   If I remember correctly you cant use stored procedures at all with the ‘original’ PHP mysql API, the one that is older than many of the kids coding with PHP.  The simple reason that MySQL  simply didn’t support stored procedures in the good old days when PHP 4 was still king. It didn’t support a few types of sub queries as well.