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
