Edit report at https://bugs.php.net/bug.php?id=35935&edit=1
ID: 35935
Comment by: jmpalacios at gmail dot com
Reported by: victoria at balic dot net
Summary: MySQL stored procedure doesn't return value when
using bindParam()
Status: Not a bug
Type: Bug
Package: PDO related
Operating System: RHEL4-64/CentOS 4.2-64
PHP Version: 5.1.1
Assigned To: wez
Block user comment: N
Private report: N
New Comment:
I'm using PHP 5.4.14 and MySQL 5.5.30 on Mac OS X 10.8 and I'm also seeing this
exact same problem.
There's bug report for the MySQL side of the issue here
http://bugs.mysql.com/bug.php?id=11638. Even though it's closed, because it was
reported as being fixed in MySQL 5.5.3, some of us demonstrated it's still an
issue even as of MySQL 5.6.11. So I'm hoping it'll get re-opened at some point
in the future; if not, I'll look into opening a new bug report for the problem.
In any case, apparently MySQL is interested in making the binding of output
parameters via its C API a possibility, so when they do it'd be great to have
the PHP side of things fixed up to finally make it a possibility.
Thanks!
Previous Comments:
------------------------------------------------------------------------
[2006-04-09 07:47:37] w...@php.net
MySQL doesn't supporting binding output parameters via its C API. You must use
SQL level variables:
$stmt = $db->prepare("CALL sp_returns_string(@a)");
$stmt->execute();
print_r($db->query("SELECT @a")->fetchAll());
------------------------------------------------------------------------
[2006-03-09 19:55:22] keyvez at hotmail dot com
I am experiencing the same issue on Windows and MSSQL.
Stored Procedure Create Code:
******************************************
CREATE PROCEDURE [dbo].[p_sel_all_termlength]
@err INT = 0 OUTPUT AS
SELECT * FROM termlength
SET @err = 2627
******************************************
PHP Code:
******************************************
$Link = new PDO('mssql:host=sqlserver;dbname=database', 'username', 'password');
$ErrorCode = 0;
$Stmt = $Link->prepare('p_sel_all_termlength ?');
$Stmt->bindParam(1,$ErrorCode,PDO::PARAM_INT,4);
$Stmt->execute();
echo "Error = " . $ErrorCode . "\n";
while ($Row = $Stmt->fetch(PDO::FETCH_OBJ)) {
echo $Row->description . "\n";
}
echo "Error = " . $ErrorCode . "\n";
******************************************
PHP Output:
******************************************
Error = 0
9 Weeks
Semester
One Year
Trimester
Error = 0
******************************************
------------------------------------------------------------------------
[2006-01-09 20:34:14] victoria at balic dot net
I just used the latest snapshot as instructed (5.1.2RC3-dev 20060109) and I get
the same behaviour as reported above -- ie. the PDO call returns empty value
when using bindParam().
------------------------------------------------------------------------
[2006-01-09 10:19:30] sni...@php.net
Please try using this CVS snapshot:
http://snaps.php.net/php5.1-latest.tar.gz
For Windows:
http://snaps.php.net/win32/php5.1-win32-latest.zip
------------------------------------------------------------------------
[2006-01-09 00:26:08] victoria at balic dot net
Description:
------------
I created a trivial stored procedure in MySQL (v5.0.17) that simply returns an
integer constant. If I call the procedure using a PDO statement and binding a
return value $stmt->bindParam(), I get nothing.
On the other hand, if I issue two SQL queries to implicitly obtain the result,
things are ok (see below). So something is broken with the way PDO handles
bindParam.
I am using the latest stable release of PHP (5.1.1), MySQL 5.0.17 and mod_php
for Apache 2.2
Reproduce code:
---------------
<?php
$DB = new PDO(...);
if($DB != NULL) {
$stmt = $DB->prepare(" CALL test_pdo(?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_INT, 10);
$stmt->execute();
print "Procedure returned: $return_value \n";
//try instead plain SQL call
$DB->query("CALL test_pdo(@nn)");
$rows = $DB->query("SELECT @nn")->fetchAll();
print "SELECT returned: \n";
print_r($rows);
}
?>
And here's actual MySQL Stored procedure:
CREATE PROCEDURE test_pdo
(OUT Pout INTEGER)
BEGIN
SET Pout := 1912;
END
Expected result:
----------------
$return_value should have been set to "1912" and instead it's empty. The stored
procedure is working ok as the second call (in which i implicitly obtain the
output by making two SQL queries) returns the correct value (see below).
Actual result:
--------------
Procedure returned:
SELECT returned:
Array ( [0] => Array ( [@nn] => 1912 [0] => 1912 ) )
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=35935&edit=1
