EllisLab text mark
Advanced Search
     
Multiple mysql Stored Procedures in 1 connection
Posted: 07 September 2007 06:53 PM
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

 
Posted: 07 September 2007 07:12 PM   [ # 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$sqlMYSQLI_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

 
Posted: 07 March 2008 02:18 PM   [ # 2 ]   [ Rating: 0 ]
Avatar
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 

A.

 
Posted: 16 June 2008 01:47 PM   [ # 3 ]   [ Rating: 0 ]
Joined: 2008-06-11
5 posts

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

 
Posted: 17 June 2008 11:09 AM   [ # 4 ]   [ Rating: 0 ]
Avatar
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 

A.

 
Posted: 22 June 2008 09:05 AM   [ # 5 ]   [ Rating: 0 ]
Avatar
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;
       
   
}   
}
?> 

cheers

 Signature 

A.

 
Posted: 24 June 2008 04:46 AM   [ # 6 ]   [ Rating: 0 ]
Avatar
Joined: 2007-11-20
34 posts

Hi Yauhen_S,
I was trying also to use your code in the class I posted but i couldn’t figure out how to
return the multiresult set from the query.

 Signature 

A.

 
Posted: 23 January 2011 07:57 AM   [ # 7 ]   [ Rating: 0 ]
Joined: 2011-01-23
4 posts

this will help you.

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

 
Posted: 08 August 2011 09:43 AM   [ # 8 ]   [ Rating: 0 ]
Avatar
Joined: 2011-02-23
47 posts

i have found this link http://ellislab.com/forums/viewthread/73714/ and it helped me.