Zend Framework

• Type: Bug
• Status: Open
• Priority: N/A
• Resolution: Unresolved
• Affects Version/s: None
• Fix Version/s: None
• Component/s: Zend_Db
• Labels:

  None

include 'Zend/Loader.php';
        Zend_Loader::registerAutoload();

  $pdoParams = array(
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true
);

$params = array(
    'host'           => 'localhost',
    'username'       => 'test',
    'password'       => 'xxxxxx',
    'dbname'         => 'test_db',
    'driver_options' => $pdoParams
);

$db = Zend_Db::factory('Pdo_Mysql', $params);

        $sql0="DROP TABLE IF EXISTS someTable";
        $sql1="CREATE TABLE someTable (id INT);";
        $sql2="INSERT INTO someTable (id) VALUES (1)";
        $db->getConnection()->exec($sql0);
        $db->getConnection()->exec($sql1);
        $db->getConnection()->exec($sql2);

The above code works fine. However, attempting to execute more than one query in each exec() call does not:

$sql1="DROP TABLE IF EXISTS someTable; CREATE TABLE someTable (id INT);";
        $sql2="INSERT INTO someTable (id) VALUES (1)";
        $db->getConnection()->exec($sql1);
        $db->getConnection()->exec($sql2);

Execution of $sql2 fails with
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.

Note that

$sql1="DROP TABLE IF EXISTS someTable; CREATE TABLE someTable (id INT);";
        $db->getConnection()->exec($sql1);

works fine on it's own - it's the subsequent call that fails.

This is more irritating than it appears, as it makes loading query data from files more fiddly - for instance in my unit test setUp() I have something like

$sql = file_get_contents('createTestSchema.sql');
      $db->getConnection()->exec($sql);

where createTestSchema.sql consists of a number of CREATE TABLE & INSERT statements. This then basically kills the $db object - it can't be used for any other queries without giving this error.

Assuming it's linked to the similar stored procedure problem, there's some discussion here that it's specific to FreeBSD; I'm certainly running FreeBSD but haven't tested it elsewhere.

• Assignee:

  Ralph Schindler

  Reporter:

  Mark

• Created:

  18/Jan/09 5:12 AM

  Updated:

  16/Aug/09 9:28 PM