Best way to log PDO queries?

Hello all,

I am sick of writing print_r ($stmt->errorInfo()); every time a query doesn’t execute. I would like a way I can review the query, the error message, and any other relevant information without writing code. I’m not sure if its better to be kept in a flat file or a database, and I’m not sure if every query should be logged, or just the ones that don’t execute. Does anyone have an implementation I could look at, or suggestions for the best way to handle this?

Thanks,

Cory

Write a decorator for your PDO connection which either logs, or remembers the SQL and throws it as part of an exception when you get a error.

As long as those exceptions are guaranteed never to be shown to end users… :wink:

I have finally had time to try my hand at this. Decorating the PDO connection worked fine, but I am having problems with decorating PDOStatement. In some instances, PDO returns false, instead of my LoggedPDOStatement class. Then I get errors like this:

Fatal error:  Call to a member function bindValue() on a non-object in /home/spirit2/Workspace/blog/classes/PostsGateway.php on line 49

Basically I wrote the subclass like this:

class LoggedPDOSTatement extends PDOStatement    {

function execute ($array)    {
    parent::execute ($array);
    $errors = parent::errorInfo();
    if ($errors[0] != '00000'):
        throw new Exception ($errors[2]);
    endif;
}

}

and I told PDO to use that instead of PDOStatement:

$this->db->setAttribute (PDO::ATTR_STATEMENT_CLASS, array ('LoggedPDOStatement', array()));

The only thing I can think of is that maybe PDO passes arguments to the constructor of PDOStatement… I can’t find that anywhere though. Any ideas?

That doesn’t really look like decoration. A decorator would wrap around the connection and the statement rather than extend them.

There’s a broken implementation here, but should be enough to get you going…

Correct me if I’m wrong, but wouldn’t that require forwarding every single method and its arguments to the connection and statement? At least with inheritance I can (or should be able to) just specify the overrides when I need them.

Well, I’ve switched to decorators that compose PDO and PDOStatement. Everything’s working fine with a little __call magic.

Can you share some code with us? I’m curious to see the actual implementation.

Sure. Here is the PDO decorator:

class LoggedPDO    {

function __construct ($db)    {
    $this->db = $db;
}

function __call ($method, $params)    {
    return call_user_func_array (array ($this->db, $method), $params); 
}

function prepare ($statement)    {
    $stmt = $this->db->prepare ($statement);
    if (is_a ($stmt, 'PDOStatement')):
        return new LoggedPDOStatement ($stmt);
    else:
        throw new Exception ('PDO did not return PDOStatement');
    endif;
}

function exec ($query)    {
    $result = $this->db->exec ($query);
    if ($this->db->errorCode() != PDO::ERR_NONE):
        $errors = $this->db->errorInfo();
        $this->paint ($query, $errors[2]);
    endif;
    return $result;
}

function paint ($query, $message)    {
    echo '<pre>';
    echo '<table cellpadding="5px">';
    echo '<tr><td colspan="2">Message: ' . $message . '</td></tr>';
    echo '<tr><td colspan="2">Query: ' . $query . '</td></tr>';
    echo '</table>';
    echo '</pre>';
}

}

I don’t actually have any logging going on yet… as I was coding, I realized it helped me out a lot more to display the query and bound parameters whenever there are errors, along with my failed tests (This is all to support my testing environment, I will implement a logger for a production environment).

Here is the decorator for PDOStatement:

class LoggedPDOStatement    {

function __construct ($stmt)    {
    $this->stmt = $stmt;
}

function execute ($params = null)    {
    $result = $this->stmt->execute ($params); 
    if ($this->stmt->errorCode() != PDO::ERR_NONE):
        $errors = $this->stmt->errorInfo();
        $this->paint ($errors[2]);
    endif;
    return $result;
}

function bindValue ($key, $value)    {
    $this->values[$key] = $value;    
    return $this->stmt->bindValue ($key, $value);
}

function paint ($message = false)    {
    echo '<pre>';
    echo '<table cellpadding="5px">';
    echo '<tr><td colspan="2">Message: ' . $message . '</td></tr>';
    echo '<tr><td colspan="2">Query: ' . $this->stmt->queryString . '</td></tr>';
    if (count ($this->values) > 0):
    foreach ($this->values as $key => $value):
    echo '<tr><th align="left" style="background-color: #ccc;">' . $key . '</th><td>' . $value . '</td></tr>';
    endforeach;
    endif;
    echo '</table>';
    echo '</pre>';
}

function __call ($method, $params)    {
    return call_user_func_array (array ($this->stmt, $method), $params); 
}

}

Enjoy…

Thanks for sharing it

I like your decorator implementation, but I must admit that your “if” block style threw me off a little cause of the indentation :wink: