CodeIgniter Forums
execute mysql stored procedures with CodeIgniter - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: execute mysql stored procedures with CodeIgniter (/showthread.php?tid=5935)

Pages: 1 2

execute mysql stored procedures with CodeIgniter - El Forum - 10-16-2008

[eluser]alx_benjamin[/eluser]
This is what you need to do.

In CI open file system/database/drivers/mysql/mysql_driver.php and
change function db_connect to:

Code:
function db_connect()
    {
    define("CLIENT_MULTI_RESULTS",131072);//Enable/disable multi-results
    define("CLIENT_MULTI_STATEMENTS",65536);//Enable/disable multi-statement support
        
    return @mysql_connect($this->hostname, $this->username, $this->password, TRUE,CLIENT_MULTI_STATEMENTS);
    }

Then go to config/database.php and change
Code:
$db['default']['pconnect'] = TRUE;
to
Code:
$db['default']['pconnect'] = FALSE;

Enjoy!

Alex

execute mysql stored procedures with CodeIgniter - El Forum - 02-25-2009

[eluser]Unknown[/eluser]
[quote author="Alexandros Monastiriotis" date="1208900869"]Hi

the fellow programmer 'xwero' is 100% correct. You need to use mysqli driver and not mysql.

http://us2.php.net/mysqli[/quote]

Ummm... I beg to differ here based on what I'm seeing. When using the 'call_method' function seems the part where the sample code gets the '$stmt' variable involved in the equation, all bets appear to be off. Any 'statement' object reference that the database reference was supposed to return from the 'stmt_init' method isn't quite what's expected. When used as a reference when calling the 'stmt_bind_param' function PHP cries about an invalid object or mysqli_stmt reference. I've used mysql(5) stored procs w/ php(5) for quite a while now without any issues. The deeper I dig into the framework it seems the more the database abstraction layer asphyxiates me. Can someone please shed a bit more light on this subject?

Thanx in advance, y'all - peace... ;-)

execute mysql stored procedures with CodeIgniter - El Forum - 11-05-2009

[eluser]CoreyLoose[/eluser]
I wasn't able to get any of the solutions given here to work for me, but I was able to get a hybrid approach to work.


database.php
Code:
$db['default']['dbdriver'] = "mysqli";


query
Code:
$this->load->helper('mysqli');

$result = $this->db->query( 'CALL MyProc(?)', array($parameter) );
print_r( $result->result_array() );

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


mysqli_helper.php
Code:
function clean_mysqli_connection( $dbc )
{
    while( mysqli_more_results($dbc) )
    {
        if(mysqli_next_result($dbc))
        {
            $result = mysqli_use_result($dbc);
            
            if( get_class($result) == 'mysqli_stmt' )
            {
                mysqli_stmt_free_result($result);
            }
            else
            {
                unset($result);
            }
        }
    }
}

Until I started using the clean_mysqli_connection function I was getting an error along the lines of 'Commands out of sync; you can't run this command now' whenever I tried to call a procedure a second time.

execute mysql stored procedures with CodeIgniter - El Forum - 12-17-2009

[eluser]boltoncole[/eluser]
I tried these codes, but I'm unable to make it work
Can someone help me? >.<

execute mysql stored procedures with CodeIgniter - El Forum - 02-21-2010

[eluser]Unknown[/eluser]
[quote author="boltoncole" date="1261099094"]I tried these codes, but I'm unable to make it work
Can someone help me? >.<[/quote]


I'm not passing in any parameters, but I have successfully called a proc this way.
Code:
$sql = "CALL my_stored_proc()";
$this->db->query($sql);

I would imagine that the parameters would need to be passed in like this (with single quotes if the params are strings).

Code:
$sql = "CALL my_stored_proc(".$param1.", ".$param2.")";
$this->db->query($sql);

I'm using the mysql driver - not the mysqli driver.

execute mysql stored procedures with CodeIgniter - El Forum - 02-28-2010

[eluser]cjbuchmann[/eluser]
Just wanted to say that I found a decent solution that allows you to pass params and get values :

