php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #56998 Stored procedure + Prepare + PDO returns garbled data
Submitted: 2006-05-03 10:37 UTC Modified: 2008-01-07 07:56 UTC
From: dhrubab at gmail dot com Assigned:
Status: Closed Package: PDO_MYSQL (PECL)
PHP Version: 5.1.4 OS: Linux
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: dhrubab at gmail dot com
New email:
PHP Version: OS:

 

 [2006-05-03 10:37 UTC] dhrubab at gmail dot com
Description:
------------
PHP 5.1.3
MySQL 5.0.20a
Apache 2.2.0

I create a stored procedure with two select statements.  Then I call that stored procedure from my code and try to access all data in both result sets.

It doesn't work.  I only get all data from the first result set.  It seems I can't physically iterate past the first result set (or row-set as pdo docs like to call it).  Hence nextRowSet() is of no use to me.  I've no idea whether this is a problem with PDO, PDO_MYSQL or MYSQL but I leave it in your capable hands.

I even tried the documented example on php.net/pdo-statement-nextrowset and that didn't work either.  Other people on irc have also verified that the example on the man page doesn't work.  I'm using the latest versions of everything.

Kindly shed some light on this as I could really use this feature.  I look forward to hearing back.  Many thanks.

Reproduce code:
---------------
Create a stored procedure
--------------------------

DELIMITER $

DROP PROCEDURE IF EXISTS OneTwo $
CREATE PROCEDURE OneTwo ()
BEGIN
	SELECT 'one';
	SELECT 'two';    
END; $
DELIMITER ;

Call it from your code
-----------------------

<?php
try {

	$oDB = new PDO('mysql:host=localhost;dbname=dhruba', 'dhruba', 'dhruba');
    $oDB->setAttribute(PDO :: ATTR_ERRMODE, PDO :: ERRMODE_EXCEPTION);
    $oDB->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
	
    $sQuery = 'CALL OneTwo()';
    $oStm = $oDB->prepare($sQuery);
	$oStm->execute();

    $aResultSets = array();
	do {
		$aResultSets[] = $oStm->fetchAll(PDO :: FETCH_OBJ);
	}
	while ($oStm->nextRowSet());
    
    var_dump($aResultSets);

} catch (PDOException $e) {

	var_dump($e);

}
?>

Expected result:
----------------
array
  0 => 
    array
      0 => 
        object(stdClass)[3]
          public 'one' => 'one' (length=3)
  1 => 
    array
      0 => 
        object(stdClass)[3]
          public 'two' => 'two' (length=3)


Actual result:
--------------
array
  0 => 
    array
      0 => 
        object(stdClass)[3]
          public 'one' => 'one' (length=3)


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-05-03 11:21 UTC] dhrubab at gmail dot com
Here is the same output but without xdebug for a more conventional var_dump() format.

Expected:


array(1) {
  [0]=>
  array(1) {
    [0]=>
    object(stdClass)#3 (1) {
      ["one"]=>
      string(3) "one"
    }
  }
  [1]=>
  array(1) {
    [0]=>
    object(stdClass)#3 (1) {
      ["two"]=>
      string(3) "two"
    }
  }
}

Actual:

array(1) {
  [0]=>
  array(1) {
    [0]=>
    object(stdClass)#3 (1) {
      ["one"]=>
      string(3) "one"
    }
  }
}
 [2006-05-03 11:43 UTC] dhrubab at gmail dot com
