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)
$result = @mysqli_store_result($this->conn_id);        
            if (@
mysqli_more_results($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)


// This handles stored procedures....
if  (stristr($sql,"call") && stripos($sql,"call")=={
$result = @mysqli_store_result($this->conn_id);        
            if (@
mysqli_more_results($this->conn_id)) {
else {
= @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
$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)) {
= @mysqli_store_result($this->conn_id);

// 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: 1 ]
Joined: 2007-07-12
119 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(
$result $this->db->query($sql,$params);

$result) && ($result->num_rows() > 0))

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


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 ]
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


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

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

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


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

Posted: 05 June 2014 07:04 AM   [ # 8 ]   [ Rating: 0 ]
Joined: 2012-06-21
2 posts

Hi. Thanks for the solution. It started working fine for me but now i’m getting this error:

Severity: Warning
Message:  Illegal string offset ‘username’
Filename: my_model.php
Line Number: 96

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();
  $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.


Posted: 05 June 2014 03:07 PM   [ # 9 ]   [ Rating: 1 ]
Joined: 2009-02-19
4534 posts

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.

Posted: 05 June 2014 06:03 PM   [ # 10 ]   [ Rating: 0 ]
Joined: 2012-06-21
2 posts

You’re right my friend. I solved my problem taking a look to your sugestion.

Thank you smile