execute mysql stored procedures with CodeIgniter

[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

[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... ;-)

[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.

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

[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.

[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:

<?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

[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

[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]

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

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