#43178 [Asn->Bgs]: MySQL 5.1 prepared statements with resultset

ID: 43178 Updated by: andrey@php.net Reported By: dio at demis dot ru -Status: Assigned +Status: Bogus Bug Type: MySQLi related Operating System: Debian Linux PHP Version: 5.2.4 Assigned To: andrey New Comment: Hi, just a few notes. SP binding is not supported. If it works, by chance, still not supported. OUT SP variables are definitely not supported. In addition,mysqli_stmt_free_result() has no meaning if mysqli_stmt_store_result() is not called beforehand. Normal queries are "stored" by default, but PS are "used" by default (use = streaming, store = caching). As the data has not been fetched fully, you cannot execute anything else. Stored Procedures always have at least one result set. If a SP does SELECTs than there will be more than one result sets. This obligatory result set contains status information regarding the execution of the SP. It's always the last set. Thus you need to do additional fetch. If you have called mysqli_stmt_store_result() the second fetch would have been unnecessary. Andrey Previous Comments: ------------------------------------------------------------------------ [2007-11-04 21:28:30] waltermin at hotmail dot com 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. ------------------------------------------------------------------------ [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