php 函数中的 PDO try-catch 用法

PDO try-catch usage in functions

提问by andyk

I'm thinking of using PDO in all of my future webapp. Currently (using what I've learned from SO so far), what I have in my site to handle database connection is a Singleton class like this :

我正在考虑在我未来的所有 web 应用程序中使用 PDO。目前（使用到目前为止我从 SO 中学到的知识），我在我的站点中处理数据库连接的是一个像这样的 Singleton 类：

class DB {

    private static $instance = NULL;
    private static $dsn      = "mysql:host=localhost;dbname=mydatabase;";
    private static $db_user  = 'root';
    private static $db_pass  = '0O0ooIl1';

    private function __construct() 
    {

    }
    private function __clone()
    {

    }   
    public static function getInstance() {

        if (!self::$instance)
        {           
            self::$instance = new PDO(self::$dsn, self::$db_user, self::$db_pass);
            self::$instance-> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
        return self::$instance;
    }
}

and another file (functions.php) with content-specific functions looking exactly like this one :

和另一个文件 (functions.php) 与内容特定的功能看起来完全一样：

function get_recent_activities ()
{    
    try
    {    
        $db = DB::getInstance();
        // --prepare and execute query here, fetch the result--
        return $my_list_of_recent_activities;
    }
    catch (PDOException $e)
    {
        return "some fail-messages";
    }
}
...

meaning that I have to repeat the try .. catchpart in all of the functions.

这意味着我必须try .. catch在所有功能中重复该部分。

My questions are :

我的问题是：

1. How should I make that more efficient ? (eg. not having to repeat try..catchin all functions, and yet still able to return different "fail-message" on each one)
2. Is this already a good practice ? I'm still new at PDO and OOP (still a lot more to learn), so (as of now), I can't really see any disadvantages or things that can be improved in there.

1. 我应该如何提高效率？（例如，不必try..catch在所有函数中重复，但仍然能够在每个函数上返回不同的“失败消息”）
2. 这已经是一个好习惯了吗？我还是 PDO 和 OOP 的新手（还有很多东西要学习），所以（截至目前），我真的看不出有任何缺点或可以改进的地方。

I'm sorry if that seems unclear or too long. Thanks in advance.

如果这看起来不清楚或太长，我很抱歉。提前致谢。

回答by pd.

Your implementation is just fine, and it'll work perfectly well for most purposes.

您的实现很好，并且在大多数情况下都能很好地工作。

It's not necessary to put every query inside a try/catch block, and in fact in most cases you actually don't want to. The reason for this is that if a query generates an exception, it's the result of a fatal problem like a syntax error or a database issue, and those are not issues that you should be accounting for with every query that you do.

没有必要将每个查询都放在 try/catch 块中，事实上，在大多数情况下，您实际上并不想这样做。这样做的原因是，如果查询生成异常，则它是致命问题（如语法错误或数据库问题）的结果，而这些不是您在执行每个查询时都应该考虑的问题。

For example:

例如：

try {
    $rs = $db->prepare('SELECT * FROM foo');
    $rs->execute();
    $foo = $rs->fetchAll();
} catch (Exception $e) {
    die("Oh noes! There's an error in the query!");
}

The query here will either work properly or not work at all. The circumstances where it wouldn't work at all should not ever occur with any regularity on a production system, so they're not conditions that you should check for here. Doing so is actually counterproductive, because your users get an error that will never change, and you don't get an exception message that would alert you to the problem.

这里的查询要么正常工作，要么根本不工作。根本不工作的情况不应该在生产系统上以任何规律发生，因此它们不是您应该在这里检查的条件。这样做实际上适得其反，因为您的用户会收到永远不会改变的错误，并且您不会收到提醒您问题的异常消息。

Instead, just write this:

相反，只需写下：

$rs = $db->prepare('SELECT * FROM foo');
$rs->execute();
$foo = $rs->fetchAll();

In general, the only time that you'll want to catch and handle a query exception is when you want to do something else if the query fails. For example:

一般而言，您唯一需要捕获和处理查询异常的时间是在查询失败时您想执行其他操作。例如：

// We're handling a file upload here.
try {
    $rs = $db->prepare('INSERT INTO files (fileID, filename) VALUES (?, ?)');
    $rs->execute(array(1234, '/var/tmp/file1234.txt'));
} catch (Exception $e) {
    unlink('/var/tmp/file1234.txt');
    throw $e;
}

You'll want to write a simple exception handler that logs or notifies you of database errors that occur in your production environment and displays a friendly error message to your users instead of the exception trace. See http://www.php.net/set-exception-handlerfor information on how to do that.

您需要编写一个简单的异常处理程序，用于记录或通知您生产环境中发生的数据库错误，并向您的用户显示友好的错误消息而不是异常跟踪。有关如何执行此操作的信息，请参阅http://www.php.net/set-exception-handler。

回答by Noah Goodrich

A couple of caveats here are:

这里有几个注意事项：

• This code is written to take several legacy issues into account such as the database logging and database configuration management.
• I would strongly recommend that you look at an existing solution before building your own. A lot of people think to themselves when they start out that they don't want to use an existing framework or library because they're too big, require too much time to learn, etc., but after having been one of these people I can't state emphatically enough that I am leaving my custom framework and wrapper classes to move to a framework. I am looking to move to Zend but there are a number of excellent choices available.

• 编写此代码是为了考虑几个遗留问题，例如数据库日志记录和数据库配置管理。
• 我强烈建议您在构建自己的解决方案之前先查看现有解决方案。很多人在开始时会想，他们不想使用现有的框架或库，因为它们太大了，需要太多时间来学习等，但在成为这些人中的一员后，我不能强调地说我要离开我的自定义框架和包装类转移到一个框架。我想迁移到 Zend，但有很多不错的选择。

Oh, I should point out that this point illustrates how one could wrap a single function to handle all of the exception handling for your queries. I don't write try catch blocks almost anywhere else now because the stack trace from the query gives me all of the information that I need to debug the problem and fix it.

哦，我应该指出，这一点说明了如何包装单个函数来处理查询的所有异常处理。我现在几乎不会在其他任何地方编写 try catch 块，因为来自查询的堆栈跟踪为我提供了调试问题和修复问题所需的所有信息。

Here is my current PDO wrapper class implementation:

这是我当前的 PDO 包装类实现：

class DB extends PDO 
{
    // Allows implementation of the singleton pattern -- ndg 5/24/2008
    private static $instance;

