Is there any way to get sql statement from PDOException?

Question

I would find useful to get the sql statement text that caused a PDOException when catching it. As far as I could research, the exception doesn't have that information. For example (and after reading the docs for PDOException class), I used Exception::__toString() and got something like:

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}

The problem is that I have statements executed from different functions and I want to catch all exceptions in a single catch block. If it is true that the statement can't be recovered from the exception then I can think of two possible solutions:

Storing the sql statement text in a some kind of "global" variable than can be recovered in the catch section.
Catch and manage the PDOException in each function that executes an SQL statement

I imagine there is a better way to do this.

I am afraid you will have to build your own query monitor. See [this StackOverflow answer](https://stackoverflow.com/a/7716896/3753055) for more information. — Anwar, Nov 08 '17 at 18:40
It is not that important, as having a stack trace you can navigate to the problem query in a few clicks. But anyway: 3. Create a database wrapper that would run your queries for you, and have a try-catch inside. — Your Common Sense, Nov 08 '17 at 18:45

score 3 · Accepted Answer · answered Nov 22 '17 at 20:07

I found the way to do this based on the answer https://stackoverflow.com/a/7716896/4044001. The code is the following, which includes an improvement to support questions marks (?) instead of named placeholders (:name) for parameter markers :

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

Is there any way to get sql statement from PDOException?

1 Answers1