|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[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)
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||
Copyright © 2001-2024 The PHP GroupAll rights reserved. |
Last updated: Thu Apr 18 23:01:27 2024 UTC |
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" } } }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.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.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�obody�????֖?*?K7?e???i??testes�b3d4dc4b30dcdb2468c47cca06ed1d90d3980ca" [1]=> string(99) "olumn2007-06-25 07:18:34�obody�????֖?*?K7?e???i??testes�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