Laravel 5 Service that Wraps PDO to call StoredProcedure with Multiple ResultSets and Return Arrays of Data

PDOService.php

<?php

namespace App\Services;

class PDOService {

    private $connection;

    public function __construct($connection) {
        $this->connection = $connection;
    }

    /**
        @param $storedProcedureName string - Name of Stored Procedure that needs to be called
        @param $parameters array - Array of Values for Stored Procedure (They need to be in the correct order)
        @return Array of Resultsets

        Example:
        Use App\Services\PDOService;
        $pdoService = new PDOService('myconnection');
        $spParameters = [12345,'string'];
        $spName = 'sp_MyStoredProcedure';
        $spData = $pdoService->callStoredProcedure($spName,$spParameters);
    */
    public function callStoredProcedure($storedProcedureName,$parameters = array()) {

        if($this->_checkStoredProcedure($storedProcedureName) == 0) return ['error' => 'Stored Procedure does not exits'];       
                
        $pdo = \DB::connection($this->connection)->getPdo();

        $parametersString = '';
        $parameterCount = count($parameters);
        // Dynamic Paramter String
        if($parameterCount){           
            // Loop Parameters and add ? to parametersString
            for($i = 0;$i < $parameterCount; $i++) {
                $parametersString .= '?';
                if($i+1 < $parameterCount) $parametersString .= ',';
            }
        }

        $callString = "CALL $storedProcedureName($parametersString)";
        $statement=$pdo->prepare($callString);
        if($parameterCount) {
            $pIndex = 1;
            for($i = 0;$i < $parameterCount; $i++) {
                $paramValue = $parameters[$i];
                $statement->bindValue($pIndex,$paramValue,$this->_PDODataType($paramValue));
                $pIndex++;
            }
        }      
        $statement->execute();//$query->execute(array($bindings ));
        $pdoDataResults = array();
        do {
            $rowset = $statement->fetchAll(\PDO::FETCH_ASSOC);
            if ($rowset) {
                array_push($pdoDataResults,$rowset);
            }
        } while ($statement->nextRowset());

        return $pdoDataResults;
    }

    private function _checkStoredProcedure($procedureName) {

        $check = \DB::connection($this->connection)
                    ->table("information_schema.routines")
                    ->where("SPECIFIC_NAME","=",$procedureName)
                    ->select("SPECIFIC_NAME")
                    ->first();
        return count($check);
    }

    private function _PDODataType($value) {

        if( is_null($value) ) return \PDO::PARAM_NULL;

        if( is_bool($value) ) return \PDO::PARAM_BOOL;

        if( is_int($value) ) return \PDO::PARAM_INT;

        if( is_object($value) ) return \PDO::PARAM_LOB;

        return \PDO::PARAM_STR;
    }
}

Can I make a Composer package based on your code?

As long as I am given credit in there some how, sure.

…

On Tue, May 26, 2020, 6:01 AM Rodion Abdurakhimov ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ Can I make a Composer package based on your code? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <https://gist.github.com/2803f4d7ae7522f707bd090c03bd1c6b#gistcomment-3318302>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAER4WT2C6ZG3QO5WJOFTQDRTOHQ3ANCNFSM4NKFDVWQ> .

Done
https://github.com/rodion-arr/laravel-mysql-stored-procedures

Very nice.