Calling stored procedure in codeigniter

Question

I am using latest codeigniter and trying to call stored procedure from my model. Also I am using mysqli as database driver. Now I am having an error when I call two stored procedures. Following is the error:

Error Number: 2014

Commands out of sync; you can't run this command now

call uspTest();

Filename: E:\wamp\www\reonomy-dev\system\database\DB_driver.php

Line Number: 330

Note that when I call a single stored procedure it works fine. Here is the code for model.

class Menus_model extends CI_Model {

function __construct()
{
    parent::__construct();

}

public function getMenus()
{
    $query = $this->db->query("call uspGetMenus()");

    return $query->result();
}

public function getSubMenus()
{
    $query = $this->db->query("call uspTest()");
    return $query->result();
}

}

Here is the code from controller

class MYHQ extends CI_Controller {

public function __construct()
{
    parent::__construct();
    $this->load->model('menus_model');
}

public function index()
{
    $menu = $this->menus_model->getMenus();
    $submenu = $this->menus_model->getSubMenus();
}

}

Is there any solution without hacking the core of codeigniter??

Answer 1

I follow the blog of Mr. Tim Brownlaw:
http://ellislab.com/forums/viewthread/73714/#562711

First, modify application/config/config.php, line 55.

$db['default']['dbdriver'] = 'mysqli'; // USE mysqli

Then, add the following into mysqli_result.php that is missing this command for some strange reason (under /system/database/drivers/mysqli/mysqli_result.php).

/**
  * Read the next result
  *
  * @return  null
  */   
 function next_result()
 {
     if (is_object($this->conn_id))
     {
         return mysqli_next_result($this->conn_id);
     }
 }

Then, in your model, add $result->next_result().

Below is my example.

function list_sample($str_where, $str_order, $str_limit)
{
   $qry_res    = $this->db->query("CALL rt_sample_list('{$str_where}', '{$str_order}', '{$str_limit}');");

   $res        = $qry_res->result();

   $qry_res->next_result(); // Dump the extra resultset.
   $qry_res->free_result(); // Does what it says.

   return $res;
}

Answer 2

change dbdriver to "mysqli" put this function to your model and use it to call stored procedure

function call( $procedure )
{
    $result = @$this->db->conn_id->query( $procedure );

    while ( $this->db->conn_id->next_result() )
    {
        //free each result.
        $not_used_result = $this->db->conn_id->use_result();

        if ( $not_used_result instanceof mysqli_result )
        {
            $not_used_result->free();
        }
    }

    return $result;

}

Answer 3

Having the same problem I found another approach which doesn't change the core, but instead uses a small helper.

Edit: The below linked asset is nowhere to be found.

See CoreyLoose post.

https://ellislab.com/forums/viewthread/71141/#663206

I had to make a small adjusment to his helper though. The line

if( get_class($result) == 'mysqli_stmt' )

could possibly produce a warning since the $result sometimes is passed as a boolean. I just put a check prior to this line and now it works perfectly, with no tinkering with the core!

Answer 4

This seems to be a bug in CodeIgniter. How come it's still in there is beyond me. However, there's a couple of ways to overcome it.

Check here: http://codeigniter.com/forums/viewthread/73714/ Basically, you modify mysqli_result.php to include next_result() function and make sure to call it after every stored proc. call. Just note that it assumes you're using mysqli as your DB driver... but you can probably do something similar with any other. You can change your driver in /application/config/database.php It's the line that says

$db['default']['dbdriver'] = 'mysql';

by default. Change it to:

$db['default']['dbdriver'] = 'mysqli';

You could also just close/reopen a DB connection between the calls, but I would definitely advise against that approach.