Error: Commands out of sync; you can't run this command now.

[hitech123]

Hello,

 

We have large queries and we have decided to use a stored procedure of MYSQL. The attached sql.php was made using mysql function of mysql, but for using stored procedure, i just changed all mysql function to mysqli. The code is working fine except that we are getting "Command out of sync." error. We get this error when first query to execute stored procedure follows another query to execute stored procedure or even simple sql query. We have made a research on the error and we understood that this error comes when the resultsets are not freed. But as i go through class we have freed mysqli results wherever necessary. We dont know what is causing the error, We had worked on the project for 6 months so we can't change everything but the sql.php. Any help will be much appreciated.

 

Regards

Анил Шарма

 

[attachment deleted by admin]

[fenway]

No one is going to look through 14kB of code -- what's the actual issue/

[fenway]

That's not any better (post removed) -- not only did you ignore the rules by not using code tags, but once again, you dumped tons of code without indicating the one query that's the problem.

[hitech123]

well, i guess you guys love tackling syntax error issues with less than 10 lines php codes only.......if you have looked at the code at least for 30 secs you'd have understood the problem. The problem is with "mysqli_fetch_object". If we go through an object oriented way the error shows in the 2nd query.

[fenway]

Then please post that particular query, and the relevant code snippet.

 

What we "love" is not having to read through code to find the problem -- we're here to help solve known problems.

[mikosiko]

well, i guess you guys love tackling syntax error issues with less than 10 lines php codes only.......if you have looked at the code at least for 30 secs you'd have understood the problem. The problem is with "mysqli_fetch_object". If we go through an object oriented way the error shows in the 2nd query.

 

try changing this portion in you code:

// Store Query Results

$num_rows=0;

while ( $row = @mysqli_fetch_object($this->result) )

{

// Store relults as an objects within main array

$this->last_result[$num_rows] = $row;

$num_rows++;

}

 

@mysqli_free_result($this->result);

 

to this one:

  do {

      while( $row = mysqli_fetch_object($this->result) ){

        $this->last_result[$num_rows] = $row;

        $num_rows++

      }

  }  while (mysqli_next_result($this->dbh));

 

mysqli_free_result() is not the solution... error is due to the fact that any stored procedure potentially return more that one resultset (one being just a execution status), therefore you MUST USE/DUMP all the resultsets before call/execute other SQL sentence, otherwise you will get the indicated error (out of sync)

[hitech123]

Thanks Mikosiko, u r right too.

Actually i solved this issue last night. I added following function in the class.

 

function ClearRecordsets(){
//mysqli_free_result($this->result);
while(mysqli_next_result($this->dbh)){
if($l_result = mysqli_store_result($this->dbh)){
mysqli_free_result($l_result);
}
}
}

 

Now it's working perfect.