問題描述

我正在嘗試了解 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 無緩沖查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！