stored procedure with multiple resultset in Codeigniter

Question

I have been working on AMFPHP + codeigniter + flash and everything was working just fine but, when I created a stored procedure the problem started. I was able to call the stored procedure with multiple result sets from the AMF browser but whenever the function was called from the flash itself it raises the Bad Version error.

Below is the library {from CI forum} to traverse multiple result sets am using

class Mydb
{
   private $CI, $Data, $mysqli, $ResultSet;

   /**
   * The constructor
   */

   function __construct()
   {
     $this->CI =& get_instance();
     $this->Data = '';
     $this->ResultSet = array();
     $this->mysqli = $this->CI->db->conn_id;
   }

    public function GetMultiResults($SqlCommand)
    {
    /* execute multi query */
    if (mysqli_multi_query($this->mysqli, $SqlCommand)) {
        $i=0;
        do
        {

             if ($result = $this->mysqli->store_result()) 
             {
                while ($row = $result->fetch_assoc())
                {
                    $this->Data[$i][] = $row;
                }
                mysqli_free_result($result);
             }
            $i++; 
        }
        while ($this->mysqli->next_result());
    }
    return $this->Data;

   }   
}

and calling it like this:

$this->load->library('mydb');
$this->mydb->GetMultiResults("CALL test()");

I have noticed that the library loading line raises the Bad Version error at flash end as if I comment out this line it works {works like no error is ther but SP doesn't execute}

Any Idea on how to fix this strange issue.

what version of CodeIgniter? I'm using 3.1.4 6/2017 – b_dubb Jun 01 '17 at 19:08 — b_dubb, Jun 01 '17 at 19:08

score 1 · Answer 1 · answered Aug 21 '16 at 11:09

Although CodeIgniter uses mysqli driver the next_result() method is not set in the DB_driver. Here and here you can find further details. The idea is:

define next_result() method in system/database/DB_driver.php:

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

(be sure to push this up to your notes pile for the CI system core upgrade )

make sure you are using mysqli drive (application/config/database.php)
In your model after you call your stored procedure and BETWEEN each fetch of results use $query_or_somethig-else->next_result(); (and use $query_or_somethig-else->free_result(); in the final)

score 0 · Answer 2 · edited May 12 '15 at 09:43

I have been working on multiple query execution in store procedure in codeigniter I created a library named SP.php. Here is the code also change in config to database = mysqli

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

    class CI_SP {
        private $CI;

        function __construct() {
            $this->CI = & get_instance();
        }

        public function GetResults($SqlCommand) {
            $k = 0;
            $arr_results_sets = array();
            /* execute multi query */
            if (mysqli_multi_query($this->CI->db->conn_id, $SqlCommand)) {
                do {
                    $result = mysqli_store_result($this->CI->db->conn_id);
                    if ($result) {
                        $l = 0;
                        while ($row = $result->fetch_assoc()) {
                            $arr_results_sets[$k][$l] = $row;                        
                            $l++;
                        }
                    }
                    $k++;
                } while (mysqli_next_result($this->CI->db->conn_id));

                return $arr_results_sets;
            }
        }

}

and the model code is

    $result = $this->sp->GetResults("call function(parameters);  
    $result1 = $this->sp->GetResults("call function(parameters));  

    print_r($result);        
    print_r($result1);          
    exit;   
    return $query->result_array();

stored procedure with multiple resultset in Codeigniter

2 Answers2