EllisLab text mark
Advanced Search
     
Command out of sync
Posted: 09 March 2008 12:40 PM
Joined: 2008-03-04
13 posts

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.

 
Posted: 02 April 2008 04:57 PM   [ # 1 ]   [ Rating: 0 ]
Joined: 2007-09-27
1 posts

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")=={
            
@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”);

 
Posted: 16 June 2008 01:39 PM   [ # 2 ]   [ Rating: 0 ]
Joined: 2008-06-11
5 posts

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.

 
Posted: 13 April 2009 04:07 AM   [ # 3 ]   [ Rating: 0 ]
Joined: 2007-07-12
52 posts

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 smile

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?

 Signature 

Before you can find the answer, you need to create the problem!

—No Links—

 
Posted: 27 June 2009 10:52 PM   [ # 4 ]   [ Rating: 0 ]
Joined: 2009-06-27
3 posts

Thanks man! You helped me!

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

 
Posted: 08 August 2011 09:30 AM   [ # 5 ]   [ Rating: 0 ]
Avatar
Joined: 2011-02-23
47 posts

thank you so much this works for me , thanks a lot

 
Posted: 09 August 2011 12:40 AM   [ # 6 ]   [ Rating: 0 ]
Joined: 2010-12-23
254 posts

This is where MS SQL Server is better

 Signature 

Half the battle is won by following The User Guide. cool grin

 
Posted: 09 August 2011 05:58 AM   [ # 7 ]   [ Rating: 0 ]
Avatar
Joined: 2011-07-26
31 posts

thank you so very much this performs for me , many thanks a great offer

 Signature 

Welcome to my web site:http://www.dailygames365.com|www.dailyarcadegames1.com