PDO Unbuffered queries

Question

Hello guys, I'm trying to get into PDO details. So I coded this:

$cn = getConnection();

// get table sequence
$comando = "call p_generate_seq('bitacora')";
$id = getValue($cn, $comando);

//$comando = 'INSERT INTO dsa_bitacora (id, estado, fch_creacion) VALUES (?, ?, ?)';
$comando = 'INSERT INTO dsa_bitacora (id, estado, fch_creacion) VALUES (:id, :estado, :fch_creacion)';
$parametros = array (
    ':id'=> (int)$id,
    ':estado'=>1,
    ':fch_creacion'=>date('Y-m-d H:i:s')
);
execWithParameters($cn, $comando, $parametros);

my getValue function works fine, and I get the next sequence for the table. But when I get into execWithParameters, i get this exception:

PDOException: 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. in D:\Servidor\xampp_1_7_1\htdocs\bitacora\func_db.php on line 77

I tried to modify the connection attributes but it doesn't work.

These are my core db functions:

function getConnection() {
    try {
     $cn = new PDO("mysql:host=$host;dbname=$bd", $usuario, $clave, array(
       PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      ));

     $cn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
     return $cn;
    } catch (PDOException $e) {
     print "Error!: " . $e->getMessage() . "<br/>";
     die();
    }
}
function getValue($cn, $comando) {
    $resul = $cn->query($comando);
     if (!$resul) return null;
     while($res = $resul->fetch()) {
      $retorno = $res[0][0];
      break;
     }
     return $retorno;
}
function execWithParameters($cn, $comando, $parametros) {
    $q = $cn->prepare($comando);
    $q->execute($parametros);
    if ($q->errorInfo() != null) {
     $e = $q->errorInfo();
     echo $e[0].':'.$e[1].':'.$e[2];
    }
}

¿Somebody who can shed a light for this? PD. Please do not suggest doing autonumeric id, cause i am porting from another system.

Answer 1

The problem seems to be---I'm not too familiar with PDO--- that after your getValue call returns, the query is still bound to the connection (You only ever ask for the first value, yet the connection returns several, or expects to do so).

Perhaps getValue can be fixed by adding

$resul->closeCursor();

before the return.

Otherwise, if queries to getValue will always return a single (or few enough) value, it seems that using fetchAll will be preferred.

Answer 2

A friend of mine had very much the same problem with the xampp 1.7.1 build. After replacing xampp/php/* by the 5.2.9-2 php.net build and copying all necessary files to xampp/apache/bin it worked fine.

Answer 3

The issue is that mysql only allows for one outstanding cursor at a given time. By using the fetch() method and not consuming all the pending data, you are leaving a cursor open.

The recommended approach is to consume all the data using the fetchAll() method. An alternative is to use the closeCursor() method.

If you change this function, I think you will be happier:

<?php
function getValue($cn, $comando) {
    $resul = $cn->query($comando);
    if (!$resul) return null;
    foreach ($resul->fetchAll() as $res) {
            $retorno = $res[0];
            break;
    }
    return $retorno;
}
?>

Answer 4

Has anyone had any luck using MySQL stored procedures with PDO? My first invocation works just fine, but any subsequent SQL - inline statement or stored procedure - BOMBS and I get that HY000 error.

Here is a snippet from a script I used to test this:

$PDOStatement = $PDO->prepare('SELECT * FROM MagicCards WHERE cardID = ?');

$PDOStatement->bindValue(1, 1600);

$PDOStatement->execute();

$PDOStatement->setFetchMode(\PDO::FETCH_ASSOC);

$rows = $PDOStatement->fetchAll();

$PDOStatement->closeCursor();

$PDOStatement = NULL;

print_r($rows); // works as expected



$PDOStatement = $PDO->prepare('CALL uspGetMagicCardByKey(?)');

$PDOStatement->bindValue(1, 10);

$PDOStatement->execute();

$PDOStatement->setFetchMode(\PDO::FETCH_ASSOC);

$rows = $PDOStatement->fetchAll();

$PDOStatement->closeCursor();

$PDOStatement = NULL;

print_r($rows); //works as expected

$PDOStatement = $PDO->prepare('SELECT * FROM MagicCards WHERE cardID = ?');

$PDOStatement->bindValue(1, 1600);

$PDOStatement->execute(); //errors out right here with the HY000 error

$PDOStatement->setFetchMode(\PDO::FETCH_ASSOC);

$rows = $PDOStatement->fetchAll();

$PDOStatement->closeCursor();

$PDOStatement = NULL;

print_r($rows); //doesn't get this far

Answer 5

If you're using XAMPP 1.7.1, you just need to upgrade to 1.7.2.