How can I get OUT parameters from a call to a stored procedure in MySQL using php?

[clintolin]

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

[JamieHowarth0]

Hi Clint,

This post on the MySQL forums shows how to use inline parameters to execute a store procedure and return the results in the format:

CALL myproc (@param1, @param2); SELECT @param1, @param2;
 

This should then return a one-row resultset with the parameters successfully modified by your stored procedure.
If this doesn't work let us know and we'll try and find another solution.

Hope it helps,

codegecko

[JamieHowarth0]

Hi Clint,

Did this work for you? If so, if you could mark the individual post with "choose as best answer" (or I can do it for you) that'd be great, it helps other people to find the solution quicker for the given problem. If not, let us know and we'll try and find another solution.

Thanks,

codegecko