Command out of sync

Posted: 09 March 2008 10:40 AM

[ Ignore ]

Summer Student

Total Posts: 3

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: 3

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

‹‹ 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 719, on June 06, 2008 10:16 AM
Total Registered Members:	60338	Total Logged-in Users:	18
Total Topics:	72442	Total Anonymous Users:	1
Total Replies:	390613	Total Guests:	358
Total Posts:	463055