try/catch 用于循环 SQL 语句

Question

我正在尝试识别尝试/捕获失败的完整SQL语句。 这是代码：

try {
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->beginTransaction();
for($counter=0;$counter<sizeof($sql);$counter++) {
$query = $conn->prepare($sql[$counter]);
$conn->exec($sql[$counter]);
}
$conn->commit();
} catch (Exception $e) {
$err_message = "Failed while saving process....";
log_event('submit_scores.php', $err_message);
$err_message = "The following SQL statements were in the queue:";
log_event('submit_scores.php', $err_message);
for($counter=0;$counter<sizeof($sql);$counter++) {
/* t should be a tab character to indent the sql statements */
$err_message = "t" . $sql[$counter];
log_event('submit_scores.php', $err_message);
}
$conn->rollBack();
$message = "Failed: " . $e->getMessage();
echo $message;
$err_message = "t" . $message;
log_event('submit_scores.php', $err_message);
return;
}

这当前将所有 SQL 语句记录到日志文件中(通过 log_event 函数(。 然后它会生成一个错误(不正确的 SQL 语句(，但我的问题是错误消息是通用的：

2017-08-25 09:19:28 - submit_scores.php:    Failed: SQLSTATE[42000]: Syntax 
error or access violation: 1064 You have an error in your SQL syntax; check 
the manual that corresponds to your MySQL server version for the right 
syntax to use near ',210,,      ,,,     )' at line 4

我可以看到问题，但我宁愿不记录循环中的每个SQL语句，而只是记录失败的完整SQL语句。

任何帮助将不胜感激。

Answer 1

由于异常是由->exec()调用引发的，因此您可以将try/catch移动到循环中，然后仅引用您上次调用的尝试查询：

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->beginTransaction();
for($counter=0;$counter<sizeof($sql);$counter++) {
try {
$query = $conn->prepare($sql[$counter]);
$conn->exec($sql[$counter]);
} catch (Exception $e) {
echo "This is the query ($counter) that failed: " . $sql[$counter];
$conn->rollBack();
return;
}
}
$conn->commit();

Answer 2

你不应该在catch块中重用你的$counter变量和/或循环遍历所有的sql语句： 您知道最后一个失败的语句，因为它在抛出异常的那一刻$sql[$counter]。

因此，与其循环遍历所有语句，不如只记录$sql[$counter].