mysql 不同步存储过程,命令不同步;您在Mysql中调用存储过程时不能运行此命令

I am trying to run a procedure i am getting this error

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

here is the original error that i get

Commands out of sync; you can't 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 any 1 tell me why i am getting this error and how to get rid of it . I am using Code igniter and i also tried this

$query->free_result().

within my procedure i have used this statement

SELECT *

FROM

temp_calculated_rates_and_rules;

-- and then

TRUNCATE temp_calculated_rates_and_rules;

as this thing is called in PHP Loop which is 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 mapper function

function fetch_p1_values()

{

$qry = "CALL sp_main_pt10(?)";

$result = $this->db->query($qry, $this->getPId());

return $result->result_array();

}

And i am using "mysqli" driver

解决方案

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

I simply added 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 my model, I simply call $result->next_result() to loose the

expected extraneous resultset.