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??
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;
}