Doc #64638 [Opn]: Fetching resultsets from stored procedure with cursor fails
Edit report at https://bugs.php.net/bug.php?id=64638&edit=1
ID: 64638
User updated by: DimonSoft at sa-sec dot org
Reported by: DimonSoft at sa-sec dot org
-Summary: No info about combining prepared statements and
stored procedures
+Summary: Fetching resultsets from stored procedure with
cursor fails
Status: Open
Type: Documentation Problem
Package: MySQLi related
Operating System: Irrelevant
-PHP Version: Irrelevant
+PHP Version: 5.3.13
Block user comment: N
Private report: N
New Comment:
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.
Previous Comments:
------------------------------------------------------------------------
[2013-04-11 23:37:54] 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.
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=64638&edit=1
Thread (8 messages)