How to capture MySQL Error codes and Messages using Laravel 5.2?

Question

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!

Luís Cruz · Accepted Answer · 2016-03-25 00:05:27Z

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

how to use in controller, specially while running multiple query in loop?

Collectives™ on Stack Overflow

How to capture MySQL Error codes and Messages using Laravel 5.2?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related