Command out of sync

Posted: 09 March 2008 10:40 AM

[ Ignore ]

Summer Student

Total Posts: 13

Joined 03-04-2008

It seems that when you try to call at 2 stored procedures at the same routine,
you get an error like this: “Command out of sync”

This error is caused due to the fact that mySQL Stored Procedures return MULTI RESULTS
even if there is only one select statement in them.

In PHP, you over come this by using mysqli_multi_query.
In CI, i re-writed funcion _execute, located in system/database/drivers/mysqli/mysqli_driver.php using the following code:

function _execute($sql)
        {
            $sql = $this->_prep_query($sql);    
            @mysqli_multi_query($this->conn_id, $sql);
            $result = @mysqli_store_result($this->conn_id);        
            if (@mysqli_more_results($this->conn_id)) {
                @mysqli_next_result($this->conn_id);            
            }
            return $result;
    } 

Is it possible to find a permanent solution on this?
Please note that the above, leaves out the possibility for one to actually need to have multiple results in one procedure call.

Profile

bwlinux

Posted: 02 April 2008 02:57 PM

[ Ignore ] [ # 1 ]

Summer Student

Total Posts: 4

Joined 09-27-2007

When I tried to modify mysqli_driver.php as you showed none of my other regular SQL queries would work. So this is what I did…

function _execute($sql)
    {

        $sql = $this->_prep_query($sql);

        // This handles stored procedures....
        if  (stristr($sql,"call") && stripos($sql,"call")==0 ) {
            @mysqli_multi_query($this->conn_id, $sql);
            $result = @mysqli_store_result($this->conn_id);        
            if (@mysqli_more_results($this->conn_id)) {
                @mysqli_next_result($this->conn_id);            
            }
        } else {
            $result = @mysqli_query($this->conn_id, $sql);
        }
        
        return $result;
    
    } 

I guess if you stuck to always using 1 case of the call command you could avoid the hit of the case insensitive string functions.

I really wish I could find a more elegant solution to this and still use syntax like $this->db->query(“call someStoredProcedure”);

Profile

Yauhen_S

Posted: 16 June 2008 11:39 AM

[ Ignore ] [ # 2 ]

Summer Student

Total Posts: 5

Joined 06-11-2008

Hi! I was solve this problem in other way. Below is a _execute method code:

// Free result from previous query
   @mysqli_free_result($this->result_id);
        
   $sql = $this->_prep_query($sql);

   // get a result code of query (), can be used for test is the query ok
   $retval = @mysqli_multi_query($this->conn_id, $sql); 

   // get a first resultset
   $firstResult = @mysqli_store_result($this->conn_id);

   // free other resultsets
   while (@mysqli_next_result($this->conn_id)) {
      $result = @mysqli_store_result($this->conn_id);
      @mysqli_free_result($result);
   }

   // test is the error occur or not 
   if (!$firstResult && !@mysqli_errno($this->conn_id)) {
       return true;
   }
   return $firstResult; 

About check for error in last lines. This is because mysqli_store_result can return false not only if error occur, but also if query don’t return resultset. Unfortunately CI checks only returned value for false and in this case “Error Number: 0” occures.

PS: I’m not sure that this will works with regular query, i’m using only stored routines in my current project.

Profile

Tim Brownlaw

Posted: 13 April 2009 02:07 AM

[ Ignore ] [ # 3 ]

Summer Student

Total Posts: 10

Joined 07-12-2007

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;

As an example, I have a stored procedure for adding a new member which I call in a model as the following function call.

// Calling  Stored procedure add_reg_member
// Returns an array in the form...
//      $row['result'] ( 0 = success )
//      $row['message'] (if there is an error - why)
//      $row['last_id'] - last insert id (could test this instead of result, but let's stay consistent) 
  function add_reg_member($db_data)
  {
// using the active record structure to test it
    $sql = 'CALL member_register(?,?,?,?,?,?,?,?,?)';
    $params =array(
      $db_data['username'],
      $db_data['firstname'],
      $db_data['lastname'],
      $db_data['email'],
      $db_data['password'],
      $db_data['country'],
      $db_data['validation_code'],
      $db_data['date_reg'],
      $db_data['valid'] );
   $result = $this->db->query($sql,$params);

  if(($result) && ($result->num_rows() > 0))
  {
     $row = $result->row_array();
  } 
  else 
    $row =array('result'=>1,'message'=>'Something went horribly wrong with the DB','last_id'=>0); // Database Problem - die gracefully.

  $result->next_result(); // Dump the extra resultset.
  $result->free_result(); // Does what it says.
  return $row; // Return the row to the controller that called it.
  } 

My stored procedure, while performing an insert, returns a few values I want to test and use.
For instance, when a duplicate username is encountered and the last inserted id.

So adding in the actual mysqli_next_result into mysqli_result.php fixed up all my problems. Well my CI ones

Plus I can still use the active record stuff…
I’m not sure why this isn’t in CI already. I checked version 1.7.1 and it’s not there.

Hope that helps someone!

Now, what was I doing a few hours ago before I got stuck on this?

Profile

nmneiftyqmwmo@mailinator.com

Posted: 27 June 2009 08:52 PM

[ Ignore ] [ # 4 ]

Summer Student

Total Posts: 3

Joined 06-27-2009

Thanks man! You helped me!

I really like CI but mulple resultsets support is missing! OMFG!

Profile

‹‹ Lost session DB.php bug ››

Username		Remember Me?
Password		forgot password?

Post Marker Legend
	New posts	Hot Topic with new posts	New Poll	Moved Topic	Sticky topic
	No new posts	Hot Topic with no new posts	Old Poll	Closed Topic	Announcements

Visitor Statistics
The most visitors ever was 819, on March 11, 2010 11:15 AM
Total Registered Members:	119813	Total Logged-in Users:	41
Total Topics:	125952	Total Anonymous Users:	3
Total Replies:	662645	Total Guests:	422
Total Posts:	788597