問(wèn)題描述

我正在嘗試了解 PDO 的詳細(xì)信息.所以我編碼了這個(gè):

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 函數(shù)工作正常，我得到了表的下一個(gè)序列.但是當(dāng)我進(jìn)入 execWithParameters 時(shí)，我得到了這個(gè)異常:

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]:一般錯(cuò)誤:2014 無(wú)法執(zhí)行查詢，而其他未緩沖的查詢處于活動(dòng)狀態(tài).考慮使用 PDOStatement::fetchAll().或者，如果您的代碼只針對(duì) mysql 運(yùn)行，您可以通過(guò)設(shè)置 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY 屬性來(lái)啟用查詢緩沖.在 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.

這些是我的核心數(shù)據(jù)庫(kù)函數(shù):

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.請(qǐng)不要建議做 autonumeric id，因?yàn)槲沂菑牧硪粋€(gè)系統(tǒng)移植的.

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

推薦答案

問(wèn)題是 mysql 在給定時(shí)間只允許一個(gè)未完成的游標(biāo).通過(guò)使用 fetch() 方法而不消耗所有掛起的數(shù)據(jù)，您將保持游標(biāo)打開.

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() 方法消耗所有數(shù)據(jù).另一種方法是使用 closeCursor() 方法.

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

如果你改變這個(gè)功能，我想你會(huì)更開心:

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

這篇關(guān)于PDO 無(wú)緩沖查詢的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！