+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Multiple mysql Stored Procedures in 1 connection (/showthread.php?tid=3070)
Multiple mysql Stored Procedures in 1 connection - El Forum - 09-07-2007
[eluser]Unknown[/eluser]
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
Multiple mysql Stored Procedures in 1 connection - El Forum - 09-07-2007
[eluser]LeePR[/eluser]
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:
Code:
/** 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
Multiple mysql Stored Procedures in 1 connection - El Forum - 03-07-2008
[eluser]Atasa[/eluser]
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
Multiple mysql Stored Procedures in 1 connection - El Forum - 06-16-2008
[eluser]Yauhen_S[/eluser]
Hi! You can also look here
http://ellislab.com/forums/viewthread/73714/
Multiple mysql Stored Procedures in 1 connection - El Forum - 06-17-2008
[eluser]Atasa[/eluser]
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:
Code:
<?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
Code:
$this->load->library('myDB');
$result = $this->myDB->GetResults(CALL MyProc());Multiple mysql Stored Procedures in 1 connection - El Forum - 06-22-2008
[eluser]Atasa[/eluser]
I apologize for the previous post.
It was wrong....
Here it is the class library corrected
Code:
<?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
Multiple mysql Stored Procedures in 1 connection - El Forum - 06-24-2008
[eluser]Atasa[/eluser]
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.
Multiple mysql Stored Procedures in 1 connection - El Forum - 01-23-2011
[eluser]safarath[/eluser]
this will help you.
http://ellislab.com/forums/viewthread/179001/
Multiple mysql Stored Procedures in 1 connection - El Forum - 08-08-2011
[eluser]Rahul gamit[/eluser]
i have found this link http://ellislab.com/forums/viewthread/73714/ and it helped me.