CodeIgniter active records' problems calling multiple stored procedures

Question

class Registration_model extends CI_Model {

    function __construct() {
        parent::__construct();
    }   

    function check_email_availability($email)
    {
        $sql = "CALL proc_1301('$email');"; 
        $query = $this->db->query($sql) or die(mysql_error()); 
        return $query->row_array();
    }

    function check_username_availability($username)
    {
        $sqlt = "CALL proc_1303('$username');"; 
        $query = $this->db->query($sqlt) or die(mysql_error()); 
        return $query->row_array();
    }

    function process_registration($username, $email, $password)
    { 
        $sql = "CALL `proc_1302`('$username', '$email', '$password');"; 
        $query = $this->db->query($sql) or die(mysql_error()); 
        return $query->result_array();   
    }

this is my controller code which calls three functions from model one by one:

$emailCheckRes = $this->Registration_model->check_email_availability($email); 
$usernameCheckRes = $this->Registration_model->check_username_availability($username); 
$this->data['regRes'] = $this->Registration_model->process_registration($username, $email, $password);

my problem is when i run only one function it runs successfully but when i run two of them or all three it shows blank page... any idea why ???

SOLUTION

So finally the only solution we got for my own problem is :

/* ADD THIS FUNCTION IN SYSTEM/DATABASE/DB_ACTIVE_REC */
/* USAGE $this->db->freeDBResource($this->db->conn_id); */
function freeDBResource($dbh){
    while(mysqli_next_result($dbh)){
            if($l_result = mysqli_store_result($dbh)){
              mysqli_free_result($l_result);
            }
        }
}

Please feel free to post your solution as an answer below (and mark it as accepted) for the benefit of future visitors. — Colin Brock, Jul 30 '12 at 15:50

score 6 · Accepted Answer · edited May 23 '17 at 12:02

6

The problem is related to CodeIgniter's active recors and multiple database stored procedure calling.

First of all check that dbdriver parameter (application/config/database.php) is set to mysqli. Then, as described in "Calling a stored procedure from CodeIgniter's Active Record class" question on StackOverflow, adding to system/database/DB_active_rec.php the following function:

function freeDBResource($dbh){
    while(mysqli_next_result($dbh)){
            if($l_result = mysqli_store_result($dbh)){
              mysqli_free_result($l_result);
            }
        }
}

..And in your controller use:

$this->db->freeDBResource($this->db->conn_id);

after any stored procedure calling.

edited May 23 '17 at 12:02

Community

1
1

answered Jul 30 '12 at 17:50

deste

542
1
5
17

But i do not have DB_active_rec.php file in my CI package. So where to add? – Deepa MG Nov 02 '17 at 16:51
1

@DeepaMG If you do not have DB_active_rec.php file I suppose you are using Codeigniter 3.x version.. I haven't tested yet this hack with actual Codeigniter version. Can you confirm you have the same issue described here with CI3? Please try to execute $this->db->cache_off(); before stored procedure calling, then $this->db->cache_on(); after. If it does not work, you can try also with $this->db->close(); $this->db->reconnect(); between any calling. Let us know if one of these way works. – deste Nov 03 '17 at 23:49
Yes i got to realised later. I have added it in system/database/drivers/mysqli/mysqli_results.php. and that worked for me – Deepa MG Nov 04 '17 at 09:31
1

@deste it works in CI3 using $this->db->close(); $this->db->initialize(); instead of reconnect() between any calling – Yudhistira Bayu Jan 17 '19 at 08:37

score 0 · Answer 2 · answered Jul 30 '12 at 12:32

0

Model and Controller seems to be ok. If you try a model like:

class Test_model extends CI_Model
{
   function __construct()
   {
      parent::__construct();
   }
   function a($a_input)
   {
      return($a_input.': a');
   }
   function b($b_input)
   {
     return($b_input.': b');
   }
}

...And call it functions from a controller like this:

$this->load->model('Test_model');
    $a_result = $this->Test_model->a('aaa');
    $b_result = $this->Test_model->b('bbb');
    echo($a_result.'<br />'.$b_result);

You can see multiple function calling working fine.

Are you sure you can execute any of three function in model correctly, if you call only one? If yes, maybe the problem can be find in your stored procedures... Can you try to execute a normal query instead of stored procedures, in model functions? For debug your problem, check also in your /application/config/database.php if db_debug is set to TRUE.

answered Jul 30 '12 at 12:32

deste

542
1
5
17

Hi deste My db_debug is TRUE and i checked the procedures they are working fine i also checked them separately they are working absolutely fine. The only problem is when i run them together as i stated above it shows blank page and actually doesn't run any function... – Lonare Jul 30 '12 at 13:14
It seems that CodeIgniter does not like the calls to multiple stored procedures .. As if they were in conflict. In application/config/database.php dbdriver parameter is set to mysql or mysqli? You can try to set as the "mysqli"? – deste Jul 30 '12 at 14:12
yes it is already set to mysqli do you think it something related to codeignitor active records..?? – Lonare Jul 30 '12 at 14:16
Maybe I'm wrong, but I think that's the problem. The problem interests me, because I might soon need to do the same thing. Maybe here on StackOverflow there's already the answer to this problem: [Calling a stored procedure from CodeIgniter's Active Record class](http://stackoverflow.com/questions/4827752/calling-a-stored-procedure-from-codeigniters-active-record-class) I don't like to edit /system contents... But let me know if it works well. – deste Jul 30 '12 at 14:27
I'm happy to know it. Great! :-) – deste Jul 30 '12 at 15:51

CodeIgniter active records' problems calling multiple stored procedures

2 Answers2

Linked