#43178 [NEW]: MySQL 5.1 prepared statements with resultset

From: dio at demis dot ru Operating system: Debian Linux PHP version: 5.2.4 PHP Bug Type: MySQLi related Bug description: MySQL 5.1 prepared statements with resultset Description: ------------ Im trying to use MySQL 5.1 prepared statements that can return resultsets. Seems that mysqli can not prepare second statement even if free_result() was called for first. MySQL 5.0 doesn't able to return resultsets from stored procedures at all. smart:/home/dio# php --version PHP 5.2.4-1+b1 with Suhosin-Patch 0.9.6.2 (cli) (built: Oct 1 2007 10:35:56) Copyright (c) 1997-2007 The PHP Group Zend Engine v2.2.0, Copyright (c) 1998-2007 Zend Technologies smart:/home/dio# mysql --version mysql Ver 14.14 Distrib 5.1.22-rc, for pc-linux-gnu (i486) using EditLine wrapper Reproduce code: --------------- <?php $id = 39; $login; $md5password; $role; $fullname; $mysqli = new mysqli("localhost", "root", "", "tyres"); $stmtLoad = $mysqli->prepare("CALL spUserLoad(?)"); $stmtLoad->bind_param("i", $id); $stmtLoad->execute(); $stmtLoad->bind_result($id, $login, $md5password, $fullname, $role); $stmtLoad->fetch(); $stmtLoad->free_result(); var_dump($id, $login, $md5password, $fullname, $role); // Everything was loaded fine $stmtSave = $mysqli->prepare("CALL spUserUpdate(?, ?, ?, ?, ?)"); echo $mysqli->error; // Commands out of sync; you can't run this command now ?> Code of stored procedures: CREATE PROCEDURE spUserLoad(IN iId INT) BEGIN SELECT id, login, md5password, fullname, role FROM users WHERE id = iId; END CREATE PROCEDURE spUserUpdate(IN iLogin VARCHAR(256), IN iMd5password VARCHAR(32), IN iFullname VARCHAR(1024), IN iRole INT, IN iId INT) BEGIN IF (SELECT COUNT(*) FROM users WHERE login = iLogin AND id != iId) = 0 THEN UPDATE users SET login = iLogin, md5password = iMd5Password, fullname = iFullname, role = iRole WHERE id = iId; ELSE SELECT 2 AS SP_ERROR_CODE; END IF; END Expected result: ---------------- The new statement should be prepared for calling. And be able to return resultsets too Actual result: -------------- The new statement can not be prepared due to "Commands out of sync; you can't run this command now" error :( -- Edit bug report at http://bugs.php.net/?id=43178&edit=1 -- Try a CVS snapshot (PHP 4.4): http://bugs.php.net/fix.php?id=43178&r=trysnapshot44 Try a CVS snapshot (PHP 5.2): http://bugs.php.net/fix.php?id=43178&r=trysnapshot52 Try a CVS snapshot (PHP 5.3): http://bugs.php.net/fix.php?id=43178&r=trysnapshot53 Try a CVS snapshot (PHP 6.0): http://bugs.php.net/fix.php?id=43178&r=trysnapshot60 Fixed in CVS: http://bugs.php.net/fix.php?id=43178&r=fixedcvs Fixed in release: http://bugs.php.net/fix.php?id=43178&r=alreadyfixed Need backtrace: http://bugs.php.net/fix.php?id=43178&r=needtrace Need Reproduce Script: http://bugs.php.net/fix.php?id=43178&r=needscript Try newer version: http://bugs.php.net/fix.php?id=43178&r=oldversion Not developer issue: http://bugs.php.net/fix.php?id=43178&r=support Expected behavior: http://bugs.php.net/fix.php?id=43178&r=notwrong Not enough info: http://bugs.php.net/fix.php?id=43178&r=notenoughinfo Submitted twice: http://bugs.php.net/fix.php?id=43178&r=submittedtwice register_globals: http://bugs.php.net/fix.php?id=43178&r=globals PHP 3 support discontinued: http://bugs.php.net/fix.php?id=43178&r=php3 Daylight Savings: http://bugs.php.net/fix.php?id=43178&r=dst IIS Stability: http://bugs.php.net/fix.php?id=43178&r=isapi Install GNU Sed: http://bugs.php.net/fix.php?id=43178&r=gnused Floating point limitations: http://bugs.php.net/fix.php?id=43178&r=float No Zend Extensions: http://bugs.php.net/fix.php?id=43178&r=nozend MySQL Configuration Error: http://bugs.php.net/fix.php?id=43178&r=mysqlcfg