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

[waltermin at hotmail dot com]

 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