execute mysql stored procedures with CodeIgniter

[eluser]Unknown[/eluser]
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.

[eluser]Christian Rößler[/eluser]
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:

Code:

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

[eluser]MABUS[/eluser]
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 ?

[eluser]xwero[/eluser]
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

Code:

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

[eluser]jdgiotta[/eluser]
I'd interested to hear if xwero's suggestion worked. I only just started using CodeIgniter and I'm a huge stored proc user.

[eluser]haekal[/eluser]

$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

[eluser]MadZad[/eluser]
We're on MySQL 5.0.27, CI 1.6.1 and my stored proc call just looks like this:

Code:

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

[eluser]haekal[/eluser]

Code:

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

[eluser]Alexandros Monastiriotis[/eluser]
Hi

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

http://us2.php.net/mysqli

[eluser]abhigudi[/eluser]
hi ....

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