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);…

Accepted Answer

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

Calling stored procedure with Out parameter using PDO

Solution

Problem

Related problems