drgbpq5930
2015-08-12 11:28
浏览 61

为什么PDO rollback()需要closeCursor()才能反映在输出中?

In the following code, transaction fails and roll back is invoked.

However, in terms of the output:

If I remove the closeCursor(), payer`s balance is printed as -200$.

If I specify the closeCursor(), payer`s balance is printed as 0 which is the expected behavior.

This happens because result from the first select statement isn't being cleared before the second one is run.

Why do we need to close the connection for the roll back to be reflected in output? In both cases, roll back is invoked, so the value 0 is specified in DB. So I expect the balance to be printed as 0.

I dont see the relation between closing a connection DB, and getting latest DB values. Fetch() gets the value from DB, so if the value is 0, how come "it thinks" it`s -200?

This applies only to SQLite. MySQL will print 0 regardless if you use closeCursor().

<?php
try {
    require_once '../../includes/pdo_connect.php';
    // Set up prepared statements transfer from one account to another
    $amount = 0;
    $payee = 'John White';
    $payer = 'Jane Black';
    $debit = 'UPDATE savings SET balance = balance - :amount WHERE name = :payer';
    ####1st SQL#### $getBalance = 'SELECT balance FROM savings WHERE name = :payer';  
    $credit = 'UPDATE savings SET balance = balance + :amount WHERE name = :payee';

    $pay = $db->prepare($debit);
    $pay->bindParam(':amount', $amount);
    $pay->bindParam(':payer', $payer);

    $check = $db->prepare($getBalance);
    $check->bindParam(':payer', $payer);

    $receive = $db->prepare($credit);
    $receive->bindParam(':amount', $amount);
    $receive->bindParam(':payee', $payee);

    // Transaction
    $db->beginTransaction();
    $pay->execute();
    if (!$pay->rowCount()) {
        $db->rollBack();
        $error = "Transaction failed: could not update $payer's balance.";
    } else {
        // Check the remaining balance in the payer's account
        $check->execute();
        $bal = $check->fetchColumn();
        ########## $check->closeCursor(); 

        // Roll back the transaction if the balance is negative
        if ($bal < 0) {
            $db->rollBack();
            $error = "Transaction failed: insufficient funds in $payer's account.";
        } else {
            $receive->execute();
            if (!$receive->rowCount()) {
                $db->rollBack();
                $error = "Transaction failed: could not update $payee's balance.";
            } else {
                $db->commit();
            }
        }
    }
} catch (Exception $e) {
    $error = $e->getMessage();
}
?>
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>PDO Transaction</title>
    <link href="../../styles/styles.css" rel="stylesheet" type="text/css">
</head>
<body>
<h1>PDO Transactions</h1>
<?php
if (isset($error)) {
    echo "<p>$error</p>";
}
?>
<table>
    <tr>
        <th>Name</th>
        <th>Balance</th>
    </tr>
            ####2nd SQL####<?php foreach ($db->query('SELECT name, balance FROM savings') as $row) { ?>
    <tr>
        <td><?php echo $row['name']; ?></td>
        <td>$<?php echo number_format($row['balance'], 2); ?></td>
    </tr>
    <?php } ?>
</table>
</body>
</html>
  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • douhuitan5863 2015-08-13 15:29
    已采纳

    The above reply by Ryan Vincent is the correct one.

    So I am specifying this to mark my question as solved.

    Thanks,

    Qwerty

    已采纳该答案
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题