Calling stored procedure with Out parameter using PDO

Question

I've been using PDO for awhile now and am refactoring a project so that it uses stored procs instead of inline SQL. I am getting an error that I can't explain.I am using PHP version 5.3.5 and MySQL version 5.0.7.

I'm just trying to get a basic stored proc with an output to work. Here is the stored proc:

DELIMITER //  
CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100))  
BEGIN  
    SET var1 = 'This is a test';  
END //  

Here is the code I am using to call the proc, $db is an instance of PDO:

$stmt = $db->prepare("CALL proc_OUT(?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000); 

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

Simple right? However, it results in the following error:

exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1414 OUT or INOUT argument 1 for routine mydb.proc_OUT is not a variable or NEW pseudo-variable in BEFORE trigger

If I call the proc directly like so:

CALL proc_OUT(@res);
SELECT @res;

it works as expected which leads me to believe that there is a problem with how it is being called with PHP, however I can't seem to find what the issue is. I am following the instructions in the manual but am still getting this error. Could anyone suggest what I could be doing wrong? Any advice would be very much appreciated. Thanks much!

Solution 1

It would seem that there is a bug at work here, best solution I've found is this:

http://www.php.net/manual/en/pdo.prepared-statements.php#101993

From the comment at the link above:

$dbh->query("CALL SomeStoredProcedure($someInParameter1, $someInParameter2, @someOutParameter)"); 
$dbh->query("SELECT @someOutParameter");

// OR, if you want very much to use PDO.Prepare(),
// insert "SELECT @someOutParameter" in your stored procedure and then use:

$stmt = $dbh->prepare("CALL SomeStoredProcedure(?, ?)"); 
$stmt ->execute(array($someInParameter1, $someInParameter2));

See also this: https://stackoverflow.com/a/4502524/815386

Solution 2

Got it! Just add a

SELECT @outputparam;

at the end of the stored procedure, where @outputparam is the name used for the param in the stored procedure definition. If you cannot edit the stored procedure, you should do a second query, for SELECT @outputparam, with PHP PDO to get the output param value.

Tip: If you're using the deprecated DBLib to connect to SQL Server and you modified the stored procedure as suggested, you'll also need to tweak your syntax to get the output param value in the calling PHP script:

$out = 0;
$sth = $db->prepare("DECLARE @myout INT; EXECUTE mysp :firstparam, :secondparam, @myout OUTPUT;"); // the DECLARE trick is needed with DBLib
$sth->bindParam(':firstparam', $firstparam, PDO::PARAM_INT);
$sth->execute();
$sth->bindColumn(1, $out, PDO::PARAM_INT);
$sth->fetch(PDO::FETCH_BOUND);

var_dump($out); // works

Solution 3

You need to specify that your parameter is IN/OUT style like PHP web site example :

http://php.net/manual/en/pdo.prepared-statements.php example #5

<?php

$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'hello';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000); 

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

print "procedure returned $value\n";

Comments

[TheMethod]

Thanks for the response. My stored proc does not have an in/out parameter, it's just an out parameter like example #4 on the page you linked

[sdespont]

My mistake. Take a look here danstraw.com/… It could be a MySQL bug

[sdespont]

Seems fixed for MySQL version 5.5.3+ & 6.0.8+. Can you upgrade?

[Admin]

Isn't there a threat to SQL Injection here?

[Matteo Tassinari]

Possibly, the code is just an example, not to be used as is; the second example should be safe though.