Buffered Queries

Yii 1.1 General Discussion
1

I am using the app db connection to run a stored procedure against MySQL5.5:




                $cmd=yii::app()->db->createCommand('call JournalEntry(:_src_id, :_src_type,:_dst_id,:_dst_type,

                    :_src_amount, :_src_currency, :_dst_amount, :_dst_currency, :_description, :_category_id)');


                $cmd->bindParam(':_src_id',$model->src_account,PDO::PARAM_INT);

                $cmd->bindParam(':_src_type',$model->src_type,PDO::PARAM_INT);

                $cmd->bindParam(':_dst_id',$model->dst_account,PDO::PARAM_INT);

                $cmd->bindParam(':_dst_type',$model->dst_type,PDO::PARAM_INT);

                $cmd->bindParam(':_src_amount',$model->src_amount,PDO::PARAM_STR);

                $cmd->bindParam(':_src_currency',$model->src_currency,PDO::PARAM_INT);

                $cmd->bindParam(':_dst_amount',$model->dst_amount,PDO::PARAM_STR);

                $cmd->bindParam(':_dst_currency',$model->dst_currency,PDO::PARAM_INT);

                $cmd->bindParam(':_description',$model->jnl_description,PDO::PARAM_STR);

                $cmd->bindParam(':_category_id',$model->jnl_category,PDO::PARAM_STR);

                $result=$cmd->queryAll();

The app error reported is a missing table but what seems to be happening is that it queries the catalog after the procedure has run and that fails - I see the following in the log (attached to the catalog query):

General error: 2014 Cannot execute queries while other unbuffered queries are active.

My procedure call is failing but is closing off any transactions. I have tried various tricks to close the connnection and also tried


Yii::app()->db->pdoInstance->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

but that doesnt seem to work. Any help would be appreciated!

2

you could add :




Yii::app()->db->setActive(false);

Yii::app()->db->setActive(true);

after you call your stored procedure, and everything works again…

i found this,because i have the same problem a mounth ago…

you need to close your connection and then open it again…

hope this helps to other

3

Thank you - that works! Doesn’t strike me as particularly efficient. Perhaps we will get some comments from yii developers on this.

4

My guess would be that it has to do with MySQL only compiling and caching queries per DB connection. Once you need to reconnect, the stored procedure is recompiled.

Just an aside, but stored procedures are quite inefficient in MySQL for this reason.

5

yeah you’re right

i’m happy that i can help you