1. in 'system/application/config/database.php' - change the $db['default']['dbdriver'] to 'mysqli' - (mysql driver doesn't seem to support stored procedures)

2. I created a model for my stored procedure as follows, I put the whole thing in case anyone has any questions, but the focus is on the 'add_movie' function.


#START MODEL
Code:
&lt;?php

class StoredProcedure_model extends Model {
    
    function __construct()
    {
        parent::Model();
    }
    
    function add_movie()
    {
        //post data
        $movie = $_POST['movie'];
        $star = $_POST['star'];
        $genre = $_POST['genre'];
        
        $query = " CALL add_movie(?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
        
        //the data to replace
        $data = array(
            $movie['name'],
            $movie['date'],
            $movie['director'],
            $movie['banner'],
            $movie['trailer'],
            $star['firstname'],
            $star['lastname'],
            $star['dob'],
            $star['photo'],
            $genre
        );
        
        $result = $this->db->query($query, $this->safe_escape($data));
        $data = "";
        
       foreach($result->result_array() as $row)
       {
            $data.= $row['results']."<br />\r\n";
       }
      
       return $data;
    }
    
    //escapes and adds single quotes
    //to each value of an array
    function safe_escape(&$data)
    {
        if(count($data) <= 0)
        {
            return $data;
        }
        
        foreach($data as $node)
        {
            $node = $this->db->escape($node);
        }
        
        return $data;
    }

}
#END MODEL

3. I then retrieved the data into a variable as I called the function and passed it to my view like so (Note, I'm using the extremely helpful 'Template' code, but you would add the data to a normal view in the same way) :

Code:
//Other Code...
if($this->form_validation->run())
{
    $this->load->model('storedProcedure_model', 'sp');
    
    $data = array();
    $data['result'] = $this->sp->add_movie();
    $this->template->write_view('content', 'display_movie_message', $data);
}
else
{
    $this->template->write_view('content', 'add_movie');    
}
//...Other Code


AND... thats pretty much it.

So to sum up:
1. Change database to mysqli (don't touch the driver class itself)
2. Create a Model class for your stored procedure and add a function to use it
3. Call the stored procedure method from that model class where needed and pass the data to your view in traditional CI form.

Note - My returned data was actually a single table that had a list of messages that were inserted as the procedure ran.


A few things ...
1. Sorry if my Codeigniter code isn't up to par. I'm two days new, so I hope you'll forgive any improper conventions.

2. I noticed that apart from changing the driver, everything else seems to be very standard codeigniter procedure. However, since I saw quite a few methods saying to do various things that weren't very standard, I thought I'd just display the entire process. The hope is that it will eliminate any confusion from other posts.

3. Hope this helps someone Smile

execute mysql stored procedures with CodeIgniter - El Forum - 04-21-2010

[eluser]Unknown[/eluser]
thanks cjbuchmann..
its working for me.. i just changed the db driver to mysqli and called proceedure as usual from a model.. its working fine... thanks a lot

execute mysql stored procedures with CodeIgniter - El Forum - 08-22-2010

[eluser]mooooo[/eluser]
NOTE! I fixed it by simple fetching the second empty result set from the stored procedure call.

In system/database/drivers/mysqli/mysqli_result.php I added the following function:

Code:
function next_result()
{
  if (is_object($this->conn_id))
  {
    return mysqli_next_result($this->conn_id);
  }
}

...and in my model I simple did $result->next_result(); after the stored procedure call.

My issue is shown below

- - - - - - - -


Hi,

I have a issue connected to this thread. I'm using stored procedures with MySQLi and the procedure is working splendid. My problem is when I try to make another standard SQL query. I get the following error:

Code:
Error Number: 2014
Commands out of sync; you can't run this command now

Below are my two model functions:
Code:
function get() {
    $startdate = date('Y-m-d');
    $enddate = '2010-10-01';
    $query = 'CALL get_calendar("?", "?")';
    $result = $this->db->query($query, array($startdate, $enddate));
    return $result->result_object();
}
    
function unavailable_weeks() {
    $query = 'SELECT * FROM weeks_unavailable';
    $result = $this->db->query($query);
    return $result->result_object();
}

I'm running these from my controller like so:
Code:
function index() {
   $data['dates'] = $this->week->get();            
   $data['unavailable'] = $this->to_array($this->week->unavailable_weeks(), 'id', 'week');
   $this->render('index', 'Välj veckor', $data);
}

$this->render and $this->to_array are custom functions but this is just to show the calls to the model.

Does anyone know how to solve this?

Regards,
Niklas[/b]

execute mysql stored procedures with CodeIgniter - El Forum - 01-23-2011

[eluser]safarath[/eluser]
This link will help you !!!

http://ellislab.com/forums/viewthread/179001/