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

score 15 · Accepted Answer · edited Oct 13 '11 at 12:31

15

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

edited Oct 13 '11 at 12:31

Dalen

8,856
4
47
52

answered Mar 11 '11 at 02:57

wework4web

398
2
12

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 – Aman Maurya Apr 30 '16 at 20:57

score 3 · Answer 2 · edited Jun 20 '15 at 06:55

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.

score 1 · Answer 3 · edited May 23 '17 at 12:31

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

score 1 · Answer 4 · answered Nov 27 '14 at 09:36

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();
}

score 1 · Answer 5 · edited Feb 22 '17 at 20:24

1

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.

edited Feb 22 '17 at 20:24

CDspace

2,639
18
30
36

answered Feb 22 '17 at 20:18

Gbang

11
3

Perfect. The above function may throw error if there are no multiple records. This works exactly. thanks – Deepa MG Nov 02 '17 at 17:11

score 0 · Answer 6 · answered Apr 28 '18 at 05:25

0

public function callSp_model{
   $sql = "CALL Materialwise_PURC_report(?,?,?)";
    $query = $this->lite_db->query($sql,array($supplierid,$fromdate,$todate));
    return $query->result_array(); }

answered Apr 28 '18 at 05:25

vijayabalan

91
1
3

5

Some explanations would significantly improve the quality of your answer – mrun Apr 28 '18 at 05:43

Calling a stored procedure from CodeIgniter's Active Record class

6 Answers6

Linked

Related