如何使用Laravel 5.2捕获MySQL错误代码和消息? [英] How to capture MySQL Error codes and Messages using Laravel 5.2?

问题描述

我在使用Laravel 5.2通过select方法通过DB Facade捕获和检索MySQL错误代码和消息时遇到了麻烦.

我想做的是选择/调用一个MySQL函数，该函数在带有参数的表中插入一个简单的行.但是，如果对于不能为null的列，参数为空，该怎么办? MySQL引发错误(Error Code: 1048).

问题是在这些情况下，我在Laravel中没有收到任何错误/异常，而且我不知道插入是否成功或无法以正确的方式处理错误. /p>

这是我的代码:

    DB::beginTransaction();
    try{            
        $statement = "select mySQLFunction('".$param."')";            
        DB::select($statement);                            
        DB::commit();            
    }catch(\Exception $e){            
        DB::rollback();
        Log::error('Exception: '.$e->getMessage());
    }   

我想知道是否有一种方法可以捕获这种错误代码和消息，以便Laravel应用程序可以知道MySQL数据库中存在错误.如果成功，还可以获取MySQL函数的返回值.

提前谢谢！

解决方案

Laravel使用PDO，因此您可以使用 errorInfo 变量，它返回SQLSTATE错误和消息.基本上，您需要使用$e->errorInfo;

如果要将所有SQL错误记录到数据库中，可以使用Exception Handler(app/Exceptions/Handler.php并监听QueryExceptions.类似这样的东西:

public function render($request, Exception $e)
{
    switch ($e) {
        case ($e instanceof \Illuminate\Database\QueryException):
            LogTracker::saveSqlError($e);
            break;
        default:
            LogTracker::saveError($e, $e->getCode());
    }

    return parent::render($request, $e);
}

然后您可以使用类似这样的内容:

public function saveSqlError($exception)
{
    $sql = $exception->getSql();
    $bindings = $exception->getBindings()

    // Process the query's SQL and parameters and create the exact query
    foreach ($bindings as $i => $binding) {
        if ($binding instanceof \DateTime) {
            $bindings[$i] = $binding->format('\'Y-m-d H:i:s\'');
        } else {
            if (is_string($binding)) {
                $bindings[$i] = "'$binding'";
            }
        }
    }
    $query = str_replace(array('%', '?'), array('%%', '%s'), $sql);
    $query = vsprintf($query, $bindings);

    // Here's the part you need
    $errorInfo = $exception->errorInfo;

    $data = [
        'sql'        => $query,
        'message'    => isset($errorInfo[2]) ? $errorInfo[2] : '',
        'sql_state'  => $errorInfo[0],
        'error_code' => $errorInfo[1]
    ];

    // Now store the error into database, if you want..
    // ....
}

I'm getting into troubles to capture and retrieve the MySQL Error codes and messages using Laravel 5.2 through the DB Facade with the select method.

What I want to do is to select/call a MySQL function that inserts a simple row in a table with a parameter. However what if the parameter in empty for a column that can't be null? MySQL throws an error (Error Code: 1048).

The problem is that I'm not getting any error/exception in Laravel in those situations and I can't know if the insert succeeded or not to handle the error in the proper way.

Here is my code:

    DB::beginTransaction();
    try{            
        $statement = "select mySQLFunction('".$param."')";            
        DB::select($statement);                            
        DB::commit();            
    }catch(\Exception $e){            
        DB::rollback();
        Log::error('Exception: '.$e->getMessage());
    }   

I wonder if there is a way to capture this kind of error codes and messages so Laravel application can know there was an error in MySQL database. Also to get the return value of the MySQL function in case of success.

Thanks in advance!

解决方案

Laravel uses PDO, so you can use the errorInfo variable which returns the SQLSTATE error and the message. Basically, you need to use $e->errorInfo;

If you want to log all SQL errors into database, you can use the Exception Handler (app/Exceptions/Handler.php and listen for QueryExceptions. Something like this:

public function render($request, Exception $e)
{
    switch ($e) {
        case ($e instanceof \Illuminate\Database\QueryException):
            LogTracker::saveSqlError($e);
            break;
        default:
            LogTracker::saveError($e, $e->getCode());
    }

    return parent::render($request, $e);
}

Then you can use something like this:

public function saveSqlError($exception)
{
    $sql = $exception->getSql();
    $bindings = $exception->getBindings()

    // Process the query's SQL and parameters and create the exact query
    foreach ($bindings as $i => $binding) {
        if ($binding instanceof \DateTime) {
            $bindings[$i] = $binding->format('\'Y-m-d H:i:s\'');
        } else {
            if (is_string($binding)) {
                $bindings[$i] = "'$binding'";
            }
        }
    }
    $query = str_replace(array('%', '?'), array('%%', '%s'), $sql);
    $query = vsprintf($query, $bindings);

    // Here's the part you need
    $errorInfo = $exception->errorInfo;

    $data = [
        'sql'        => $query,
        'message'    => isset($errorInfo[2]) ? $errorInfo[2] : '',
        'sql_state'  => $errorInfo[0],
        'error_code' => $errorInfo[1]
    ];

    // Now store the error into database, if you want..
    // ....
}

这篇关于如何使用Laravel 5.2捕获MySQL错误代码和消息?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！