php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #35935 MySQL stored procedure doesn't return value when using bindParam()
Submitted: 2006-01-09 00:26 UTC Modified: 2006-04-09 07:47 UTC
Votes:1
Avg. Score:5.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: victoria at balic dot net Assigned: wez (profile)
Status: Not a bug Package: PDO related
PHP Version: 5.1.1 OS: RHEL4-64/CentOS 4.2-64
Private report: No CVE-ID: None
 [2006-01-09 00:26 UTC] 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 ) )

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-01-09 10:19 UTC] sniper@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 20:34 UTC] 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-03-09 19:55 UTC] 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-04-09 07:47 UTC] wez@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());

 [2013-05-09 03:15 UTC] jmpalacios at gmail dot com
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!
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sat Dec 21 14:01:32 2024 UTC