How to show MySQL errors from a failed Modx/xPDO query object?

Question

How do I show the error mysql throws when I try to insert data into a custom table and the insert fails?

For example, below a bit of code that should(will) fail with an SQL error.

$insert = "some insert sql statement that will fail";

$myquery = $modx->query($insert);

    if(!$myquery){

        echo 'error occurred! <br>';

    }

How do I return what the error actually was [i.e. column mismatch, unique id exists etc.]?

Answer 1

Based on the examples in the xPDO Getting Started guide, $modx in this context appears to be a class extending PDO and the result resource object $myquery is likely a PDOStatement object.

You can therefore set an exception error mode on $modx as you would with a normal PDO object.

$modx->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

to force it to throw errors on failure. By default, PDO errors silently; its error modes are documented here.

Correction: Looking into the xPDO source it does not extend PDO, but rather contains a PDO object as a property and implement PDO methods, passing them through to its connection property. So the setAttribute() call will be passed through to the underlying PDO object and should work accordingly.

The xPDO constructor extends functionality from a normal PDO constructor slightly, and accepts an array of options in the 5th parameter where you may set the error mode, rather than setting it later via setAttribute():

$xpdo = new xPDO($dsn, $user, $password, [], [PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION]);

Whichever method you choose to set it, you may wrap your code in a try/catch block to catch exceptions of type PDOException on error:

    try {
      $insert = "some insert sql statement that will fail";
      $myquery = $modx->query($insert);
   }
    catch (PDOException $e) {
      echo 'error occurred! ' . $e->getMessage() . '<br>';
    }

You may also more simply set the errormode to PDO::ERRMODE_WARNING and PHP will instead just emit E_WARNING messages, which unlike exceptions, are non-fatal.

I was able to verify all of this works as expected by setting up a quick test with xPDO.

Answer 2

There is a little bit easier way to track your custom xpdo request.

$c = $modx->newQuery('modResource');
$c->where(array(
    'id1' => 1
));

// print request for control
print_r($c->toSQL());

$s = $c->prepare();
$s->execute();

print_r($s->errorInfo());

After execution we can catch an error:

Array ( [0] => 42S22 [1] => 1054 [2] => Unknown column 'modResource.id1' in 'where clause' )

It's all because xpdo use pdo and controls execution with it's help. Some code from xpdo source:

/**
 * @see http://php.net/manual/en/function.pdo-errorinfo.php
 */
public function errorInfo() {
    if (!$this->connect()) {
        return false;
    }
    return $this->pdo->errorInfo();
}