CODEWITHSUNDEEP
phpmysqlstored-procedurespdo
Krimson
Krimson

Reputation: 7664

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:

Screenshot form mysql work bench

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];

Upvotes: 8

Views: 36022

Answers (3)

Evan
Evan

Reputation: 1

The bindParam function accepts only values variables, That's why parameter two hai which is not a variable cannot be passed. So it is not necessary the bindValue, but using the bindParam correctly. Example: when using bindParam: From your snippet.

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

This Should solve your problem and not the answer above.

Upvotes: -1

Armando Rodriguez Arguijo
Armando Rodriguez Arguijo

Reputation: 1

The following code works for calling without prepare statement!

$query="CALL store_procedure_name(@a)";
$conn->query($query);

$query="SELECT @a as outvar;";
$result = $conn->query($query);
foreach ($result as $x)
{
    $res=$x['outvar'];
}

echo $res;

Upvotes: -6

Corbin
Corbin

Reputation: 33437

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

Upvotes: 24

Related Questions