Multiple mysql Stored Procedures in 1 connection / Forums / Community / EllisLab

Forum Logo

Multiple mysql Stored Procedures in 1 connection

fgcc

Posted: 07 September 2007 06:53 PM

[ Ignore ]

Joined: 2007-09-04
1 posts

I have a question about using MySQL stored procedures with one controller/view using PHP5, and the mysqli driver.

Whenever I try to do 2 Procedure calls with one controller/connection I get the following error:

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

They are called in this fashion:

$sql = “CALL usp_Procedure1(?)”;
$parms = array($id);
$qresult=$this->db->query($sql, $parms);

Anyone know how to get around this? I think this is a mySQL 5 issue that is preventing multiple Stored Procedure calls in one connection.

Thanks,

Reg

LeePR

Posted: 07 September 2007 07:12 PM

[ Ignore ] [ # 1 ] [ Rating: 0 ]

Joined: 2007-06-04
16 posts

I had a similar problem - the query() is returning two result sets for stored procedures. The first is what you expect (the result)... I forget what the second one is. Anyway, in my own database class (extends PHP’s mysqli) I overcame it with a simple call to mysqli_next_result(). I’m not familiar enough with CI yet to give you the answer you’re looking for, but this should help. Here’s the code for my DB class query() method:

/** Execute an SQL query and return the result set.*/
    public function query($sql) {            
        $result = mysqli_query(self::$INSTANCE, $sql, MYSQLI_STORE_RESULT);

        if(mysqli_error($this)){
              throw new RSFSException(mysqli_error($this), mysqli_errno($this));
        }

        if (mysqli_next_result($this)) {
            /** Do nothing; executing stored procedures return a second result
             * object resulting in a 'commands out of synch error'. */
        }



        return $result; 

    } 

If I removed the mysqli_next_result($this) call and tried to execute two stored procedures on the same page, I would get exactly the same message as you (Commands out of sync; you can’t run this command now).

Maybe in CI you can just do something like $query->result()->getNext()?

HTH,
Lee

Atasa

Posted: 07 March 2008 03:18 PM

[ Ignore ] [ # 2 ] [ Rating: 0 ]

Joined: 2007-11-20
34 posts

I had also similar problems.
I changed the database driver to mysqli
tweeked the _execute function in system/database/drivers/mysqli/mysqli_driver.php
from @mysqli_query to @mysqli_multi_query.
Now it works.
I start getting really nervous sometimes with CI and small things like this.
For example you could not set the character_collation connection before 1.6.1

Cheers

Signature

Yauhen_S

Posted: 16 June 2008 01:47 PM

[ Ignore ] [ # 3 ] [ Rating: 0 ]

Joined: 2008-06-11
5 posts

Hi! You can also look here
http://ellislab.com/forums/viewthread/73714/

Atasa

Posted: 17 June 2008 11:09 AM

[ Ignore ] [ # 4 ] [ Rating: 0 ]

Joined: 2007-11-20
34 posts

Hey Yauhen_S nice approach,
I was just thinking to write a library file that does just this.
And here is why I thought of this.
1.) The _execute function from version 1.6.1 to version 1.6.2 changed…
So in order to maintain a successful upgrade you don’t need to alter system files.
2.) You don’t need to worry if the other db function will work cause you call this method library only when you want to return a multi result set.

Therefore I wrote the following:

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class MyDB
{
   private $CI;
   
   /**
   * The constructor
   */
   
   function __construct()
   {
     $this->CI =& get_instance();
   }
 
    public function GetResults($SqlCommand)
    {
    /* execute multi query */
    if (mysqli_multi_query($this->CI->db->conn_id, $SqlCommand)) {
        do 
        {
            $result = mysqli_store_result($this->CI->db->conn_id);
        }
        while(mysqli_next_result($this->CI->db->conn_id)); 

       return $result; 
    }
    }   
}
?> 

then in your models you can just do

$this->load->library('myDB');
$result = $this->myDB->GetResults(CALL MyProc()); 

Signature

Atasa

Posted: 22 June 2008 09:05 AM

[ Ignore ] [ # 5 ] [ Rating: 0 ]

Joined: 2007-11-20
34 posts

I apologize for the previous post.
It was wrong….
Here it is the class library corrected

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Mydb
{
   private $CI, $Data, $mysqli, $ResultSet;
   
   /**
   * The constructor
   */
   
   function __construct()
   {
     $this->CI =& get_instance();
     $this->Data = '';
     $this->ResultSet = array();
     $this->mysqli = $this->CI->db->conn_id;
   }

    public function GetMultiResults($SqlCommand)
    {
    /* execute multi query */
    if (mysqli_multi_query($this->mysqli, $SqlCommand)) {
        do
        {
             if ($result = $this->mysqli->store_result()) 
             {
                while ($row = $result->fetch_array())
                {
                    $this->Data[] = $row;
                }
                mysqli_free_result($result);
             }
        }
        while ($this->mysqli->next_result());
    }
    return $this->Data;
       
   }   
}
?> 