Changing php version to 5.1.3 (wasn't available in dropdown initially)
 [2006-05-12 10:44 UTC] dhrubab at gmail dot com
Happens with PHP 5.1.4 too.  Any suggestions?
 [2006-07-07 12:46 UTC] sherman at mos dot org
I'm getting the same issue except an error is thrown reading:
Connection failed: SQLSTATE[HYC00]: Optional feature not implemented

My script is similar, notably different in that it is using the pdo "query" function to execute a stored procedure.

this is on a windows server 2003 system with:
php 5.1.4
mysql 5.0.21
apache 2.0.55

any help is greatly appreciated
 [2006-09-10 16:24 UTC] unixy at hotmail dot nospam dot com
PHP 5.1.4
Apache 2.2.3
MySQL 5.0.18 
Linux 2.4

Unable to prepare and execute a stored procedure. In my case ( PDO-Mysql ) it causes the Apache child process to segfault. Pretty annoying. As a temporary fix, having a stored procedure dump to a temporary table, then selecting from that is a useful workaround.

Code:
<?php
$var=10;
$sh=$res->prepare("CALL some_sp(?)");
$sh->execute(array( $var ) );           <-- segfault
?>
Also, a straight $res->query("CALL some_sp(10)"); will segfault. Would like to know where this problem resides (PDO, PHP). Or if anyone knows a patch/fix.

Cheers.
 [2006-10-27 02:42 UTC] yuanjie dot zhong at gmail dot com
PHP 5.1.6
Apache 2.2.3
MySQL 5.0.26 
Win XP
----------------

Happens with PHP 5.1.6 too.
 [2006-11-10 13:49 UTC] baj at aberdeensw dot com
Be advised this issue persists in PHP 5.2.0 (Windows binary distribution).  I did not observe the problem in PHP 5.1.6 (Linux built from source).
 [2006-11-10 14:38 UTC] baj at aberdeensw dot com
Additional info:

The environment where this problem exhibits itself for me:

  OS: Windows XP SP2
  PHP: 5.2.0 (binary distribution)
  MySQL: 5.0.24a (remote Linux server)

The environment that does not exhibit the problem for me:

  OS: Slackware Linux 10.2
  PHP: 5.1.6 (compiled from source)
  MySQL: 5.0.24a (local server, compiled from source)

I see from the pdo_mysql_stmt_next_rowset() function in file ext/pdo/mysql_statement.c that a macro called HAVE_MYSQL_NEXT_RESULT is required in order for the main body of code to be compiled.  If the macro is not defined, the error described in this bug report is raised:

  #if HAVE_MYSQL_NEXT_RESULT
    .
    .
    .
  #else
    strcpy(stmt->error_code, "HYC00");
    return 0;
  #endif

I therefore conclude that either the Windows distribution is compiled using MySQL libraries that do not support the "next result" function or that the HAVE_MYSQL_NEXT_RESULT macro is not being defined properly during the configure/build process.
 [2006-12-04 13:16 UTC] mike at we11er dot co dot uk
I have this problem!

Please for the love of god, I want a dev to address this.

nextRowset isn't implemented on windows, so it's impossible to use stored procedures from PDO without breaking subsequent queries.

This is a show-stopping bug and must be fixed!
 [2006-12-09 05:29 UTC] mike at we11er dot co dot uk
Having opened a bug related to this for the issue on Windows and had it closed... my problem still persists, so I'll post my information here.

I can only speak for windows here. So, because nextRowset is unavailable you get one of two errors when calling subsequent stored procedures/queries after the very first one.

When query buffering is off, you get this error:

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

In my bug report, one of the pdo_mysql devs said that using buffered queries will fix this. I used the latest cvs snapshot as per instructions (which enables it by default), and I now get the following error:

SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query

Of course, calling nextRowset() returns:

SQLSTATE[HYC00]: Optional feature not implemented


The dev closed my bug even though the problem apparently has nothing to do with buffered queries. I already re-opened it twice, so I've emailed him directly to try and see wtf is going on.
 [2007-01-26 02:10 UTC] gigawatz at gmail dot com
I am having the same problem accessing the 2nd rowset with PDO on the following configuration:

PHP 5.2.0
Apache 2.2.3
MySQL 5.0.23
Suse Linux 10.1
 [2007-06-12 03:10 UTC] patrick at hipheid dot com
What's the status on this bug? I also have the same problem on windows...
 [2007-06-25 06:27 UTC] diogo86 at gmail dot com
I'm getting garbled data while fetching rows from a stored procedure that has a prepared statement. The garbage seems to be from old queries, even from closed connections in different databases with different users.


Stored procedure:
-----------------

DELIMITER //

DROP PROCEDURE IF EXISTS bugpdo//

CREATE PROCEDURE bugpdo()
BEGIN
    PREPARE stmt FROM "SELECT 1 AS a, 'second column' AS b, NOW() AS c FROM dual";
    
    EXECUTE stmt;
    
    DEALLOCATE PREPARE stmt;
END;
//

DELIMITER ;


PHP Script:
-----------

<?php
$dbh = new PDO(
    'mysql:host=localhost;dbname=testes',
    'nobody',
    'nobody');
 
$res = $dbh->query("CALL bugpdo()");
 
var_dump($res->fetch());
?>


Expected result:
----------------

mysql> CALL bugpdo();
+---+---------------+---------------------+
| a | b             | c                   |
+---+---------------+---------------------+
| 1 | second column | 2007-06-25 07:19:24 |
+---+---------------+---------------------+
1 row in set (0.00 sec)


Actual result:
--------------

array(6) {
  ["a"]=>
  string(19) "2334111931572777741"
  [0]=>
  string(19) "2334111931572777741"
  ["b"]=>
  string(99) "olumn2007-06-25 07:18:34&#65533;obody&#65533;????֖?*?K7?e???i??testes&#65533;b3d4dc4b30dcdb2468c47cca06ed1d90d3980ca"
  [1]=>
  string(99) "olumn2007-06-25 07:18:34&#65533;obody&#65533;????֖?*?K7?e???i??testes&#65533;b3d4dc4b30dcdb2468c47cca06ed1d90d3980ca"
  ["c"]=>
  NULL
  [2]=>
  NULL
}


Versions
--------

PHP 5.2.0-10+lenny1 (cli) (built: May 26 2007 08:23:53)
Mysql 5.0.38-Debian_1-log
 [2008-01-07 07:32 UTC] richard dot blockley at googlemail dot com
How is the issue of nextRowset() being not implemented progressing? I'm using the latest version of PHP, MYSQL and APACHE on WinXP and this is occuring.

I'm not using any concurrent statement processing, and using only fetchAll() rather than using some of a recordset and stopping. Please advise.
 [2008-01-07 07:56 UTC] dhrubab at gmail dot com
Closing as not my concern anymore.  Someone else take 
ownership and re-report.  This has had nil official feedback 
anyway for this long; makes you wonder what's the point.
 
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Sat Sep 19 16:01:24 2020 UTC