CODEWITHSUNDEEP
phpmysqlstored-procedures
bio_sprite
bio_sprite

Reputation: 448

mysql stored procedure error Commands out of sync; you can't run this command now

I have a stored procedure which I call from PHP which works fine on my dev version which is running Maria DB. I transferred to production which is running mysql (5.5.53) a 'Commands out of sync; you can't run this command now' error. I have tried suggestions to make sure the connection is closed after each query. I have also tried $stmt->store_result(); & $stmt->free_result();. If I try $stmt-next_result(); I do not get this out of sync error, but my script exists the loop and I do not get any other results.

Stored Procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `test3`(IN `Input_Value` INT, OUT `Out_val` VARCHAR(150))
BEGIN
    SET @sql = 'SELECT sample_name FROM sample';
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
END

PHP: ` 

foreach($locations as $location_name){
    echo $location_name.'<br>';
    $sp_query = "CALL test3(?,@Out_val)";
    $stmt = $dbc->prepare($sp_query);
    if(!$stmt){
        echo mysqli_error($dbc);
    }
    $stmt->bind_param('i',$view);

    if ($stmt->execute()){
        $params = array();
        $meta = $stmt->result_metadata();
        print_r($meta); 

        while ($field = $meta->fetch_field()){
            $params[] = &$row[$field->name]; 
        }
        print_r($params); 
        call_user_func_array(array($stmt, 'bind_result'), $params); 
        $header_ct = 0; 

        $labels = '';
        while ($stmt->fetch()) {
            foreach($row as $key => $value){
                $key = htmlspecialchars($key);
                //echo "Key:".$key."<br>";
                //echo "Value:".$value."<br>";
            }
        }
    }else{
        echo mysqli_error($dbc);
        echo "not working";
    }
    $stmt->store_result();
    //$stmt->free_result();
    $stmt->close();                 
}           

?>`

Upvotes: 1

Views: 1446

Answers (1)

Akhil J
Akhil J

Reputation: 69

In Just seperate your queries with $select_stmt->close(); to split them (not simultaneous but procedural. For more on this see

If you are using codeigniter Framework, add following code into /system/database/drivers/mysqli/mysqli_result.php For codeigniter

function next_result()
 {
     if (is_object($this->conn_id))
     {
         return mysqli_next_result($this->conn_id);
     }
}

then in model when you call Stored Procedure

$query    = $this->db->query("CALL test()");
$res      = $query->result();

//add this two line 
$query->next_result(); 
$query->free_result(); 
//end of new code

return $res;

Upvotes: 2

Related Questions