[Laravel] Stored procedure in Debugbar

readme.md

I needed to call a routine with Laravel but I encountered the MySQL "Packets out of order" error. I've found an answer on this StackOverflow question : $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

I have created a trait that can call a stored procedure and log it in Debugbar :

<?php

namespace App\Models\Traits;

use Illuminate\Support\Facades\DB;
use PDO;

trait RoutineTrait
{
	
    /**
     * Call a routine.
     * This trait resolves the "Packets out of order" error by re-setting PDO attribute
     * @link https://stackoverflow.com/questions/19389809/packets-out-of-order-error-when-calling-mysql-stored-proc
     *
     * @param $name
     * @param array $params
     * @return \Illuminate\Support\Collection
     */
    public static function routine($name, $params = [])
    {
        $start = microtime(true);

        // Create the query
        $query_params = implode(', ', array_fill(0, count($params), '?'));
        $query = "call `{$name}`({$query_params})";

        // Retrieve connection and PDO object
        $db_connection = DB::connection();
        $db = $db_connection->getPdo();

        // Retrieve previous PDO configuration
        $previous_ATTR_EMULATE_PREPARES = $db->getAttribute(PDO::ATTR_EMULATE_PREPARES);

        // Update attribute
        $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

        // Prepare the query
        $stmt = $db->prepare($query);

        // Bind parameters
        $i = 0;
        foreach($params as $p) {
            $stmt->bindValue(++$i, $p, PDO::PARAM_STR);
        }
        $stmt->execute();

        // Retrieve the result
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Reset PDO attribute to previous configuration
        $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, $previous_ATTR_EMULATE_PREPARES);

        // Log in the Debugbar
        if(function_exists('debugbar')) {
            debugbar()['queries']->addQuery($query, $params, microtime(true) - $start, $db_connection);
        }

        // Return collection
        return collect($result);
    }
}

You can use it in your Eloquent model :

<?php
use App\Models\Traits\RoutineTrait;
use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    use RoutineTrait;
    
    public function myMethod()
    {
        return static::routine('My_StoredProcedure', ['param1', 'param2']);
    }
}

And you will see the query in your debugbar :