ZF-4951: CLONE -Can't execute multiple stored procedures with Pdo_Mysql

Description

This bug is not fixed yet. Details: ZF v1.6.2, PHP v5.1.6, Mysql V5.0.45.

Irregardless of how PDO:MYSQL_ATTR_USE_BUFFERED_QUERY is set, or using fetchAll() if you run two store procedures you get the same error:

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.

Example code: $registry = Zend_Registry::getInstance(); $tempdata = array(); $stmt = $registry->dbAdapter->query("CALL mgmtcomm_yearlybarchart('2008-01-01','2008-10-01')"); // Store results in array $rows = $stmt->fetchAll(); foreach($rows as $row) { $tempdata[] = number_format($row['data'],2); $numrows++; } // Free DB Query resources $stmt->closeCursor(); print_r($tempdata); $stmt = $registry->dbAdapter->query("CALL mgmtcomm_yearlybarchart('2008-01-01','2008-10-01')"); // Store results in array $rows = $stmt->fetchAll(); foreach($rows as $row) { $tempdata[] = number_format($row['data'],2); $numrows++; } // Free DB Query resources $stmt->closeCursor(); print_r($tempdata);

Patrick Calkins writes:

{quote} Code:


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

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

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

// Stored procedure returns a single row
$stmt = $db->prepare('CALL get_customer_by_id(:customerId)');
$stmt->bindParam('customerId', $customerId, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll();
print_r($result);

$stmt->closeCursor();

// Stored procedure returns a single row
$stmt = $db->prepare('CALL get_address_by_id(:customerId)');
$stmt->bindParam('customerId', $customerId, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll();
print_r($result);

This will consistently throw this error: '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.'

If you insert $stmt->nextRowset(); before $stmt->closeCursor(); it will throw this error: 'SQLSTATE[HYC00]: Optional feature not implemented'

This appears to be a bug, and I haven't found any more info as to its status lately. Is this correct?? {quote}

Comments

Assigning to Ralph to get closure on this issue.

Just my 5 cents: On Linux (ubuntu 9.4) with PHP5 from Zend (Zend Server CE), I don't have the issue

Hi Patric Calkins, i am relatively new to zend framework but i think i have find solution to this problem. i can call procedures multiple time. you dont even have to set attribute. you can simply avoid this problem using changing cursor name. because even if you call method $stmt->closeCursor(). $stmt still retains the prepare statement properties. so it will shout error if you try to overwrite its old properties. your code might look something like follows * $db = Zend_Db::factory('Pdo_Mysql', $params);

// Stored procedure returns a single row $stmt = $db->prepare('CALL get_customer_by_id(:customerId)'); $stmt->bindParam('customerId', $customerId, PDO::PARAM_INT); $stmt->execute(); $result = $stmt->fetchAll(); print_r($result);

$stmt->closeCursor();

// Stored procedure returns a single row $stmt2 = $db->prepare('CALL get_address_by_id(:customerId)'); $stmt2->bindParam('customerId', $customerId, PDO::PARAM_INT); $stmt2->execute(); $result = $stmt2->fetchAll(); print_r($result);*

regards Rohit Deshmukh