Commands are not synchronized; you cannot run this command now when you call a stored procedure in Mysql

Question

I try to start a procedure, I get this error

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

here is the original error i get

Commands are not synchronized; you cannot run this command now

 SELECT DISTINCT `property_id`, `pin`, `block_id`, `serial_no`, `status`, `ex_sn`, `ex_code`, `property_date_time`, `street_add`, `lab_name` FROM `view_property_user_lab` WHERE status = '6' AND lab_id = '01' AND designation IN( '5','6') LIMIT 10 

can anyone tell me why i get this error and how to get rid of it. I use a code igniter and I also tried this

 $query->free_result(). 

I used this operator in my procedure

  SELECT * FROM temp_calculated_rates_and_rules; -- and then TRUNCATE temp_calculated_rates_and_rules; 

as this thing is being called in PHP Loop which looks like this

  $arrIds = array('5','10'); foreach ($arrIds as $id) { $this->_StoredProcedureMapper->setPId($id); $p10values = $this->_StoredProcedureMapper->fetch_p10_values(); if (intval(@$p10values[0]['is_exempted']) != 1) { $this->generate_p10($p10values); } } 

and here is the display function

  function fetch_p1_values() { $qry = "CALL sp_main_pt10(?)"; $result = $this->db->query($qry, $this->getPId()); return $result->result_array(); } 

And I use the mysqli driver

Answer 1

So, you need to deal with additional result sets generated by the stored procedure. The mysqli driver provides a method for this, but CodeIgniter cannot make this method available.

From https://ellislab.com/forums/viewthread/73714/#562711 :

I just added the following to mysqli_result.php, which this command is missing 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 my model I just call $ result-> next_result () to lose the expected extraneous result set.