Posted by IamtheEvster on 04/10/06 18:18

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

>From the MySQL Command Line, I can do the following:
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;dbname=myDB', $user, $pass);

$stmt = $dbh->prepare("CALL 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;dbname=myDB', $user, $pass);

foreach ($dbh->query('SELECT * 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?parent_id=4&page_id=4.0.3 - using
the code sample under the section titled "Using Parameters", right
above the link to "php_proc_helloworld.phx". My code is as follows:

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

$link = mysqli_connect("localhost",$user,$pass);

if (mysqli_connect_errno()) {
echo "connection error";
exit();
}

mysqli_select_db ($link,$db);

if ($result = mysqli_multi_query($link,"call testproc(@out_param)")) {

if ($result = mysqli_multi_query($link,"select @out_param")) {

$row = mysqli_fetch_array($result, MYSQLI_NUM);

printf($row[0]);

mysqli_free_result($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

[Back to original message]