PHP and MySQL: How to Properly call a Stored Procedure with Output Parameters from PHP - mysql, mysqli, or PDO

[IamtheEvster]

Hi All,

I am currently using PHP 5 and MySQL 5, both on Fedora Core 5. I am
unable to call a MySQL stored procedure that returns output parameters
using mysql, mysqli, or PDO. I'm having a hell of a time with it...

The following comes from phpinfo():
PHP Version: 5.1.2
mysql Client API version: 5.0.18
mysqli Client API version: 5.0.18
PDO Driver for MySQL, client library version: 5.0.18

MySQL version, from running SELECT version(); = 5.0.18



Here's the procedure I've created in MySQL:
CREATE PROCEDURE testproc(OUT p_testvar varchar(50)) BEGIN SET
p_testvar = 'HELLO WORLD!'; END
[color=blue]
>From the MySQL Command Line, I can do the following:[/color]
mysql> call testproc(@a);
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+--------------+
| @a |
+--------------+
| HELLO WORLD! |
+--------------+
1 row in set (0.00 sec)



Now, on the PHP side, I'm using the code samples in the documentation
at:

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

under the heading "Example 9. Calling a stored procedure with an output
parameter"

<?
$user = "username";
$pass = "pAsSwOrD";

try {
$dbh = new PDO('mysql:host =localhost;dbna me=myDB', $user, $pass);

$stmt = $dbh->prepare("CAL L testproc(?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000);

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

print "procedure returned $return_value\n ";

$dbh = null;
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
?>


When I run this PHP file, it outputs nothing. No output whatsoever.
No error, no source code, nothing. Database username and password are
the same as those I'm using when I log in at the mysql command line.

I've tried using named parameters as well, with no success.


Just FYI, I can run the following from PHP:
<?
$user = "username";
$pass = "pAsSwOrD";

try {
$dbh = new PDO('mysql:host =localhost;dbna me=myDB', $user, $pass);

foreach ($dbh->query('SELEC T * from url_redirect') as $row) {
print_r($row);
}

$dbh = null;
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
?>

and I get the rows from the table.



I've also tried using the mysqli extension, rather than PDO. I'm using
the examples at
http://mysql.gilfster.com/page.php?p...&page_id=4.0.3 - using
the code sample under the section titled "Using Parameters", right
above the link to "php_proc_hello world.phx". My code is as follows:

<?php
$user = "username";
$pass = "pAsSwOrD";
$db = "databasena me";

$link = mysqli_connect( "localhost",$us er,$pass);

if (mysqli_connect _errno()) {
echo "connection error";
exit();
}

mysqli_select_d b ($link,$db);

if ($result = mysqli_multi_qu ery($link,"call testproc(@out_p aram)")) {

if ($result = mysqli_multi_qu ery($link,"sele ct @out_param")) {

$row = mysqli_fetch_ar ray($result, MYSQLI_NUM);

printf($row[0]);

mysqli_free_res ult($result);

} else { echo "problem :( inner "; }

} else { echo "problem :( outer"; }

?>


When I run this, I also get nothing. No errors, no output, nothing.
The browser completes the load of the page, but it has no output on it.


Any thoughts or advice is greatly appreciated. I'm willing to try
almost anything at this point - I'm totally stumped.

Thanks,

Evan