How to get stored procedure return values via PDO?

Hi,

I'm calling a MySQL Stored Procedure via PDO (PHP 5.2.3)

$stmt = $dbh->prepare('CALL forum_post(?, ?, ?, ?, ?, @status, @thread_id, @message_id)');

At the moment in order to get the values of status, thread_id and
message_id I need to issue a second query:

$sql = "SELECT @status AS status, @thread_id AS thread_id, @message_id AS message_id";

and then foreach my way through it:

foreach ($dbh->query($sql) as $row)
{
$status = $row['status'];
$thread_id = $row['thread_id'];
$message_id = $row['message_id'];
}

Which seems a bit insane.. is there no way to do a bindValue at the
same time as I do my bindParams?

Now I write this I really can't remember why I am even putting that
second query into a foreach loop, even so it's still a step I'd like
to remove entirely if possible?

Cheers,

Rich
--
Zend Certified Engineer
http://www.corephp.co.uk

"Never trust a computer you can't throw out of a window"

Richard Davey wrote:
> Hi,
>
> I'm calling a MySQL Stored Procedure via PDO (PHP 5.2.3)
>
> $stmt = $dbh->prepare('CALL forum_post(?, ?, ?, ?, ?, @status, @thread_id, @message_id)');
>
> At the moment in order to get the values of status, thread_id and
> message_id I need to issue a second query:
>
> $sql = "SELECT @status AS status, @thread_id AS thread_id, @message_id AS message_id";
>
> and then foreach my way through it:
>
> foreach ($dbh->query($sql) as $row)
> {
> $status = $row['status'];
> $thread_id = $row['thread_id'];
> $message_id = $row['message_id'];
> }
>
> Which seems a bit insane.. is there no way to do a bindValue at the
> same time as I do my bindParams?
>
> Now I write this I really can't remember why I am even putting that
> second query into a foreach loop, even so it's still a step I'd like
> to remove entirely if possible?
>
> Cheers,
>
> Rich

From: http://www.php.net/manual/en/ref.pdo.php

Example 1716. Calling a stored procedure with an input/output parameter
<?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";
?>

Hi M.,

Thursday, July 26, 2007, 2:09:47 PM, you wrote:

> Richard Davey wrote:
>> Hi,
>>
>> I'm calling a MySQL Stored Procedure via PDO (PHP 5.2.3)
>>
>> $stmt = $dbh->prepare('CALL forum_post(?, ?, ?, ?, ?, @status, @thread_id, @message_id)');
>>
>> At the moment in order to get the values of status, thread_id and
>> message_id I need to issue a second query:
>>
>> $sql = "SELECT @status AS status, @thread_id AS thread_id, @message_id AS message_id";
>>
>> and then foreach my way through it:
>>
>> foreach ($dbh->query($sql) as $row)
>> {
>> $status = $row['status'];
>> $thread_id = $row['thread_id'];
>> $message_id = $row['message_id'];
>> }
>>
>> Which seems a bit insane.. is there no way to do a bindValue at the
>> same time as I do my bindParams?
>>
>> Now I write this I really can't remember why I am even putting that
>> second query into a foreach loop, even so it's still a step I'd like
>> to remove entirely if possible?
>>
>> Cheers,
>>
>> Rich

> From: http://www.php.net/manual/en/ref.pdo.php

> Example 1716. Calling a stored procedure with an input/output parameter

It's not an INOUT parameter though.

Cheers,

Rich
--
Zend Certified Engineer
http://www.corephp.co.uk

"Never trust a computer you can't throw out of a window"