- 里论外几
-
我在SQLSRV上建好了存储过程,单步调试能取得OUTPUT的值,但是PHP页面上尝试取得该值时,总是报错,在网上查了很多资料,都没办法解决?不知道是不是我的代码有问题,麻烦大神帮忙看看,以下为具体说明。
存储过程的OUTPUT值为 @flag INT,成功为1,失败为0,单步调试是有值的,我感觉应该不是SQL部分的问题。这里的类型也试过BIT、TINYINT、VARCHAR,一样的在SQL部分都有值,但PHP页面好像都没取到值。
try {
$dbh = new PDO($dsn, $username, $password);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); try {
$dbh->beginTransaction();
$sql = "{CALL UploadMasterData(:model, :workplace, :item, :itemCategory, :itemNumber, :filename, :flag)}";
$statement = $dbh->prepare($sql);
$statement->bindParam(":model", $model, PDO::PARAM_STR);
$statement->bindParam(":workplace", $workplaceNumber, PDO::PARAM_INT);
$statement->bindParam(":item", $item, PDO::PARAM_STR);
$statement->bindParam(":itemCategory", $itemCategory, PDO::PARAM_STR);
$statement->bindParam(":itemNumber", $itemNumber, PDO::PARAM_INT);
$statement->bindParam(":filename", $filename, PDO::PARAM_STR);
$statement->bindParam(":flag", $insertFlag, PDO::PARAM_INT, 11);
$statement->execute(); /*if (!$insertFlag) {
break;
}*/
if ($insertFlag) { echo "更新成功<br>"; echo "|" . $insertFlag . "|";
$dbh->commit();
} else { echo "更新失败<br>"; echo "|" . $insertFlag . "|";
$dbh->rollBack();
}
}catch (PDOException $exception) { echo "An error occurred while operating the database and started to roll back. PDOException info:<br>" . $exception->getMessage() . "<br>";
$dbh->rollBack();
}
}catch (PDOException $exception) { echo "Failed to establish database connection. PDOException info:<br>" . $exception->getMessage();
}
这里$insertFlag的值好像没有接收成功,有尝试过修改:flag为@flag好像也不行,尝试很多方法,不是报错就是0,不知道是不是我接收的写的有问题,代码有删减,说的比较乱,如果有不明白的麻烦追问,请大神解答,谢谢。
An error occurred while operating the database and started to roll back. PDOException info:
SQLSTATE[IMSSP]: Types for parameter value and PDO::PARAM_* constant must be compatible for input/output parameter 7.
Array
(
[0] => IMSSP
[1] => -55
[2] => Types for parameter value and PDO::PARAM_* constant must be compatible for input/output parameter 7.
)
IMSSP193#0 C:Apache24htdocsSOPpagesdo.php(193): PDOStatement->execute()
#1 {main}
以上是出现过的错误信息。
大概整理下要点,如下。
$dbh->beginTransaction();$sql = "{CALL UploadMasterData(:model, :workplace, :item, :itemCategory, :itemNumber, :filename, :flag)}";$statement = $dbh->prepare($sql);$statement->bindParam(":model", $model, PDO::PARAM_STR);$statement->bindParam(":workplace", $workplaceNumber, PDO::PARAM_INT);$statement->bindParam(":item", $item, PDO::PARAM_STR);$statement->bindParam(":itemCategory", $itemCategory, PDO::PARAM_STR);$statement->bindParam(":itemNumber", $itemNumber, PDO::PARAM_INT);$statement->bindParam(":filename", $filename, PDO::PARAM_STR);$statement->bindParam(":flag", $insertFlag, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT, 2048);$statement->execute();
这里的$insertFlag我老是取不到值,看到一个微软官方的示例,安照一样的方法好像就是不行,是我的理解错了吗?[以下为微软官方示例]
<?php
$database = "AdventureWorks";
$server = "(local)";
$dbh = new PDO("sqlsrv:server=$server ; Database = $database", "", "");
$dbh->query("IF OBJECT_ID("dbo.sp_ReverseString", "P") IS NOT NULL DROP PROCEDURE dbo.sp_ReverseString");
$dbh->query("CREATE PROCEDURE dbo.sp_ReverseString @String as VARCHAR(2048) OUTPUT as SELECT @String = REVERSE(@String)");
$stmt = $dbh->prepare("EXEC dbo.sp_ReverseString ?");
$string = "123456789";
$stmt->bindParam(1, $string, PDO::PARAM_STR | PDO::PARAM_INPUT_OUTPUT, 2048);
$stmt->execute();
print $string; // Expect 987654321 ?>
而如果我把:flag改为@flag就会提示参数未定义。。。蛋疼