CODEWITHSUNDEEP
phpmysqlcodeigniterstored-procedures
Ganesh Aher
Ganesh Aher

Reputation: 1126

How to use two or more stored procedures in codeigniter code?

I'm working on Codeigniter project and I'm using a stored procedures for my back-end operations. When I'm using a single stored procedure for SELECT statement, it will work fine. But when I'm trying to call two or more stored procedures for same operation it gives me an error.

ERROR:

A Database Error Occurred

Error Number: 2014

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

call all_Cluster()

Filename: C:/xampp/htdocs/prop/system/database/DB_driver.php

Line Number: 691

Here is my code:

Model:

public function getProperty()
    {
        $query = $this->db->query('call select_prop_SP()');
        // if ($query->num_rows() > 0) {
        return $query->result();
        $query->next_result(); 
        $query->free_result();
    }
public function area()
    {
         $query = $this->db->query('call select_area_SP()');
        // if ($query->num_rows() > 0) {
        return $query->result();
        $query->next_result(); 
        $query->free_result();
    }
public function cluster()
    {
        $query = $this->db->query('call select_Cluster_SP()');
        // if ($query->num_rows() > 0) {
        return $query->result();
        $query->next_result(); 
        $query->free_result();
    }

Here is my SP:

select_area_SP

BEGIN
SELECT `area_id`, `area_name`, `area_status` FROM `tbl_area`;
END

select_Cluster_SP

BEGIN
SELECT `cluster_id`, `cluster_name`, `area_id`, `cluster_status` FROM `tbl_cluster`;
END

I've googled it for long time but I haven't gate any positive result. I've gone through Calling stored procedure in codeigniter and make all changes, but no success.

Any kind of help is appreciated. Thanks in advance.

Upvotes: 1

Views: 1166

Answers (1)

Ivan Cachicatari
Ivan Cachicatari

Reputation: 4284

You next_result and free_result functions are not executing because the return sentence exits function. You must store the result in a temporary variable before return:

public function cluster()
{
        $query = $this->db->query('call select_Cluster_SP()');
        $result = $query->result();
        $query->next_result(); 
        $query->free_result();
        return $result;
}

OR change your configuration to mysqli driver that includes the next_result function after every stored procedure call: 

$db['default']['dbdriver'] = 'mysqli';

Upvotes: 1

Related Questions