5

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

noobie-php
  • 6,817
  • 15
  • 54
  • 101
  • Is that all your SQL? Are you running any other queries at the same time? I most often see this error when calling stored procedures or executing multi-queries without turning on multi-query mode. – kitti Mar 10 '12 at 15:58
  • yes very true i am running multiple quries but i only pasted the 1 that is actually displayed with error – noobie-php Mar 10 '12 at 16:02
  • Could you post the previous queries, and maybe the PHP that runs them? There isn't any problem with the query you posted; there's a problem with the state of your MySQL session when you try to execute it. – kitti Mar 10 '12 at 16:27
  • i think this issue arises when i try to hit the procedure in the loop. For single hit it is working fine, there are actually 2 scenarios here actually when i try to return some result from procedure i get the error. – noobie-php Mar 10 '12 at 17:14
  • the other scenario is that i don't return from anything procedure. – noobie-php Mar 10 '12 at 17:15
  • If you are running stored procedures, you should probably switch to the `mysqli` driver as it has better support for multi query, and make sure you free all result sets. The basic problem here is that you have unfreed result sets and you're issuing another query, and calls to stored procedures usually return multiple result sets. Please post more code so we can see what's going on. – kitti Mar 10 '12 at 17:44
  • ok i have edited the code above and i will try to apply more detail please look at it too. – noobie-php Mar 10 '12 at 17:48
  • @noobie-php, inside the stored procedure, you should free all resultsets returned from the procedure. You may read more on http://dev.mysql.com/doc/refman/5.0/en/commands-out-of-sync.html. – Abhay Mar 10 '12 at 18:11

1 Answers1

4

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.

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

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.

kitti
  • 14,663
  • 31
  • 49
  • I've been troubleshooting this issue the past 2 hours. There a lot of similiar issues, but most solutions did not with CI. This worked perfectly for me. THANK YOU Ryan! – user752746 Feb 12 '19 at 18:29