php 从 CodeIgniter 的 Active Record 类调用存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4827752/
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 a stored procedure from CodeIgniter's Active Record class
提问by iamjonesy
In my CI applicationsetup to query a mssqldatabase. I want to execute a stored procedurefrom active record. But I can't get hold of any solid documentation.
在我的CI 应用程序设置中查询mssql数据库。我想stored procedure从active record. 但我无法掌握任何可靠的文件。
Does anyone have any experience with calling stored procs with CodeIgniterand/or Active Recordand passing in parameters?
有没有人有使用CodeIgniter和/或Active Record传入参数调用存储过程的经验？
Thanks,
谢谢，
Billy
比利
回答by wework4web
Yes , try this in your model.
是的，在你的模型中试试这个。
$this->db->query("call {storedprocedure function name} ");
if you encounter trouble calling more than 1 stored procedure at a time you need to add the following line
如果您在一次调用 1 个以上的存储过程时遇到问题，则需要添加以下行
/* ADD THIS FUNCTION IN SYSTEM/DATABASE/DB_ACTIVE_REC */
/* USAGE $this->db->freeDBResource($this->db->conn_id); */
function freeDBResource($dbh){
while(mysqli_next_result($dbh)){
if($l_result = mysqli_store_result($dbh)){
mysqli_free_result($l_result);
}
}
}
回答by johnoDread
If you are using later versions of codeigniter with mssql or sqlsrv with stored procedures, using 'CALL' as in query('CALL procedureName($param1,$params,....)')may not work.
如果您将更高版本的 codeigniter 与 mssql 或 sqlsrv 与存储过程一起使用，则使用 'CALL'query('CALL procedureName($param1,$params,....)')可能不起作用。
In the case of MSSQL use:
在 MSSQL 的情况下使用：
$this->db->query('EXEC procedureName')
OR
或者
$this->db->query('EXEC procedureName $param1 $param2 $param3,...')
In some cases you might need to turn on some constants for the driver. In this case run:
在某些情况下，您可能需要为驱动程序打开一些常量。在这种情况下运行：
$this->db->query('Set MSSQL constant ON )
before running your regular query.
在运行常规查询之前。
回答by Gbang
This a little modification from above answer. If you are using codeigniter 3 place this code in /system/database/drivers/mysqli/mysqli_driver.php:
这是对上述答案的一点修改。如果您使用的是 codeigniter 3，请将此代码放在 /system/database/drivers/mysqli/mysqli_driver.php 中：
function free_db_resource()
{
do
{
if($l_result = mysqli_store_result($this->conn_id))
{
mysqli_free_result($l_result);
}
}
while(mysqli_more_results($this->conn_id) && mysqli_next_result($this->conn_id));
}
Then just call the function like others suggested here.
然后就像其他人在这里建议的那样调用该函数。
回答by Arafat Rahman
I have added the following function to class CI_DB_mysqli_driverin /system/database/drivers/mysqli/mysqli_driver.php
我在 /system/database/drivers/mysqli/mysqli_driver.php 中的类 CI_DB_mysqli_driver中添加了以下函数
function free_db_resource()
{
while(mysqli_next_result($this->conn_id))
{
if($l_result = mysqli_store_result($this->conn_id))
{
mysqli_free_result($l_result);
}
}
}
and use it after the procedure call
并在过程调用后使用它
$this->db->free_db_resource();
Thanks to wework4web
回答by user2182143
A simply way to call your stored procedure which has parameters is by using query() method provided by database library of Codeigniter.
调用具有参数的存储过程的一种简单方法是使用 Codeigniter 数据库库提供的 query() 方法。
In your model:-
在您的模型中：-
function call_procedure(){
$call_procedure ="CALL TestProcedure('$para1', '$para2', @para3)";
$this->db->query($call_procedure);
$call_total = 'SELECT @para3 as Parameter3';
$query = $this->db->query($call_total);
return $query->result();
}
回答by vijayabalan
public function callSp_model{
$sql = "CALL Materialwise_PURC_report(?,?,?)";
$query = $this->lite_db->query($sql,array($supplierid,$fromdate,$todate));
return $query->result_array(); }
