Best way to log PDO queries?

[allspiritseve]

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

[sweatje]

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.

[Czaries]

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...

[allspiritseve]

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.

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?

[cuberoot]

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...
http://www.sitepoint.com/forums/showthread.php?t=549387

[allspiritseve]

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...
http://www.sitepoint.com/forums/showthread.php?t=549387

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.

[allspiritseve]

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

[ghurtado]

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

[allspiritseve]

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...

[ghurtado]

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