php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #43178 MySQL 5.1 prepared statements with resultset
Submitted: 2007-11-02 13:36 UTC Modified: 2007-11-12 16:22 UTC
Votes:1
Avg. Score:5.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: dio at demis dot ru Assigned: andrey (profile)
Status: Not a bug Package: MySQLi related
PHP Version: 5.2.4 OS: Debian Linux
Private report: No CVE-ID: None
 [2007-11-02 13:36 UTC] 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 :(

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2007-11-04 21:28 UTC] 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-12 16:22 UTC] andrey@php.net
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
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Wed Feb 21 23:01:28 2024 UTC