Stored Procedure Out Parameter

Yii 1.1 General Discussion
1

Dear All,

Could some one advise me how to deal with OUT parameters in Yii?


$connection = Yii::app()->db;

$command = $connection->createCommand("CALL remove_cars($user_id,$car_id,$car_type,$outParm)"); 

$command->execute();

$outParm is the one that I want to get back from stored procedure .

The approach I tried is not working .

Thanks for your help

Regards

Yii Fan

2

read binding parameters




$sql="INSERT INTO tbl_user (username, email) VALUES(:username,:email)";

$command=$connection->createCommand($sql);

// replace the placeholder ":username" with the actual username value

$command->bindParam(":username",$username,PDO::PARAM_STR);

// replace the placeholder ":email" with the actual email value

$command->bindParam(":email",$email,PDO::PARAM_STR);

$command->execute();

// insert another row with a new set of parameters

$command->bindParam(":username",$username2,PDO::PARAM_STR);

$command->bindParam(":email",$email2,PDO::PARAM_STR);

$command->execute();

binding with bindParam function is bidirectional (by reference), so you should get return value in one of such defined params. If you want to bind only value (by value) - use bindValue function instead.

3

Thank You redguy .

Unfortunately that approach is not working .

But found a way and here it is


        $command = $connection->createCommand("CALL remove_places(:user_id,:placeID,:place_type,@out)"); 

          $command->bindParam(":user_id",$user_id,PDO::PARAM_INT);

          $command->bindParam(":placeID",$placeID,PDO::PARAM_INT);

          $command->bindParam(":place_type",$place_type,PDO::PARAM_INT);

          $command->execute();

          $valueOut = $connection->createCommand("select @out as result;")->queryScalar();

Regards

Yii Fan

4

according to PDO this could also work:




...

$command->bindParam(':out', $valueOut, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, '');

$command->execute();

have you tried to define param as INPUT_OUPUT?

5

Thank You again . I tried this approach but this also didn’t help . May be I am doing some thing . As you said It should work . I tried even changing SP parameter to INOUT but still no luck . I will see if I can get any exceptions for this and try to debug it .

Thanks again

6

Hey there,

I hope this may help you…





$db = Yii::$app->db;

$command = $db->createCommand('CALL DATABASE_NAME.PKG_NAME.PROCEDURE_NAME(:code,@out_rv)');

$command->bindParam(":code_o", $code_o, PDO::PARAM_STR);

$command->execute();


$RV = $db->createCommand("select @out_rv as rv;")->queryScalar();

Happy Coding…

Cheers!