SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

  2. #2
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Jason Sweat ZCE - jsweat_php@yahoo.com
    Book: PHP Patterns
    Good Stuff: SimpleTest PHPUnit FireFox ADOdb YUI
    Detestable (adjective): software that isn't testable.

  3. #3
    PHP/Rails Developer Czaries's Avatar
    Join Date
    May 2004
    Location
    Central USA
    Posts
    806
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sweatje View Post
    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...

  4. #4
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sweatje View Post
    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:

    PHP Code:
    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:

    PHP Code:
    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:

    PHP Code:
    $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?

  5. #5
    SitePoint Addict
    Join Date
    Feb 2007
    Posts
    251
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cuberoot View Post
    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.

  7. #7
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, I've switched to decorators that compose PDO and PDOStatement. Everything's working fine with a little __call magic.

  8. #8
    SitePoint Evangelist ghurtado's Avatar
    Join Date
    Sep 2003
    Location
    Wixom, Michigan
    Posts
    591
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you share some code with us? I'm curious to see the actual implementation.
    Garcia

  9. #9
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ghurtado View Post
    Can you share some code with us? I'm curious to see the actual implementation.
    Sure. Here is the PDO decorator:

    PHP Code:
    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:

    PHP Code:
    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...

  10. #10
    SitePoint Evangelist ghurtado's Avatar
    Join Date
    Sep 2003
    Location
    Wixom, Michigan
    Posts
    591
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Garcia


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •