Calling a stored procedure from CodeIgniter's Active Record class

Calling a stored procedure from CodeIgniter's Active Record class

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

Answer by wework4web for 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);              }          }  }  

Answer by Arafat Rahman for Calling a stored procedure from CodeIgniter's Active Record class

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

Answer by user2182143 for Calling a stored procedure from CodeIgniter's Active Record class

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

Answer by johnoDread for Calling a stored procedure from CodeIgniter's Active Record class

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.

Answer by Vince Osana for Calling a stored procedure from CodeIgniter's Active Record class

I am new here and I dont know if this is allowed, because I cant post another question if there are same problem already asked in this site, so I assume that I can ask my problem here about my program, I cant call my SP properly in CI can someone help me?

This is the error: Fatal error: Call to undefined method Welcome::navi_model() in /home/development/public_html/rmt/application/controllers/welcome.php on line 252

here is my sample code:

Controller

    function upload_gen048()  {    $data['uploadData']=$this->input->post('upload');                $this->load->model('navi_model');              $query = $this->navi_model($data);               $data = array('msg' => "Upload success!");              $data['main_content'] = 'gen048';              $this->load->view('includes/template',$data);              $data['uploadData'] = $this->upload->data();  

Model

function upload_gen048($data)  {    $qString = '[gen048upload] '; //STORED PROCEDURE      $qString .= "'"                . $data['filedate'] . "','";               . $data['accdate'] . "','";            echo $qString;            $this->db->query('set ansi_padding on                          set ARITHABORT on                          set CONCAT_NULL_YIELDS_NULL on                          set QUOTED_IDENTIFIER on                          set ANSI_NULLS on                          set ANSI_WARNINGS on                          set numeric_roundabort off');            $query = $this->db->query($qString);        return ($query->num_rows() > 0) ? $query->result() : NULL;  }  

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72