    // Public static variables for configuring the DB class for a particular database -- ndg 6/16/2008
    public static $error_table;
    public static $host_name;
    public static $db_name;
    public static $username;
    public static $password;
    public static $driver_options;
    public static $db_config_path;



    function __construct($dsn="", $username="", $password="", $driver_options=array()) 
    {
        if(isset(self::$db_config_path))
        {
            try 
            {
                if(!require_once self::$db_config_path)
                {
                    throw new error('Failed to require file: ' . self::$db_config_path); 
                }
            } 
            catch(error $e) 
            {
                $e->emailAdmin();
            }
        }
        elseif(isset($_ENV['DB']))
        {
            self::$db_config_path = 'config.db.php';

            try 
            {
                if(!require_once self::$db_config_path)
                {
                    throw new error('Failed to require file: ' . self::$db_config_path); 
                }
            } 
            catch(error $e) 
            {
                $e->emailAdmin();
            }
        }

        parent::__construct("mysql:host=" . self::$host_name . ";dbname=" .self::$db_name, self::$username, self::$password, self::$driver_options);
        $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('QueryStatement', array($this)));

        if(!isset(self::$error_table))
        {
            self::$error_table = 'errorlog_rtab';
        }
    }

    /**
     * Return a DB Connection Object
     *
     * @return DB
     */
    public static function connect()
    {

        // New PDO Connection to be used in NEW development and MAINTENANCE development
        try 
        {
            if(!isset(self::$instance))
            {   
                if(!self::$instance =  new DB())
                {
                    throw new error('PDO DB Connection failed with error: ' . self::errorInfo());
                }
            }

            return self::$instance;
        }
        catch(error $e)
        {
            $e->printErrMsg();
        }
    }

    /**
     * Returns a QueryBuilder object which can be used to build dynamic queries
     *
     * @return QueryBuilder
     * 
     */
    public function createQuery()
    {
        return new QueryBuilder();
    }

    public function executeStatement($statement, $params = null, $FETCH_MODE = null)
    {
        if($FETCH_MODE == 'scalar')
        {
            return $this->executeScalar($statement, $params);   
        }


        try {
            try {
                if(!empty($params))
                {
                    $stmt = $this->prepare($statement);
                    $stmt->execute($params);
                }
                else 
                {
                    $stmt = $this->query($statement);
                }
            }
            catch(PDOException $pdo_error)
            {
                throw new error("Failed to execute query:\n" . $statement . "\nUsing Parameters:\n" . print_r($params, true) . "\nWith Error:\n" . $pdo_error->getMessage());
            }
        }
        catch(error $e)
        {
            $this->logDBError($e);
            $e->emailAdmin();
            return false;
        }

        try 
        {
            if($FETCH_MODE == 'all')
            {
                $tmp =  $stmt->fetchAll();
            }
            elseif($FETCH_MODE == 'column')
            {
                $arr = $stmt->fetchAll();

                foreach($arr as $key => $val)
                {
                    foreach($val as $var => $value)
                    {
                        $tmp[] = $value;
                    }
                }           
            }
            elseif($FETCH_MODE == 'row') 
            {
                $tmp =  $stmt->fetch();
            }
            elseif(empty($FETCH_MODE))
            {
                return true;
            }
        }
        catch(PDOException $pdoError)
        {
            return true;
        }

        $stmt->closeCursor();

        return $tmp;

    }

    public function executeScalar($statement, $params = null)
    {
        $stmt = $this->prepare($statement);

        if(!empty($this->bound_params) && empty($params))
        {
            $params = $this->bound_params;
        }

        try {
            try {
                if(!empty($params))
                {
                    $stmt->execute($params);
                }
                else 
                {
                        $stmt = $this->query($statement);
                }
            }
            catch(PDOException $pdo_error)
            {
                throw new error("Failed to execute query:\n" . $statement . "\nUsing Parameters:\n" . print_r($params, true) . "\nWith Error:\n" . $pdo_error->getMessage());
            }
        }
        catch(error $e)
        {
            $this->logDBError($e);
            $e->emailAdmin();
        }

        $count = $stmt->fetchColumn();

        $stmt->closeCursor();

        //echo $count;
        return $count;      
    }

    protected function logDBError($e)
    {
        $error = $e->getErrorReport();

        $sql = "
        INSERT INTO " . self::$error_table . " (message, time_date) 
        VALUES (:error, NOW())";

        $this->executeStatement($sql, array(':error' => $error));
    }
}

class QueryStatement extends PDOStatement 
{
    public $conn;

    protected function __construct() 
    {
        $this->conn = DB::connect();
        $this->setFetchMode(PDO::FETCH_ASSOC);
    }

    public function execute($bound_params = null)
    {
        return parent::execute($bound_params);          
    }
}