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

Question

This is my code below:

public function listParkedAccounts($days = NULL)
{
    global $db;
    $days_db = (empty($days))?"7":$days;
    $stmt    = "CALL parked_accounts('.$days_db.')";

    $result       = $db->query($stmt,true,'Failed to fetch aged author record');
    $aged_authors = mysqli_fetch_all($result,MYSQLI_ASSOC);
    //mysql_free_result($result); tried this

    $counter = 0;
    $data    = null;

    foreach($aged_authors as $authors){
        if(empty($authors['call_descp'])){
            $stmt_notes   = "SELECT description FROM notes WHERE description IS NOT NULL AND parent_id = '".$authors['call_id']."' AND parent_type = 'Calls' ORDER BY date_entered DESC LIMIT 1";
            $notes_descp = $db->getOne($stmt_notes, TRUE, 'Error retrieving call notes');


            $notes = (!empty($notes_descp))?$notes_descp[0]['description']:"No Notes";

        }else{
           $notes = (!empty($authors['call_descp']))?$authors['call_descp']:"No Notes";
        }
        $lead = 'stuff';
        $data[$counter]['lead_id']    = $lead;
        $data[$counter]['call_notes'] = $notes;
        $counter++;
    }

    $size = sizeof($data);
    $json_vals['draw'] = "1";
    $json_vals['recordsTotal'] = $size;
    $json_vals['recordsFiltered'] = $size;
    $json_vals['data'] = ($size > 0)?$data:array();
    return $json_vals;
}

My problem here is this error message:

Error retrieving call notes Query Failed: SELECT description FROM notes WHERE description IS NOT NULL AND parent_id = '123' AND parent_type = 'Calls' ORDER BY date_entered DESC LIMIT 1: MySQL error 2014: Commands out of sync; you can't run this command now

What I understood from reading this is that I need to free the results or store them but when I tried those I still got the same error message. Though I am not quite sure where I should be freeing the results or if I am even doing them correctly. This is my first time using the stored procedure.

score 1 · Answer 1 · edited Dec 15 '17 at 13:14

1

When you call a stored procedure, there is a possibility that the stored procedure contains multiple result sets that you need to process. If you only process the first result set and go ahead and try to execute another query with the same connection, you will get the

"Commands out of sync"

error.

To fix the issue, instead of the $db->query + mysqli_fetch_all (btw try not to mix object oriented style and procedural style) you should use multi_query.

An example how to handle multiple result sets with rows is shown in the PHP documentation: http://php.net/manual/en/mysqli.multi-query.php

edited Dec 15 '17 at 13:14

Star

3,222
5
32
48

answered Oct 03 '17 at 10:09

slaakso

8,331
2
16
27

I get this error `MysqliManager::multi_query()` might be a limitation of the current version of SugarCRM we're using. – hungrykoala Oct 03 '17 at 11:32
`Call to undefined method MysqliManager::multi_query() ` – hungrykoala Oct 03 '17 at 12:04
What if you try to use the database connection directly instead ($db->database) as a first parameter to mysqli_multi_query? – slaakso Oct 03 '17 at 12:21
I would like to try to avoid doing that. Is there any way for me to change my query or flow instead? – hungrykoala Oct 03 '17 at 13:05
You could put mysqli_next_result($db->getDatabase()); where you had the mysql_free_result-call. This should clear the buffer assuming your procedure returns only one result set. – slaakso Oct 03 '17 at 15:27

score 0 · Answer 2 · answered Oct 05 '17 at 07:21

I have changed few lines as following:

$notes_descpRes = $db->query($stmt_notes);
$notes_descp  = $db->fetchByAssoc($notes_descpRes); 
$notes = (!empty($notes_descp))?$notes_descp['description']:"No Notes";

Following is the complete code:

public function listParkedAccounts($days = NULL)
{
    global $db;
    $days_db = (empty($days))?"7":$days;
    $stmt    = "CALL parked_accounts('.$days_db.')";

    $result       = $db->query($stmt,true,'Failed to fetch aged author record');
    $aged_authors = mysqli_fetch_all($result,MYSQLI_ASSOC);
    //mysql_free_result($result); tried this

    $counter = 0;
    $data    = null;

    foreach($aged_authors as $authors){
        if(empty($authors['call_descp'])){
            $stmt_notes   = "SELECT description FROM notes WHERE description IS NOT NULL AND parent_id = '".$authors['call_id']."' AND parent_type = 'Calls' ORDER BY date_entered DESC LIMIT 1";

            $notes_descpRes = $db->query($stmt_notes);
            $notes_descp  = $db->fetchByAssoc($notes_descpRes); 

            $notes = (!empty($notes_descp))?$notes_descp['description']:"No Notes";

        }else{
           $notes = (!empty($authors['call_descp']))?$authors['call_descp']:"No Notes";
        }
        $lead = 'stuff';
        $data[$counter]['lead_id']    = $lead;
        $data[$counter]['call_notes'] = $notes;
        $counter++;
    }

    $size = sizeof($data);
    $json_vals['draw'] = "1";
    $json_vals['recordsTotal'] = $size;
    $json_vals['recordsFiltered'] = $size;
    $json_vals['data'] = ($size > 0)?$data:array();
    return $json_vals;
}

Give it a try.

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

2 Answers2