php - 有没有办法从 PDOException 中获取 sql 语句？

Question

我发现在捕获它时获取导致 PDOException 的 sql 语句文本很有用。据我所知，异常没有该信息。例如(在阅读了 PDOException 类的文档之后)，我使用了 Exception::__toString() 并得到了类似的东西:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '14' for key 'PRIMARY'
exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '14' for key 'PRIMARY'' in xx.php:64
Stack trace:
#0 xx.php(64): PDOStatement->execute(Array)
#1 xx.php(108): insertKeplerian(Object(MyConn), '14', Object(stdClass))
#2 Command line code(1): include('/srv/www/htdocs...')
#3 {main}

问题是我有从不同函数执行的语句，我想在单个 catch block 中捕获所有异常。如果语句确实无法从异常中恢复，那么我可以想到两种可能的解决方案:

1. 将 sql 语句文本存储在某种“全局”变量中可以在 catch 部分恢复。
2. 在执行 SQL 语句的每个函数中捕获并管理 PDOException

我想有更好的方法来做到这一点。

Answer 1

我根据答案 https://stackoverflow.com/a/7716896/4044001 找到了执行此操作的方法. 代码如下，其中包括对参数标记支持问号 (?) 而不是命名占位符 (:name) 的改进:

<?php
///\brief Class that extends PDOStatement to add exception handling
class MyPDOStatement extends PDOStatement
{
   protected $_debugValues = null;
   protected $_ValuePos = 0;

   protected function __construct()
   {
      // need this empty construct()!
   }

   ///\brief overrides execute saving array of values and catching exception with error logging
   public function execute($values = array())
   {
      $this->_debugValues = $values;
      $this->_ValuePos    = 0;

      try {
         $t = parent::execute($values);
      }
      catch (PDOException $e) {
         // Do some logging here
         print $this->_debugQuery() . PHP_EOL;
         throw $e;
      }

      return $t;
   }

   ///\brief Retrieves query text with values for placeholders
   public function _debugQuery($replaced = true)
   {
      $q = $this->queryString;

      if (!$replaced) {
         return $q;
      }

      return preg_replace_callback('/(:([0-9a-z_]+)|(\?))/i', array(
         $this,
         '_debugReplace'
      ), $q);
   }

   ///\brief Replaces a placeholder with the corresponding value
   //$m is the name of a placeholder
   protected function _debugReplace($m)
   {
      if ($m[1] == '?') {
         $v = $this->_debugValues[$this->_ValuePos++];
      }
      else {
         $v = $this->_debugValues[$m[1]];
      }
      if ($v === null) {
         return "NULL";
      }
      if (!is_numeric($v)) {
         $v = str_replace("'", "''", $v);
      }

      return "'" . $v . "'";
   }
}

///\brief Class that encapsulates DB connection parameters and configuration
class MyConn extends PDO
{
   function __construct()
   {
      $servername = "localhost";
      $username   = "root";
      $password   = "xxx";
      $dbname     = "new_att";

      parent::__construct("mysql:host=$servername;dbname=$dbname", $username, $password);

      //Set connection to raise exception when error
      $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
      //Set connection to use the derived class MyPDOStatement instead of PDOStatement for statements
      $this->setAttribute(PDO::ATTR_STATEMENT_CLASS, array(
         'MyPDOStatement',
         array()
      ));
   }
}
?>