CODEWITHSUNDEEP
phpmysqlstored-procedureslaravel-5laravel-4
pallavi-Mindfire Solutions
pallavi-Mindfire Solutions

Reputation: 303

how to fetch multiple result set from a mysql stored procedure in laravel

I want to fetch multiple result sets from a stored procedure in laravel. Is there a way I can do this? Currently, I can get a single row's data using the below code:

$result = DB::statement('CALL user_login(' . $userId . ',"'
                                                              . $password . '",'
                                                              . '@success'.','
                                                              . '@first_Name'
                                                              );

$res = DB::select('select @success AS success, @first_Name AS firstName);

Here is my stored procedure:

DELIMITER //

DROP PROCEDURE IF EXISTS user_login//

create procedure user_login (IN userid VARCHAR(50),
                                   IN password VARCHAR(50),
                                   out success int,
                                   OUT first_Name VARCHAR(255),
                                   )

begin

declare count int(1);
set count =0;

select firstName, count(*)
into first_Name, count
from `tmc`.user where user_id = userid and pwd=password;

if count >0 then

set success =0;

else 
set success=1;

end if;

end//

Upvotes: 3

Views: 7311

Answers (2)

irfanengineer
irfanengineer

Reputation: 1300

If your stored procedure returns multiple outputs, In this case, you can handle this situation by two methods.

  1. Goto vendor\laravel\framework\src\Illuminate\Database\Connectors\Connector.php and set PDO::ATTR_EMULATE_PREPARES as true.

It will create problems when you will developing APIs, because of all APIs return numbers as a string when response return in JSON Like: {"status": "1"} but it should be {"status": 1}

  1. Add a new connection to a particular situation. You have to handle the above discussed problem for a particular API only not in all APIs. So I suggest choosing option 2nd.

Add connection in the config/database.php use below code into connections

'mysql_procedure' => [
'driver' => 'mysql',
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => 'sv_',
'prefix_indexes' => true,
'strict' => false,
'engine' => null,
'options' => extension_loaded('pdo_mysql') ? array_filter([
PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),PDO::ATTR_EMULATE_PREPARES => true
]) : [],
],

update details as per your configuration.

$procRslts = DB::connection('mysql_procedure')
->select("CALL user_login(?,?,?,?)", array( $userId ,$password,$success,$firstName ));

I think in this you don't need to pass the last two parameters, you can write logic int procedure to get from the database.

You can get multiple outputs returned by the stored procedure.

Upvotes: 1

Alex
Alex

Reputation: 810

I am using the following code and it works flawlessly. Change it to suit your needs.

public static function CallRaw($procName, $parameters = null, $isExecute = false)
{
    $syntax = '';
    for ($i = 0; $i < count($parameters); $i++) {
        $syntax .= (!empty($syntax) ? ',' : '') . '?';
    }
    $syntax = 'CALL ' . $procName . '(' . $syntax . ');';

    $pdo = DB::connection()->getPdo();
    $pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, true);
    $stmt = $pdo->prepare($syntax,[\PDO::ATTR_CURSOR=>\PDO::CURSOR_SCROLL]);
    for ($i = 0; $i < count($parameters); $i++) {
        $stmt->bindValue((1 + $i), $parameters[$i]);
    }
    $exec = $stmt->execute();
    if (!$exec) return $pdo->errorInfo();
    if ($isExecute) return $exec;

    $results = [];
    do {
        try {
            $results[] = $stmt->fetchAll(\PDO::FETCH_OBJ);
        } catch (\Exception $ex) {

        }
    } while ($stmt->nextRowset());


    if (1 === count($results)) return $results[0];
    return $results;
}

Example call:

$params = ['2014-01-01','2014-12-31',100];
$results = APIDB::CallRaw('spGetData',$params);

The resulting call will be:

CALL spGetData(?,?,?)

If there is only one resultset, it will be returned as is. If there are more, it will return an array of result sets. The key is using $pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, true);. Without it, a horrible SQLSTATE[HY000]: General error: 2053 exception will be thrown.

The try{} catch() block is used to eliminate the resultsets that cannot be fetched. Particularly, I have procedures that returns two resultsets, one as a result of an update (or other execute statements) and the last one as the real data. The exception thrown on fetchAll() with an execute query will be PDOException.

Warning: the function is not optimised. You can rewrite it with one single pass through the parameters.

Upvotes: 7

Related Questions