php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #39759 Can't use stored procedures from PDO on Windows
Submitted: 2006-12-06 17:09 UTC Modified: 2006-12-17 18:24 UTC
Votes:2
Avg. Score:5.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:1 (100.0%)
From: mike at we11er dot co dot uk Assigned: iliaa (profile)
Status: Closed Package: PDO related
PHP Version: 5.2.0 OS: Windows XP
Private report: No CVE-ID: None
 [2006-12-06 17:09 UTC] mike at we11er dot co dot uk
Description:
------------
On the windows version of pdo_mysql, the nextRowset() method isn't implemented for PDOStatement.

This means that when executing a stored procedure, it is impossible to fetch all result sets, because all stored procedures return multiple result sets (one contains a return value of some sort).

Because you can't fetch all result sets, all subsequent queries are broken, with the following error/exception:

SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll().

nextRowset() is /required/ to properly use stored procedures.



Reproduce code:
---------------
It should be possible to fetch all results from a stored procedure with code like this:

$stmt = $db->prepare("CALL SomeProcedure()");
$stmt->execute();

do
{
  $stmt->fetchAll();
} while ($stmt->nextRowset());

Intead the error message:

SQLSTATE[HYC00]: Optional feature not implemented

Is displayed. But I don't see how this can be optional in this case, it is required.


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-12-06 17:14 UTC] tony2001@php.net
Duplicate of feature request #38842.
 [2006-12-06 17:46 UTC] mike at we11er dot co dot uk
I'm reopening this because it isn't a feature request, it's a /bug/. You cannot use stored procedures. That is a bug! And the bug report/feature request you mention has been idling for months.

This is an important issue and I have yet to hear any acknowledgement that this is an issue worth fixing. It's getting rather annoying now.
 [2006-12-06 17:55 UTC] tony2001@php.net
The error message "Optional feature not implemented" means that the feature is optional and it's not implemented yet.
We already have a request for this feature, no need for another one, thank you.

 [2006-12-06 18:03 UTC] mike at we11er dot co dot uk
I'm sorry to reopen again, but I think the "optional feature not implemented" is just wrong. That would imply that stored procedures are an optional feature. If so, then that is unnacceptable.

I have some code to reproduce the error:

<?php

/*

CREATE PROCEDURE Test()
BEGIN
  SELECT 0;
  SELECT 1;
END

*/

class DBWriter extends PDO
{
	public function __construct()
	{
	    try
	    {
	    	parent::__construct("mysql:host=localhost;dbname=test","user", "password");

	    	$this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	    }
	    catch (PDOException $e) {
  			throw new Exception("Failed to connect: " . $e->getMessage());
		}
	}
}

$db = new DBWriter();

$stmt = $db->prepare("CALL Test()");
$stmt->execute();
$stmt->fetchAll();

// run the query again and we get an error "Cannot execute queries while other unbuffered queries are active."
// but we can't fetch all results from the previous query because nextRowset isn't defined
$stmt->execute();


?>

I just want the status of this to be elevated - I think "feature request" is just plain wrong. I want to hear an official opinion on this...
 [2006-12-06 18:10 UTC] iliaa@php.net
Latest version enabled buffered queries by default so your 
code will work.
 [2006-12-06 18:38 UTC] mike at we11er dot co dot uk
Well it would seem buffered queries aren't on for some reason, but if I use

$this->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, TRUE);

I still get errors.
 [2006-12-06 18:40 UTC] mike at we11er dot co dot uk
FYI I'm using everything from PHP 5.2.0 which I downloaded just a couple of days ago.
 [2006-12-06 18:45 UTC] iliaa@php.net
The change is only about a day old, you need to download the 
latest win32 snapshot from http://snaps.php.net/
 [2006-12-06 20:05 UTC] mike at we11er dot co dot uk
Well, I tried with the latest snapshot and still no luck. What are buffered queries anyway? I still think that nextRowset needs to be implemented and this whole thing can go away.
 [2006-12-08 11:29 UTC] mike at we11er dot co dot uk
I'll be idling on ##php on irc.freenode.net as 'weller', it would be good to talk/debug directly. This is really stopping me in my tracks so I'd appreciate any help.
 [2006-12-08 16:30 UTC] mike at we11er dot co dot uk
I'm also seeing this error now:

SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query
 [2006-12-08 16:54 UTC] mike at we11er dot co dot uk
When I use libmysql.dll from the php snapshot, I get the connection lost error. When I use libmysql.dll from my mysql installation, I get the unbuffered query error.
 [2006-12-08 19:51 UTC] iliaa@php.net
This bug has been fixed in CVS.

Snapshots of the sources are packaged every three hours; this change
will be in the next snapshot. You can grab the snapshot at
http://snaps.php.net/.
 
Thank you for the report, and for helping us make PHP better.


 [2006-12-12 14:18 UTC] mike at we11er dot co dot uk
I'm sorry to reopen yet again, but I still have this issue, and it's identical to these two bug reports on the PECL site:

http://pecl.php.net/bugs/bug.php?id=7976
http://pecl.php.net/bugs/bug.php?id=5827

These people describe the "Lost connection to MySQL server
during query" when calling multiple stored procedures, which I am having on the latest cvs snapshot. I've done a clean install of everything, and this problem persists.

There is clearly still a problem somewhere with certain configurations.
 [2006-12-12 15:43 UTC] iliaa@php.net
It definitely works fine here even with 3 stored procedures 
being called from one script. If as you claim you get the same 
unbuffered query message from the PDO it means you are still 
using the old binaries
 [2006-12-13 11:19 UTC] mike at we11er dot co dot uk
I'm not using the old binaries. Every time I've tested this I have rd /s'd the php directory and copied the new snapshot version across.

I have tried with the latest snapshot again (Built On: Dec 13, 2006 09:30 GMT), the md5sum of the php_pdo_mysql.dll is 6460abb216e83219fb29b9e80580643a. I have tried with libmysql.dll from the php directory which gives the "SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server" and also with the libmysql.dll with my MySQL installation, which gives the unbuffered query error. So changing the libmysql.dll I use changes the error but not the cause of the problem, which must be pdo_mysql.

I have no doubt it works on your machine, but there is clearly still a problem /somewhere/. Perhaps you could try (if you haven't already) it with a more recent version of mysql and libmysql.dll.

Reading those pecl bug reports suggests that in some cases it doesn't happen all the time, although with me I've only ever seen it work once which was just after I started the mysql service, but then when I hit refresh it was broken again.

Once again, I am /not/ using old binaries, and if there is anything I can do to assure you of this let me know. I just want to find out why the heck this is happening. It could be a bug in MySQL itself for all I know, but I don't know how I would verify that.
 [2006-12-13 12:05 UTC] mike at we11er dot co dot uk
OK, having tested with the commandline version of php, the error is much less frequent, 99% of the time the test script works.

Running through apache however, the error happens every time, except sometimes when I have just restarted the mysql service.
 [2006-12-17 18:24 UTC] iliaa@php.net
Bug #39858 better describes the remaining, win32 specific 
issue.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Mar 19 03:01:29 2024 UTC