1

What is wrong in this code? 

$statement = $dbConn->prepare("CALL SearchUser(?)");
$statement->bindParam(1, $username, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);

This is the procedure: 

CREATE PROCEDURE SearchUser(IN Username VARCHAR(10), OUT numRows INT) 
BEGIN SELECT COUNT(*) INTO numRows 
FROM USER
WHERE Username='IN'; 
END//

The error is: Incorrect number of arguments for PROCEDURE, expected 2 got 1. Why? Thank you.

JamieITGirl
  • 161
  • 1
  • 11
  • 2
    Where do you supply an argument for `numRows`? – David Sep 23 '17 at 17:01
  • I don't know what you mean; numRows must be the output parameter of the procedure. Isn't that so? Thanks. – JamieITGirl Sep 23 '17 at 17:03
  • Possible Duplicate of https://stackoverflow.com/questions/13382922/calling-stored-procedure-with-out-parameter-using-pdo or maybe https://stackoverflow.com/questions/118506/stored-procedures-mysql-and-php/4502524#4502524 ether way read both answers – RiggsFolly Sep 23 '17 at 17:14

1 Answers1

1

You need to pass two parameter

$statement = $dbConn->prepare("CALL SearchUser(?,?)");
$statement->bindParam(1, $username, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);
$statement->bindParam(2, $rowcount, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT);

You could refer this article

unnamed
  • 74
  • 10
  • But rowcount is the output parameter of the procedure, why do I have to pass it? Anyway I tried with this result: 1414 OUT or INOUT argument 2 for routine SearchUser is not a variable or NEW pseudo-variable in BEFORE trigger – JamieITGirl Sep 23 '17 at 17:39
  • when you are getting output, you need to hold it somewhere. right ? – unnamed Sep 23 '17 at 17:43
  • @JamieITGirl What you are referring, is seems to be a bug. Please refer this answer https://stackoverflow.com/questions/10428480/php-bindparam-does-not-seem-to-work-with-a-param-int-out-parameter – unnamed Sep 23 '17 at 17:52
  • @JamieITGirl I was not sure about your complete code otherwise I would have updated the answer – unnamed Sep 23 '17 at 17:53
  • But I have mysql version 5.6.35! I quote: "It’s been fixed for version 5.5.3+ & 6.0.8+." – JamieITGirl Sep 23 '17 at 17:55
  • @JamieITGirl try the approach mentioned in that answer ? – unnamed Sep 23 '17 at 17:56
  • The example it's about a procedure which takes a string and returns a string. My procedure takes a string and returns an int. I tried this but doesn't work (fatal error: Using $this when not in object context). statement = $dbConn->prepare("CALL SearchUser(:username, @numrows)"); $statement->bindParam(':username', $numRows); $statement->execute(); $numRows = $this->dbConn->query("select @numrows")->fetch(PDO::FETCH_ASSOC); – JamieITGirl Sep 23 '17 at 18:08