PDO 无缓冲查询 [英] PDO Unbuffered queries

问题描述

我正在尝试了解 PDO 的详细信息.所以我编码了这个:

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);

我的 getValue 函数工作正常，我得到了表的下一个序列.但是当我进入 execWithParameters 时，我得到了这个异常:

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]:一般错误:2014 无法执行查询，而其他未缓冲的查询处于活动状态.考虑使用 PDOStatement::fetchAll().或者，如果您的代码只针对 mysql 运行，您可以通过设置 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY 属性来启用查询缓冲.在 D:Servidorxampp_1_7_1htdocsitacorafunc_db.php 第 77 行

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:Servidorxampp_1_7_1htdocsitacorafunc_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];
    }
}

有人可以为此提供帮助吗?PD.请不要建议做 autonumeric id，因为我是从另一个系统移植的.

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

推荐答案

问题是 mysql 在给定时间只允许一个未完成的游标.通过使用 fetch() 方法而不消耗所有挂起的数据，您将保持游标打开.

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.

推荐的方法是使用 fetchAll() 方法消耗所有数据.另一种方法是使用 closeCursor() 方法.

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;
}
?>

这篇关于PDO 无缓冲查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！