how to use result sets from stored procedure in codeigniter

Question

how to perform multiple queries in single stored procedure and then use result we get by all queries in our model. I get Query error: Commands out of sync; you can't run this command now error when try to use the result of the stored procedure. Here is my code :

**In Model: **

    /* convert post array data to string */
    $pr = "'" .implode("', '", $data) . "'";
    $pr2 = "'" .implode("', '", $data2) . "'";
    $pr5 = "'" .implode("', '", $data4) . "'";

    /* call stored procedure */
    $this->db->query('CALL addCustomerSalesData("'.$pr.'", "'.$pr2.'", "'.$pr5.'", @CustSalesID, @CustSalesProID)');

    /* get the stored procedure returned output */
    $query = $this->db->query('SELECT @CustSalesID AS cust_sales_id, @CustSalesProID AS cust_sales_pro_id');
    $row = $query->row_array();

    /* this will release memory which is used by stored procedure and make it free for another process */
    $query->next_result();
    $query->free_result();

    /* return inserted id*/
    return $row;

score 0 · Answer 1 · answered Jan 16 '18 at 13:01

0

You're running two querys in one. So mysql consume more memory to hold them

$this->db->query('CALL addCustomerSalesData("'.$pr.'", "'.$pr2.'", "'.$pr5.'", @CustSalesID, @CustSalesProID)');

$query = $this->db->query('SELECT @CustSalesID AS cust_sales_id, @CustSalesProID AS cust_sales_pro_id');

What can you do

Store First and execute second
or $query->free_result(); in between two query to free up those

answered Jan 16 '18 at 13:01

Abdulla Nilam

36,589
17
64
85

hey thanks @DeadManAlive bt i want to run all queries simultaneously, can you please tell me how can i use $query->free_result(); – Vishwa Dave Jan 16 '18 at 13:18
as you say **i want to run all queries simultaneously**, save first` $result = $this->db->query('CALL ....` and run second one. Read [Codeigniter Transactions](https://stackoverflow.com/questions/15224826/codeigniter-transactions) – Abdulla Nilam Jan 16 '18 at 13:20

score 0 · Answer 2 · answered Jan 22 '18 at 07:23

when you want to use more than one select in stored procedure, to avoid Commands out of sync; you can't run this command now error what you need to do is:

find this file : System/database/drivers/mysqli/mysqli_driver.php

in this file find function : function _execute($sql) { }

change this to:

protected function _execute($sql)
{
    // 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;
}

how to use result sets from stored procedure in codeigniter

2 Answers2