EllisLab text mark
Advanced Search
1 of 2
1
   
execute mysql stored procedures with CodeIgniter
Posted: 08 February 2008 03:23 PM
Joined: 2008-02-08
1 posts

Hi to all, please I need to know if I can execute a stored procedure from mysql 5.0 using codeigniter´s database functions.

Thank you.

 
Posted: 09 February 2008 01:06 PM   [ # 1 ]   [ Rating: 0 ]
Joined: 2007-09-17
7 posts

Hy,

i dont think that you can do this via ci’s active-record-db-syntax. you’ll have to call the procedure like this:

$this->db->query("call myprocname(".$this->db->escape($parm).");  "); 
 
Posted: 26 February 2008 06:50 AM   [ # 2 ]   [ Rating: 0 ]
Joined: 2007-08-13
17 posts

hhhmm .. that last one didn’t seem to work for me either. I"m using vesion 1.6.1 right now . Any other ways that you migh suggest ?

 
Posted: 26 February 2008 07:23 AM   [ # 3 ]   [ Rating: 0 ]
Joined: 2006-07-14
4237 posts

You have to use the mysqli driver but there are no functions added to call stored procedures so you have to add them yourself or use the call_function method. If would be something like this

$stmt $this->db->call_function('stmt_init',$this->db->conn_id;);
if (
$this->db->call_function('stmt_prepare',$stmt'SELECT District FROM City WHERE Name=?')) {

    
/* bind parameters for markers */
    
$this->db->call_function('stmt_bind_param',$stmt"s"$city);

    
/* execute query */
    
$this->db->call_function('stmt_execute',$stmt);

    
/* bind result variables */
    
$this->db->call_function('stmt_bind_result',$stmt$district);

    
/* fetch value */
    
$this->db->call_function('stmt_fetch',$stmt);

    
printf("%s is in district %s\n"$city$district);

    
/* close statement */
    
$this->db->call_function('stmt_close',$stmt);

I took the example from php.net because i never used it but at least you get the idea how it can be done.

 
Posted: 26 February 2008 05:25 PM   [ # 4 ]   [ Rating: 0 ]
Avatar
Joined: 2008-02-26
15 posts

I’d interested to hear if xwero’s suggestion worked. I only just started using CodeIgniter and I’m a huge stored proc user.

 
Posted: 16 April 2008 06:48 AM   [ # 5 ]   [ Rating: 0 ]
Joined: 2007-09-05
4 posts

$this->db->query(“call myprocname(”.$this->db->escape($parm).”);  “);

ain’t work for me either.

is there any other solution to execute stored procedure at MySQL 5.0

 
Posted: 16 April 2008 10:45 AM   [ # 6 ]   [ Rating: 0 ]
Avatar
Joined: 2007-08-09
67 posts

We’re on MySQL 5.0.27, CI 1.6.1 and my stored proc call just looks like this:

$sql "CALL my_stored_proc(?)";
$params = array($only_input);
$this->my_db->query($sql$params);
return (
$this->my_db->affected_rows() == 1); 

Now, this is a simplistic version because I have no need for return values, and this is my first time using SPs, so I’m not exactly a fountain of information.  But it is clearly possible, so keep trying.

 
Posted: 22 April 2008 07:00 AM   [ # 7 ]   [ Rating: 0 ]
Joined: 2007-09-05
4 posts
$sql 'call prc_groupmenu(?)';
$param = array($idgroupuser);
$query $this->db->query($sql,$param); 

An Error Was Encountered
Error Number: 1312

PROCEDURE optis.prc_groupmenu can’t return a result set in the given context

call prc_groupmenu(‘1’)

I’ve try that… still error for me… any other solution???
maybe you’ve been modified another file ??
I see that you’ve have wrote $this->my_db->query, not like usual $this->db->query
did you change database driver class ???

thanks…

 
Posted: 22 April 2008 01:47 PM   [ # 8 ]   [ Rating: 0 ]
Joined: 2008-04-18
3 posts

Hi

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

http://us2.php.net/mysqli

 
Posted: 06 August 2008 10:50 AM   [ # 9 ]   [ Rating: 0 ]
Joined: 2008-07-03
5 posts

hi ....

I too am getting the same error can u please guide me how to use mysqli driver

 
Posted: 16 October 2008 06:17 PM   [ # 10 ]   [ Rating: 0 ]
Joined: 2008-10-16
6 posts

This is what you need to do.

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

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->passwordTRUE,CLIENT_MULTI_STATEMENTS);
    

Then go to config/database.php and change

$db['default']['pconnect'TRUE

to

$db['default']['pconnect'FALSE

Enjoy!

Alex

 
Posted: 25 February 2009 08:58 PM   [ # 11 ]   [ Rating: 0 ]
Joined: 2009-01-06
1 posts
Alexandros Monastiriotis - 22 April 2008 05:47 PM

Hi

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

http://us2.php.net/mysqli

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… wink

 
Posted: 05 November 2009 03:05 PM   [ # 12 ]   [ Rating: 0 ]
Joined: 2009-09-25
3 posts

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

$db['default']['dbdriver'"mysqli"


query

$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

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.

 
Posted: 17 December 2009 03:18 PM   [ # 13 ]   [ Rating: 0 ]
Avatar
Joined: 2009-12-16
6 posts

I tried these codes, but I’m unable to make it work
Can someone help me? >.<

 
Posted: 21 February 2010 03:26 PM   [ # 14 ]   [ Rating: 0 ]
Joined: 2010-02-21
1 posts
boltoncole - 17 December 2009 08:18 PM

I tried these codes, but I’m unable to make it work
Can someone help me? >.<


I’m not passing in any parameters, but I have successfully called a proc this way.

$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). 

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

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

 
Posted: 28 February 2010 06:18 AM   [ # 15 ]   [ Rating: 0 ]
Joined: 2010-02-28
7 posts

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

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

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

 
1 of 2
1