|
P: 1
| My environment
php 5.3.0
MySQL Server version: 5.1.37
MySQL client version: 5.1.37
xampp for windows 1.7.2
Windows 7 The Error message I get is this:
Array ( [0] => HY000 [1] => 1414 [2] => OUT or INOUT argument 3 for routine cmcs.spLogin is not a variable or NEW pseudo-variable in BEFORE trigger ) The php code looks like this: - $pdo=new PDO("mysql:host=localhost;dbname=mydb;","dbuser","secret");
-
//$query=$pdo->prepare("call spLogin(:USERNAME,:PASSWORD, @ROLLTYPEID, @RollType, @LastLogin, @LoginCount, @CreateDate, @UpdateDate, @isActive )");
-
//$query=$pdo->prepare("call spLogin(?,?, ?, @RollType, @LastLogin, @LoginCount, @CreateDate, @UpdateDate, @isActive )");
-
$query=$pdo->prepare("call spLogin(?,?,?,?,?,?,?,?,?)");
-
//$query->bindParam(':USERNAME',$this->UserName);
-
//$query->bindParam(':PASSWORD',$this->Password);
-
//$query->bindParam(':ROLLTYPEID',$value, PDO::PARAM_INT);
-
$query->bindParam(1,$this->UserName);
-
$query->bindParam(2,$this->Password);
-
$query->bindParam(3,$value, PDO::PARAM_INT);
-
//$query->bindParam(3,$value, PDO::PARAM_INT, 11);
-
$query->bindParam(4,$this->RollType, PDO::PARAM_STR, 50);
-
$query->bindParam(5,$this->LastLogin, PDO::PARAM_STR, 50);
-
$query->bindParam(6,$this->LoginCount, PDO::PARAM_INT);
-
$query->bindParam(7,$this->CreateDate, PDO::PARAM_STR, 50);
-
$query->bindParam(8,$this->UpdateDate, PDO::PARAM_STR, 50);
-
$query->bindParam(9,$this->IsActive, PDO::PARAM_INT);
-
-
$result = $query->execute();
-
if(!$result) {
-
$errArray=$query->errorInfo();
-
print_r($errArray);
-
} else {
-
$this->Status = "Testing: ".$this-RollType;
-
}
The stored procedure looks like this: - DELIMITER $$
-
-
DROP PROCEDURE IF EXISTS `spLogin` $$
-
CREATE DEFINER=`root`@`localhost` PROCEDURE `spLogin`( IN _UserName VARCHAR(50),
-
IN _Password VARCHAR(50),
-
OUT _rollTypeId INT,
-
INOUT _rollType VARCHAR(50),
-
INOUT _lastLogin DATETIME,
-
INOUT _loginCount INT,
-
INOUT _createDate DATETIME,
-
INOUT _updateDate DATETIME,
-
INOUT _isActive BOOL )
-
BEGIN
-
DECLARE _userId int;
-
-
SELECT UserId,
-
`User`.RollTypeId,
-
`RollType`.RollType,
-
LastLogin,
-
LoginCount,
-
CreateDate,
-
UpdateDate,
-
IsActive
-
FROM `User` JOIN `RollType` ON User.RollTypeId = RollType.RollTypeId
-
WHERE UserName = _UserName
-
AND `Password` = SHA1(_Password)
-
INTO _userId,
-
_rollTypeId,
-
_rollType,
-
_lastLogin,
-
_loginCount,
-
_createDate,
-
_updateDate,
-
_isActive;
-
-
IF _isActive = TRUE THEN
-
SET _loginCount = _loginCount + 1;
-
SET _lastLogin = NOW();
-
END IF;
-
-
UPDATE `user`
-
SET LoginCount = _loginCount,
-
LastLogin = _lastLogin
-
WHERE UserID = _userId
-
AND IsActive = TRUE;
-
-
END $$
-
-
DELIMITER ;
Thanks for your help!
clint
| |
