470,862 Members | 1,731 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,862 developers. It's quick & easy.

BIG PDO issues with StoredProc output params with PHP 5 and MySQL5

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_returns_string`$$
CREATE PROCEDURE `test`.`sp_returns_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_loaded('pdo_mysql')) {
// If not loaded we could try loading it manually
$prefix = (PHP_SHLIB_SUFFIX == 'dll') ? 'php_' : '';
if (!@dl($prefix . 'pdo_mysql.' . PHP_SHLIB_SUFFIX)) {
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=$DB_dbName";
$dbOptions = array();

//Create a DB connection
$db = new PDO($DB_Con, $DB_user, $DB_pass, $dbOptions);

$return_value = '';
$stmt = $db->prepare("CALL sp_returns_string(@?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000);
$stmt->execute();
print_r($stmt);
print "<br/>Returned: $return_value<br/><br/>\r\n";
?>
--------------

This should print something like this:
--------------
PDOStatement Object ( [queryString] => CALL sp_returns_string(@?) )
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("CALL sp_returns_string(?)");
$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.
Jan 10 '06 #1
2 2379
I have always used named parameters with PDO. I don't like having to rely on
the parameter order.

$stmt = $db->prepare( "SELECT foo FROM bar WHERE baz = :baz" );
$stmt->bindParam( ':baz', 123 );
$stmt->execute();

Did you try using PDO with a simple query first using your @? symbol? The
problem might not be with the stored procedure, but with the parameter
binding.

--
Louis-Philippe Huberdeau

James wrote:
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_returns_string`$$
CREATE PROCEDURE `test`.`sp_returns_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_loaded('pdo_mysql')) {
// If not loaded we could try loading it manually
$prefix = (PHP_SHLIB_SUFFIX == 'dll') ? 'php_' : '';
if (!@dl($prefix . 'pdo_mysql.' . PHP_SHLIB_SUFFIX)) {
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=$DB_dbName";
$dbOptions = array();

//Create a DB connection
$db = new PDO($DB_Con, $DB_user, $DB_pass, $dbOptions);

$return_value = '';
$stmt = $db->prepare("CALL sp_returns_string(@?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000);
$stmt->execute();
print_r($stmt);
print "<br/>Returned: $return_value<br/><br/>\r\n";
?>
--------------

This should print something like this:
--------------
PDOStatement Object ( [queryString] => CALL sp_returns_string(@?) )
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("CALL sp_returns_string(?)");
$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.


Jan 10 '06 #2
Hey,
Thanks for the feedback.

Unfortunately using param names doesn't work either.
All Database functions seem to work perfectly "except" fro output params.
All of the examples I've found on the net point to using "?" as the
parameter rather than "@?" (or "@:name" rather than ":name" for var names)
Unfortunately when I do this, I get :
SQLSTATE: HY000
ERROR CODE: 1414
ERROR: OUT or INOUT argument 1 for routine test.sp_returns_string is not a variable

Anyway, when I include the "@" it simply returns nothing...

I just can't seem to find any working examples anywhere...

Thanks,
James.

Louis-Philippe Huberdeau wrote: I have always used named parameters with PDO. I don't like having to rely on
the parameter order.

$stmt = $db->prepare( "SELECT foo FROM bar WHERE baz = :baz" );
$stmt->bindParam( ':baz', 123 );
$stmt->execute();

Did you try using PDO with a simple query first using your @? symbol? The
problem might not be with the stored procedure, but with the parameter
binding.

--
Louis-Philippe Huberdeau

Jan 11 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

12 posts views Thread by Sarah Tanembaum | last post: by
9 posts views Thread by PyPK | last post: by
4 posts views Thread by Clint Pidlubny | last post: by
4 posts views Thread by opswordfish | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.