PHP bindParam does not seem to work with a PARAM_INT out parameter

Question

The following is my MySQL stored procedure:

DROP PROCEDURE IF EXISTS sp_authenticate;
CREATE PROCEDURE sp_authenticate(IN user_name VARCHAR(50),
  IN password1 VARCHAR(50), OUT nmatch INT)
BEGIN

  SELECT COUNT(*) INTO nmatch
    FROM user1 u
    WHERE u.name = user_name AND
      u.password1 = password1;

END//

This is how I am calling it from PHP:

function authenticate($pdo, $user_name, $password){
  $stmt = $pdo->prepare('CALL sp_authenticate(:user_name, :password, :nmatch)');
  $stmt->bindValue(':user_name', $user_name, PDO::PARAM_STR);
  $stmt->bindValue(':password', $password, PDO::PARAM_STR);
  $nmatch = 888888;
  $stmt->bindParam(':nmatch', $nmatch, PDO::PARAM_INT, 4);
  $result = $stmt->execute();

  return $nmatch;
}

$nmatch always retains it's old value and does not receive the value from the stored procedure. What could I be doing wrong here?

MySQL Server Version: 5.5.22 PHP Version: 5.3.10

Answer 1

As mentioned in this blog:

Unfortunately there’s a bug with the MySQL C API that PDO uses which means that trying to fetch an output parameter when calling a procedure results in the error:

“Syntax error or access violation: 1414 OUT or INOUT argument $parameter_number for routine $procedure_name is not a variable or NEW pseudo-variable”.

You can see the bug report on bugs.mysql.com. It’s been fixed for version 5.5.3+ & 6.0.8+.

To workaround the issue, you would need to keep in & out parameters separate and call the procedure. Example #11 on the PHP PDO documentation would then read:

$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(:in_string, @out_string)");
$stmt->bindParam(':in_string', 'hello');

// call the stored procedure
$stmt->execute();

// fetch the output
$outputArray = $this->dbh->query("select @out_string")->fetch(PDO::FETCH_ASSOC);

print "procedure returned " . $outputArray['@out_string'] . "\n";