Example of using Mysql stored procedure in Codeigniter framework

Execute the stored procedure

$ Query = $ this-> db-> query ('Call YOU_SP_NAME ');
$ Result = $ query-> result ();

This is not a big problem, just like executing a common SQL statement.

Problems encountered when using stored procedures

After the stored procedure is used, it is found that the database link cannot use the persistent link. set $ db ['pconnect '] in php to FALSE. Otherwise, the connection to the database fails.

In addition, after the stored procedure is executed, if other database queries are executed, the Commands out of sync; you can't run this command now error will occur.

The reason was found. It is said that the result set was not released after the stored procedure was executed.

CI can be solved by reconnecting to the database:

$ This-> db-> reconnect ();
How to obtain multiple result sets

It is mainly obtained using Mysqli multi_query ().

The key code is as follows:

$ Mysqli = new mysqli ('localhost', 'username', 'password', 'dbname ');
$ Mysqli-> query ("set names utf8 ");
/* Check connection */
If (mysqli_connect_errno ()){
Printf ('connect failed: % s \ n', mysqli_connect_error ());
Exit ();
}
$ Query = 'Call YOU_SP_NAME ';
/* Execute multi query */
If ($ mysqli-> multi_query ($ query )){
Do {
/* Store first result set */
If ($ result = $ mysqli-> store_result ()){
While ($ row = $ result-> fetch_all ()){
$ All_result [] = $ row;
                        }
$ Result-> free ();
                }
        }
While ($ mysqli-> next_result ());
}
/* Close connection */
$ Mysqli-> close ();