how to get mysql stored procedure output param in yii framework

Question

I am using Yii to deal with a mysql stored procedure. The sp takes several parameter, one of which is a output parameter.

After execute the sp, when I try to get the output parameter, I run into a error

CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.. The SQL statement executed was: select @error_info as result;

My mock up code is like:

$sql = 'CALL p_bid(:username, @param)';
$command = Yii::app()->db->createCommand($sql);
$command->bindParam(":username", $username, PDO::PARAM_STR);

$command->execute();

// the following line raise the error
$errorInfo = Yii::app()->db->createCommand("select @error_info as result;")->queryScalar();

How can I walk around the question? Thanks.

The mock up code has a typing error, param should be error_info' — user1450627
– user1450627, Commented May 11, 2013 at 7:16
Might be worth checking out: stackoverflow.com/questions/9017861/… According to that post its a PHP error, and was fixed by updating to PHP 5.3.2. — Xethron
– Xethron, Commented May 11, 2013 at 7:25
Thanks for your clue, I will try your suggestion and come back later. — user1450627
– user1450627, Commented May 11, 2013 at 7:37
I have figured it out, it is my mistake not to use it correctly. I should add the following lines. $command->getPdoStatement()->fetchAll(); 261 $command->getPdoStatement()->closeCursor(); — user1450627
– user1450627, Commented May 11, 2013 at 14:08

deacs · Accepted Answer · 2015-10-12 12:50:06Z

2

Try this, It's working for me

$command = $connection->createCommand("CALL r emove_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();

edited Oct 12, 2015 at 12:50

deacs

4,3192 gold badges29 silver badges37 bronze badges

answered Dec 31, 2013 at 20:13

shivaP

2971 gold badge2 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to get mysql stored procedure output param in yii framework

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related