Using pdo in php with stored procedure

Question

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

the line which has $stmt->bindParam(1, 'hai!', PDO::PARAM_STR); — Krimson
– Krimson, Commented Aug 7, 2012 at 0:49

Corbin · Accepted Answer · 2012-08-07 00:57:14Z

24

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

edited Aug 7, 2012 at 0:57

answered Aug 7, 2012 at 0:52

Corbin

33.6k7 gold badges73 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Evan · Accepted Answer · 2020-12-27 12:46:04Z

-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.

edited Dec 27, 2020 at 12:46

answered Dec 22, 2020 at 6:32

Evan

12 bronze badges

Comments

AbcAeffchen · Accepted Answer · 2015-01-22 20:31:03Z

-6

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;

edited Jan 22, 2015 at 20:31

AbcAeffchen

15.1k15 gold badges52 silver badges67 bronze badges

answered Jan 22, 2015 at 20:11

Armando Rodriguez Arguijo

11 bronze badge

Collectives™ on Stack Overflow

Using pdo in php with stored procedure

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related