CODEWITHSUNDEEP
phpmysqlstored-procedurespdoprepared-statement
NaN
NaN

Reputation: 1306

PDO and MySQL stored procedure. Not returning out param

When using PDO, I should be able to select the param and echo it. All I get however is NULL. When I use Workbench, I can see it just fine.

Can anyone tell me why this is so?

CREATE PROCEDURE testing(OUT ret int)
BEGIN SET ret=12; END;

// On workbench returns '12' - Correct       
call testing(@ret);
select @ret;

// PHP/PDO returns NULL
$stmt=Db()->prepare("CALL testing(@ret)");
$stmt->execute();
$param = Db()->query("SELECT @ret")->fetch(PDO::FETCH_ASSOC);
var_dump($param);

EDIT: I was just about convinced that this may have been a specific issue with Windows so I uploaded this example to my UNIX server and get exactly the same result, NULL.

Upvotes: 5

Views: 3162

Answers (3)

rizon
rizon

Reputation: 8197

I see a bug here http://bugs.mysql.com/bug.php?id=11638.

try this

   insert "SELECT @someOutParameter" in your stored procedure and then use: 

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

Upvotes: 1

rizon
rizon

Reputation: 8197

Try this

$stmt=Dbh->prepare("CALL testing(@ret)");
$stmt->execute();
$param = $stmt->fetchAll();
var_dump($param);

Upvotes: 0

Raffaele
Raffaele

Reputation: 20875

It seems you miss the call to bindParam(). From the PHP doc examples:

<?php
/* Call a stored procedure with an INOUT parameter */
$colour = 'red';
$sth = $dbh->prepare('CALL puree_fruit(?)');
$sth->bindParam(1, $colour, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 12);
$sth->execute();
print("After pureeing fruit, the colour is: $colour");
?>

UPDATE

I missed the MySQL part. MySQL doesn't support binding output parameters via its C API. In the bug report it's said you must use SQL level variables:

$stmt = $db->prepare("CALL sp_returns_string(@a)");
$stmt->execute();
print_r($db->query("SELECT @a")->fetchAll());

but this is exactly what you do, and it doesn't work. I'm trying this myself right now.

Upvotes: 1

Related Questions