MySQL 在codeigniter中调用存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7767231/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calling stored procedure in codeigniter
提问by Tausif Khan
I am using latest codeigniter and trying to call stored procedure from my model. Also I am using mysqli as database driver. Now I am having an error when I call two stored procedures. Following is the error:
我正在使用最新的 codeigniter 并尝试从我的模型中调用存储过程。我也使用 mysqli 作为数据库驱动程序。现在，当我调用两个存储过程时出现错误。以下是错误：
Error Number: 2014
Commands out of sync; you can't run this command now
call uspTest();
Filename: E:\wamp\www\reonomy-dev\system\database\DB_driver.php
Line Number: 330
错误编号：2014
命令不同步；你现在不能运行这个命令
调用 uspTest();
文件名：E:\wamp\www\reonomy-dev\system\database\DB_driver.php
行号：330
Note that when I call a single stored procedure it works fine. Here is the code for model.
请注意，当我调用单个存储过程时，它工作正常。这是模型的代码。
class Menus_model extends CI_Model {
function __construct()
{
parent::__construct();
}
public function getMenus()
{
$query = $this->db->query("call uspGetMenus()");
return $query->result();
}
public function getSubMenus()
{
$query = $this->db->query("call uspTest()");
return $query->result();
}
}
Here is the code from controller
这是控制器的代码
class MYHQ extends CI_Controller {
public function __construct()
{
parent::__construct();
$this->load->model('menus_model');
}
public function index()
{
$menu = $this->menus_model->getMenus();
$submenu = $this->menus_model->getSubMenus();
}
}
Is there any solution without hacking the core of codeigniter??
有没有不破解codeigniter核心的解决方案？
采纳答案by kaqqao
This seems to be a bug in CodeIgniter. How come it's still in there is beyond me. However, there's a couple of ways to overcome it.
这似乎是 CodeIgniter 中的一个错误。它怎么还在那里超出我的范围。但是，有几种方法可以克服它。
Check here: http://codeigniter.com/forums/viewthread/73714/Basically, you modify mysqli_result.php to include next_result() function and make sure to call it after every stored proc. call. Just note that it assumes you're using mysqli as your DB driver... but you can probably do something similar with any other. You can change your driver in /application/config/database.php It's the line that says
在这里查看：http: //codeigniter.com/forums/viewthread/73714/ 基本上，您修改 mysqli_result.php 以包含 next_result() 函数并确保在每个存储过程之后调用它。称呼。请注意，它假定您使用 mysqli 作为您的数据库驱动程序......但您可能可以用任何其他方式做类似的事情。您可以在 /application/config/database.php 中更改您的驱动程序 这是说
$db['default']['dbdriver'] = 'mysql';
$db['default']['dbdriver'] = 'mysql';
by default. Change it to:
默认情况下。将其更改为：
$db['default']['dbdriver'] = 'mysqli';
$db['default']['dbdriver'] = 'mysqli';
You could also just close/reopen a DB connection between the calls, but I would definitely advise against that approach.
您也可以关闭/重新打开调用之间的数据库连接，但我绝对建议不要使用这种方法。
回答by Norman
I follow the blog of Mr. Tim Brownlaw:
http://ellislab.com/forums/viewthread/73714/#562711
我关注 Tim Brownlaw 先生的博客：http://ellislab.com/forums/viewthread/73714/#562711
First, modify application/config/config.php, line 55.
首先修改application/config/config.php，第55行。
$db['default']['dbdriver'] = 'mysqli'; // USE mysqli
Then, add the following into mysqli_result.php that is missing this command for some strange reason (under /system/database/drivers/mysqli/mysqli_result.php).
然后，将以下内容添加到 mysqli_result.php 中，因为某些奇怪的原因（在 /system/database/drivers/mysqli/mysqli_result.php 下）缺少此命令。
/**
* Read the next result
*
* @return null
*/
function next_result()
{
if (is_object($this->conn_id))
{
return mysqli_next_result($this->conn_id);
}
}
Then, in your model, add $result->next_result().
然后，在您的模型中，添加$result->next_result().
Below is my example.
下面是我的例子。
function list_sample($str_where, $str_order, $str_limit)
{
$qry_res = $this->db->query("CALL rt_sample_list('{$str_where}', '{$str_order}', '{$str_limit}');");
$res = $qry_res->result();
$qry_res->next_result(); // Dump the extra resultset.
$qry_res->free_result(); // Does what it says.
return $res;
}
回答by jonas
Having the same problem I found another approach which doesn't change the core, but instead uses a small helper.
遇到同样的问题，我找到了另一种方法，它不会改变核心，而是使用一个小助手。
Edit:The below linked asset is nowhere to be found.
编辑：以下链接的资产无处可寻。
See CoreyLoose post.
请参阅 CoreyLoose 帖子。
https://ellislab.com/forums/viewthread/71141/#663206
https://ellislab.com/forums/viewthread/71141/#663206
I had to make a small adjusment to his helper though. The line
不过，我不得不对他的帮手做一个小小的调整。线
if( get_class($result) == 'mysqli_stmt' )
could possibly produce a warning since the $result sometimes is passed as a boolean. I just put a check prior to this line and now it works perfectly, with no tinkering with the core!
可能会产生警告，因为 $result 有时作为布尔值传递。我只是在此行之前进行了检查，现在它可以完美运行，无需修改核心！
回答by Fredy
change dbdriver to "mysqli" put this function to your model and use it to call stored procedure
将 dbdriver 更改为 "mysqli" 将此函数放入您的模型并使用它来调用存储过程
function call( $procedure )
{
$result = @$this->db->conn_id->query( $procedure );
while ( $this->db->conn_id->next_result() )
{
//free each result.
$not_used_result = $this->db->conn_id->use_result();
if ( $not_used_result instanceof mysqli_result )
{
$not_used_result->free();
}
}
return $result;
}
