Calling a stored procedure from CodeIgniter's Active Record class

Question

In my CI application setup to query a mssql database. I want to execute a stored procedure from active record. But I can't get hold of any solid documentation.

Does anyone have any experience with calling stored procs with CodeIgniter and/or Active Record and passing in parameters?

Thanks,

Billy

Solution 1

Yes , try this in your model.

$this->db->query("call {storedprocedure function name} ");

if you encounter trouble calling more than 1 stored procedure at a time you need to add the following line

/* 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);
            }
        }
}

Solution 2

If you are using later versions of codeigniter with mssql or sqlsrv with stored procedures, using 'CALL' as in query('CALL procedureName($param1,$params,....)') may not work.

In the case of MSSQL use:

$this->db->query('EXEC procedureName')

OR

$this->db->query('EXEC procedureName $param1 $param2 $param3,...')

In some cases you might need to turn on some constants for the driver. In this case run:

$this->db->query('Set MSSQL constant ON )

before running your regular query.

Solution 3

I have added the following function to class CI_DB_mysqli_driver in /system/database/drivers/mysqli/mysqli_driver.php

    function free_db_resource()
    {
        while(mysqli_next_result($this->conn_id))
        {
            if($l_result = mysqli_store_result($this->conn_id))
            {
                mysqli_free_result($l_result);
            }
        }
    }

and use it after the procedure call

$this->db->free_db_resource();

Thanks to wework4web

Solution 4

This a little modification from above answer. If you are using codeigniter 3 place this code in /system/database/drivers/mysqli/mysqli_driver.php:

function free_db_resource()
{
    do
    {
        if($l_result = mysqli_store_result($this->conn_id))
        {
            mysqli_free_result($l_result);
        }
    }
    while(mysqli_more_results($this->conn_id)  && mysqli_next_result($this->conn_id));
}

Then just call the function like others suggested here.

Solution 5

A simply way to call your stored procedure which has parameters is by using query() method provided by database library of Codeigniter.

In your model:-

function call_procedure(){
    $call_procedure ="CALL TestProcedure('$para1', '$para2', @para3)";
    $this->db->query($call_procedure);
    $call_total = 'SELECT @para3 as Parameter3';
    $query = $this->db->query($call_total);
    return $query->result();
}

View more solutions

Comments

[Aman Maurya]

Thanks you, I almost waste 5 hour to figure out this problem ,finally i got the right place.There was some mistake in the code for calling the function to free the object but i changed "$this->db->freeDBResource($this->db->conn_id);" to "$this->freeDBResource($this->db->conn_id);" ,and It worked perfectly

[Deepa MG]

Perfect. The above function may throw error if there are no multiple records. This works exactly. thanks

[mrun]

Some explanations would significantly improve the quality of your answer