Bug #35935 [Com]: MySQL stored procedure doesn't return value when using bindParam()

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] 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()); ------------------------------------------------------------------------ [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] 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 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