Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 551
NaN Avatar asked Nov 03 '22 07:11

NaN


1 Answers

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.

like image 164
Raffaele Avatar answered Nov 09 '22 14:11

Raffaele