分享程序员开发的那些事...
更新时间：2023-01-30 15:57:41
事实证明，这是一个从 2005 年开始就已经存在了很长时间的错误！
这是原始错误报告:2005 年到 2013 年.这是新的错误报告:从 2013 年到现在.>
有多种方法可以返回答案，我找到了其中一种并进行了演示......
技巧"是从mysql"过程中获取输出.这是一个两个阶段"的过程.
第一部分是使用您的输入运行该过程，并告诉它存储结果的 MYSQL 变量.
然后，您运行一个单独的查询来选择"那些mysql"变量.
这里描述的很清楚:php-calling-mysql-stored-程序
更新(2017 年 1 月):
这是一个示例，显示了IN"、INOUT"和OUT"Mysql 过程参数的变量的使用.
在我们开始之前，这里有一些提示:
当您尝试将变量绑定到 INOUT 和 OUT 参数时，您会遇到一些非常奇怪的运行时错误.
像往常一样，我倾向于提供比要求更多的评论;-/
运行时环境 (XAMPP):
源代码:
CREATE PROCEDURE `demoSpInOutSqlVars`(IN pInput_Param INT,/* PHP 变量将绑定到这个*//* --- */INOUT pInOut_Param INT,/* 包含将由 mysql 读取和设置的 SQL 用户变量的名称 */OUT pOut_Param INT)/* 包含将由 mysql 设置的 SQL 用户变量的名称 */开始/** 为这些参数传递 SQL 用户变量的全名.例如'@varInOutParam'* 这些SQL 用户变量名称"是 Mysql 将用于的变量:* 1) 寻找值* 2) 存储结果** 它类似于 PHP 中的变量变量".*/设置 pInOut_Param := ABS(pInput_Param) + ABS(pInOut_Param);/* 总是正和 */设置 pOut_Param := ABS(pInput_Param) * -3;/* 总是负数 * 3 */完$$数据库连接:
$db = appDIC('getDbConnection', 'default');//获取默认的数据库连接$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);注意:输出与 EMULATE_PREPARES = false 相同.
设置将要使用的所有 PHP 变量:
$phpInParam = 5;$phpInOutParam = 404;/* PHP 输入输出变量 ==>阅读并应更改 */$phpOutParam = null;/* PHP 输出变量 ==>应该改变 */定义和准备 SQL 过程调用:
$sql = "调用 demoSpInOut(:phpInParam,@varInOutParam,/* mysql 变量名将被读取和更新 */@varOutParam)";/* 将写入的 mysql 变量名 */$stmt = $db->prepare($sql);绑定 PHP 变量并设置 SQL 变量:
1) 绑定 PHP 变量
$stmt->bindParam(':phpInParam', $phpInParam, PDO::PARAM_INT);
2) 设置 SQL 用户 INOUT 变量
$db->exec("SET @varInOutParam = $phpInOutParam");//这是安全的，因为它只是将值设置到 MySql 变量中.
执行程序:
$allOk = $stmt->execute();获取 SQL 变量到 PHP 变量中:
$sql = "SELECT @varInOutParam AS phpInOutParam,@varOutParam 作为 phpOutParam从双";$results = current($db->query($sql)->fetchAll());$phpInOutParam = $results['phpInOutParam'];$phpOutParam = $results['phpOutParam'];注意:也许不是***的方法;-/
显示 PHP 变量
"$phpInParam:" =>5""$phpInOutParam:" =>409""$phpOutParam:" =>-15"I've seen this question asked a load of times, but they're all really long, and I just can't get my head around what they're doing ... So, could someone tell me how to get the LAST_INSERT_ID() from this procedure into php using PDO:
CREATE TABLE names (
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(50) NOT NULL
)
CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleProcedure`(newname varchar(50), OUT returnid INT(11))
BEGIN
INSERT INTO names (name) VALUES (newname);
SET returnid = LAST_INSERT_ID();
END
$stmt=$db->prepare("CALL simpleProcedure(:name,:returnid)");
$stmt->bindValue(':name',$name,PDO::PARAM_STR);
$stmt->bindParam(':returnid',$returnid,PDO::PARAM_INT,11);
$stmt->execute();
echo $returnid;
But, probably obvious to someone who has more brain cells than me, this doesn't work. Any help appreciated.
http://www.php.net/pdo.prepared-statements (Example #4)
It turns out that this is a bug that has been going on for a long time... since 2005!
Here is the original bug report: 2005 through to 2013. And here is the new bug report: From 2013 to the present.
There are various approaches to getting the answer returned, I found one of them and demonstrate it...
The 'trick' is that to get the output from a 'mysql' procedure. It is a 'two stage' process.
The first part is to run the procedure with your inputs, and also tell it what MYSQL variables to store the result in.
Then, you run a separate query to 'select' those 'mysql' variables.
It is described quite clearly here: php-calling-mysql-stored-procedures
Update (Jan 2017):
Here is an example showing the use of variables for 'IN', 'INOUT' and 'OUT' Mysql procedure parameters.
Before we start here are some tips:
You will get some really odd runtime errors when you try binding variables to INOUT and OUT parameters.
As usual I tend to provide rather more comments than are required ;-/
Runtime Environment (XAMPP):
Source Code:
CREATE PROCEDURE `demoSpInOutSqlVars`(IN pInput_Param INT, /* PHP Variable will bind to this*/
/* --- */
INOUT pInOut_Param INT, /* contains name of the SQL User variable that will be read and set by mysql */
OUT pOut_Param INT) /* contains name of the SQL User variable that will be set by mysql */
BEGIN
/*
* Pass the full names of SQL User Variable for these parameters. e.g. '@varInOutParam'
* These 'SQL user variables names' are the variables that Mysql will use for:
* 1) finding values
* 2) storing results
*
* It is similar to 'variable variables' in PHP.
*/
SET pInOut_Param := ABS(pInput_Param) + ABS(pInOut_Param); /* always positive sum */
SET pOut_Param := ABS(pInput_Param) * -3; /* always negative * 3 */
END$$
DB Connection:
$db = appDIC('getDbConnection', 'default'); // get the default db connection
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
Note: The output is the same with EMULATE_PREPARES = false.
Set all PHP Variables that will be used:
$phpInParam = 5;
$phpInOutParam = 404; /* PHP InOut variable ==> read and should be changed */
$phpOutParam = null; /* PHP Out variable ==> should be changed */
Define and Prepare the SQL procedure call:
$sql = "call demoSpInOut(:phpInParam,
@varInOutParam, /* mysql variable name will be read and updated */
@varOutParam)"; /* mysql variable name that will be written to */
$stmt = $db->prepare($sql);
Bind PHP Variables and Set SQL Variables:
1) bind the PHP variables
$stmt->bindParam(':phpInParam', $phpInParam, PDO::PARAM_INT);
2) Set the SQL User INOUT variables
$db->exec("SET @varInOutParam = $phpInOutParam"); // This is safe as it just sets the value into the MySql variable.
Execute the procedure:
$allOk = $stmt->execute();
Get the SQL Variables into the PHP variables:
$sql = "SELECT @varInOutParam AS phpInOutParam,
@varOutParam AS phpOutParam
FROM dual";
$results = current($db->query($sql)->fetchAll());
$phpInOutParam = $results['phpInOutParam'];
$phpOutParam = $results['phpOutParam'];
Note: maybe not the best way ;-/
Display the PHP variables
"$phpInParam:" => "5"
"$phpInOutParam:" => "409"
"$phpOutParam:" => "-15"