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

ID: 43178 Comment by: waltermin at hotmail dot com Reported By: dio at demis dot ru Status: Open Bug Type: MySQLi related Operating System: Debian Linux PHP Version: 5.2.4 New Comment: I have the same problem reported here. I can't execute two prepared consecutive statements. I'm using MySQL 6.0 and PHP 5.2.4. The error messagge is the same: "Commands out of sync; you can't run this command now". Thanks in advance. I'm in trouble with this. Previous Comments: ------------------------------------------------------------------------ [2007-11-02 13:36:22] dio at demis dot ru 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 this bug report at http://bugs.php.net/?id=43178&edit=1