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

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.