Calling a Stored Procedure from Codeigniter's Active Record Class

Calling a stored procedure from CodeIgniter's Active Record class

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

CodeIgniter active records' problems calling multiple stored procedures

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.

Call multiple stored procedures from Codeigniter last result contains the previous

Initiate $this->data = '' into the function GetMultiResults() instead into constructor.
I think this will solve your problem.

CodeIgniter active records' problems calling multiple stored procedures

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.

Call stored procedure from php codeigniter

Glad to say that I will maintain my job.

Here goes the post that solved my problem.
Remembering I'm working with codeigniter on php and mssql database:

In model:

public function execute_sp($var1 = NULL,$var2 = NULL, $var3 = NULL, $var4 = NULL, $var5 = NULL, $var6 = NULL, $var7 = NULL, $var8 = NULL, $var9 = NULL, $var10 = NULL){
$sp = "stored_procedure_name ?,?,?,?,?,?,?,?,?,? "; //No exec or call needed

//No @ needed.  Codeigniter gets it right either way
$params = array(
'PARAM_1' => NULL,
'PARAM_2' => NULL,
'PARAM_3' => NULL,
'PARAM_4' => NULL,
'PARAM_5' => NULL,
'PARAM_6' => NULL,
'PARAM_7' => NULL,
'PARAM_8' => NULL,
'PARAM_9' => NULL,
'PARAM_10' =>NULL);

$result = $this->db->query($sp,$params);

In controller:

$var1 = 'value';
$var2 = 'value';
$var3 = 'value';
$var4 = 'value';
$var5 = 'value';
$var6 = 'value';
$var7 = 'value';
$var8 = 'value';
$var9 = 'value';
$var10 = 'value';

$this->model->sp($var1, $var2, $var3, $var4, $var5, $var6, $var7, $var8, $var9, $var10);

It works!

source that helped so much: Issue executing stored procedure from PHP to a Microsoft SQL SERVER reply from user @Ulises Burlando

Calling stored procedure in codeigniter

This seems to be a bug in CodeIgniter. How come it's still in there is beyond me.
However, there's a couple of ways to overcome it.

Check here: http://codeigniter.com/forums/viewthread/73714/
Basically, you modify mysqli_result.php to include next_result() function and make sure to call it after every stored proc. call.
Just note that it assumes you're using mysqli as your DB driver... but you can probably do something similar with any other. You can change your driver in /application/config/database.php It's the line that says

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

by default. Change it to:

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

You could also just close/reopen a DB connection between the calls, but I would definitely advise against that approach.

How to call a stored procedure in CodeIgniter?

I think you are using the following way to call procedure.

$this->db->call_function('test_proc');

Its wrong. Only default procedures can be called using this method.
To call procedures defined by you, you have to go with

$this->db->query("call test_proc()");

---

Related Topics