Hey everyone...
I'm having an issue with a seemingly simple piece of PHP/MySQL
I have a stored procedure in MySQL5 as such:
SQL:
--------------
DELIMITER $$;
DROP PROCEDURE IF EXISTS `test`.`sp_retu rns_string`$$
CREATE PROCEDURE `test`.`sp_retu rns_string`(OUT vOutput varchar(32))
BEGIN
SET vOutput = 'It Worked';
END$$
DELIMITER ;$$
--------------
And a piece of PHP that uses PDO to call the Stored Proc as such:
PHP:
--------------
<?php
//First - check/load PDO!
if (!extension_loa ded('pdo_mysql' )) {
// If not loaded we could try loading it manually
$prefix = (PHP_SHLIB_SUFF IX == 'dll') ? 'php_' : '';
if (!@dl($prefix . 'pdo_mysql.' . PHP_SHLIB_SUFFI X)) {
die('pdo_mysql unavailable');
}
}
$DB_host = "localhost" ; // the hostname of the database server
$DB_user = "root"; // YOUR username to connect with
$DB_pass = "password"; // YOUR user's password
$DB_dbName = "test"; // the name of the database to connect to
$DB_dbType = "mysqli"; // the type of database server.
$DB_Con = "mysql:host=$DB _host;dbname=$D B_dbName";
$dbOptions = array();
//Create a DB connection
$db = new PDO($DB_Con, $DB_user, $DB_pass, $dbOptions);
$return_value = '';
$stmt = $db->prepare("CAL L sp_returns_stri ng(@?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000);
$stmt->execute();
print_r($stmt);
print "<br/>Returned: $return_value<b r/><br/>\r\n";
?>
--------------
This should print something like this:
--------------
PDOStatement Object ( [queryString] => CALL sp_returns_stri ng(@?) )
Returned: It Worked!
--------------
But unfortunately it NEVER returns anything.
The Stored Proc works fine and returns the string ok, but only when I'm
calling it from an SQL console.
The PHP PDO documentation says to use:
--------------
$return_value = '';
$stmt = $db->prepare("CAL L sp_returns_stri ng(?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000);
--------------
Note the missing "@".
But when I do this, it complains that the parameter isn't a variable and
the call dies.
(see: http://www.php.net/pdo/ for the examples)
Any help would be great!
Thanks.
James.