Command out of sync

[eluser]stathis[/eluser]
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:

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.

[eluser]Unknown[/eluser]
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...

Code:

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");

[eluser]Yauhen_S[/eluser]
Hi! I was solve this problem in other way. Below is a _execute method code:

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.

[eluser]Tim Brownlaw[/eluser]
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)

Code:

// --------------------------------------------------------------------
  /**
   * 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.

Code:

// 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?

[eluser][email protected][/eluser]
Thanks man! You helped me!

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

[eluser]Rahul gamit[/eluser]
thank you so much this works for me , thanks a lot

[eluser]CodeIgniteMe[/eluser]
This is where MS SQL Server is better

[eluser]nikes[/eluser]
thank you so very much this performs for me , many thanks a great offer

[eluser]charly87[/eluser]
Hi. Thanks for the solution. It started working fine for me but now i'm getting this error:

<p>Severity: Warning</p>
<p>Message: Illegal string offset 'username'</p>
<p>Filename: my_model.php</p>
<p>Line Number: 96</p>


What is the problem?

This is my model function:

PHP Warning, illegal offset 'username'

I'm doing an ajax request and the error is in $params =array( $db_data['username'] );


// 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 validate_UserExists(?)';
$params =array(
$db_data['username'] );
$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.
}


Hope you can help me.

Thanks

[eluser]CroNiX[/eluser]
It looks like your $db_data array that you are passing to your add_reg_member() method does not contain a 'username' index. The problem most likely isn't in the code you posted, but wherever you are calling that method from.