Using pdo in php with stored procedure

I have a simple stored procedure in MySQL database:

DELIMITER $$
CREATE DEFINER=`vidhu`@`%` PROCEDURE `test`(var_datain TEXT)
BEGIN
    SELECT var_datain;
END

When calling this procedure in mysql-workbench it returns the data I put in:

Now when I call it from PHP using pdo I get an error:

Fatal error: Cannot pass parameter 2 by reference in C:/apache......(3rd line)

Here is my php code:

$db = new PDO(DSN, DBUSER, DBPASS);
$stmt = $db->prepare("CALL test(?)");
$stmt->bindParam(1, 'hai!', PDO::PARAM_STR);
$rs = $stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo $result[0];

Answer

You need to use bindValue instead of bindParam.

When you use bindParam, it binds the variable provided to the parameter, not the value of the variable.

So, if you do:

$x = 5;
$stmt->bindParam(1, $x, PDO::PARAM_INT);
$x = 6;
$stmt->execute(); //executes with 6 instead of 5

It’s actually executed with 6 rather than 5. To do this, the method must have a reference to the variable. You cannot have a reference to a literal, so this means that bindParam cannot be used with literals (or anything you can’t have a reference to).

$x = 5;
$stmt->bindValue(1, $x, PDO::PARAM_INT);
$x = 6;
$stmt->execute(); //executes with 5 instead of 6

Then:

$stmt->bindParam(1, 1, PDO::PARAM_INT); 
//invalid because there's no way to pass a literal 1 by reference
$stmt->bindValue(1, 1, PDO::PARAM_INT);
//valid