|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[2013-04-11 23:37 UTC] DimonSoft at sa-sec dot org
Description:
------------
It seems like there's no enough info in the documentation on how to retrieve result sets when calling a stored procedure with mysqli_stmt (prepared statement).
Although it is quite easy to find non-official recomendations on the Internet (like using mysqli_stmt_more_results() and mysqli_stmt_next_result()) it is still not enough.
Right now I'm having a related problem. There're 2 stored procedures: Proc1 and Proc2. Proc1 internally calls Proc2(), which, in its turn, produces a resultset. See pseudocode.
Test script:
---------------
Pseudocode:
$DB = new mysqli(…);
$Stmt = $DB->prepare('CALL `Proc1`(?)');
$Stmt->bind_param(…);
$Stmt->execute();
$Stmt->store_result();
$Stmt->bind_result(…);
while ($Stmt->fetch())
{
…
}
while ($Stmt->next_result())
$Stmt->store_result();
$Stmt->free_result
…
Expected result:
----------------
Resultset is expected to get fetched.
Actual result:
--------------
A call to $Stmt->fetch() fails with "Packets out of order" message.
I guess, I'm doing something wrong, but there're no hints anywhere in the documentation.
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||||||||||||
Copyright © 2001-2024 The PHP GroupAll rights reserved. |
Last updated: Tue Dec 03 17:01:29 2024 UTC |
Description: ------------ Attempt to retrieve resultsets from stored procedures containing cursors fails with "Packets out of order" message. Retrieving data from SPs without cursors works fine. See "Test script" section for a minimal test that reproduces the problem. Test script: --------------- PHP: <?php $DB = new mysqli('SomeHost', 'SomeLogin', 'SomePassword', 'SomeDB'); $Stmt = $DB->prepare('CALL `Proc1`()'); $Stmt->execute(); $Stmt->store_result(); $Stmt->bind_result($Res); $Stmt->fetch(); echo 'OK'; $Stmt->close(); $DB->close(); var_dump($Res); ?> SQL: DROP PROCEDURE IF EXISTS `Proc1`; DELIMITER // CREATE DEFINER=`root`@`localhost` PROCEDURE `Proc1`() LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN DECLARE Temp CURSOR FOR SELECT 15 AS Result; OPEN Temp; CLOSE Temp; SELECT 20 AS Result; END// DELIMITER ; Expected result: ---------------- "OKint(20)" is expected to be output without any error messages. Actual result: -------------- A call to $Stmt->fetch() fails with "Packets out of order" message.delimiter // CREATE PROCEDURE `test`() BEGIN declare test_var varchar(100) default "ciao"; declare bNoMoreRows bool default false; declare test_cursor cursor for select id from tmp_folder; declare continue handler for not found set bNoMoreRows := true; create temporary table tmp_folder select "test" as id; open test_cursor; fetch test_cursor into test_var; close test_cursor; select test_var; drop temporary table if exists tmp_folder; END// ./php -r '$c=mysqli_connect("127.0.0.1", "root","", "test");$s=$c->prepare("CALL test()");var_dump($s->execute());var_dump($s->get_result());'