#48857 [Opn->Fbk]: fetch mashes data when Stored Procedure uses prepared statements
| From: | uw@php.net | Date: | Wed, 26 Aug 2009 08:39:32 +0000 |
| Subject: | #48857 [Opn->Fbk]: fetch mashes data when Stored Procedure uses prepared statements | ||
| References: | 1 | Groups: | php.bugs |
| Request: | Send a blank email to php-bugs+get-141526@lists.php.net to get a copy of this message | ||
ID: 48857
Updated by: uw@php.net
Reported By: web at sellingpower dot com
-Status: Open
+Status: Feedback
Bug Type: MySQLi related
Operating System: ubuntu5.6
PHP Version: 5.2.10
-Assigned To:
+Assigned To: mysql
New Comment:
Thank you for this bug report. To properly diagnose the problem, we
need a short but complete example script to be able to reproduce
this bug ourselves.
A proper reproducing script starts with <?php and ends with ?>,
is max. 10-20 lines long and does not require any external
resources such as databases, etc. If the script requires a
database to demonstrate the issue, please make sure it creates
all necessary tables, stored procedures etc.
Please avoid embedding huge scripts into the report.
Previous Comments:
------------------------------------------------------------------------
[2009-07-08 21:58:21] web at sellingpower dot com
I misspelled statement in the summary and wanted to correct it for
search purposes.
------------------------------------------------------------------------
[2009-07-08 21:54:45] web at sellingpower dot com
Description:
------------
We are using the mysqli extension to interface with MySQL 5.0.51a. We
are using the prepare/bind_param/execute/bind_result/fetch process
against stored procedures. When the stored procedure pulls the data via
a prepared statment, fetch mangles the data when putting it into the
bound result variables.
PROCEDURE
p_TestNonPreparedStatement()
and
PROCEDURE p_TestPreparedStatement()
produce identical results in the query browser but very different
results when run through getresult($stmt,$fields)
Unfortunately, this server is behind a firewall and not accessible to
the public.
All previous bug reports similar to this have identified this behavior
as not a bug without identifying how to get around the problem.
Reproduce code:
---------------
CREATE DEFINER=robertp@` PROCEDURE p_TestNonPreparedStatement`()
READS SQL DATA
DETERMINISTIC
BEGIN
SELECT * FROM eBlast.veMailList;
END
--------------------------------------------------------------------------
CREATE DEFINER=robertp@` PROCEDURE p_TestPreparedStatement`()
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE new_query VARCHAR(4096) DEFAULT '';
SET new_query = 'SELECT * FROM eBlast.veMailList';
SET @new_query = new_query;
PREPARE filtersetlist_query FROM @new_query;
EXECUTE filtersetlist_query;
DEALLOCATE PREPARE filtersetlist_query;
END
--------------------------------------------------------------------------------------
public function pullSendees($status_id,&$fields)
{
$sendees = array();
$DB = new mysqli(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD,
DB_DATABASE_EBLAST);
if (mysqli_connect_errno()) {
throw new Exception('Connection to database failed');
}
$stmt = $DB->stmt_init();
if ($stmt->prepare('CALL eBlast.p_TestNonPreparedStatement
(?,?)'))
{
$stmt->bind_param('is',$this->id,$status_id);
$stmt->execute();
$sendees = getresult($stmt,$fields);
$stmt->close();
}
$DB->close();
return $sendees;
}
function getresult($stmt,&$fields)
{
$result = array();
$metadata = $stmt->result_metadata();
$fields = $metadata->fetch_fields();
for (;;)
{
$pointers = array();
$row = new stdClass();
$pointers[0] = $stmt;
$count = 1;
foreach ($fields as $field)
{
$fieldname = $field->name;
$pointers[$count] = &$row->$fieldname;
$count++;
}
call_user_func_array('mysqli_stmt_bind_result', $pointers);
if (!mysqli_stmt_fetch($stmt))
break;
foreach ($fields as $field)
{
$fieldname = $field->name;
trigger_error('$' . 'row->' . $fieldname . ' = ' .
$row->$fieldname);
}
$result[] = $row;
}
$metadata->free();
return $result;
}
Expected result:
----------------
Prepared Statment Results:
1 1 Robert Polickoski robertpolickoski@sellingpower.com
Fredericksburg VA 223 22406-1126 1 1 6 7 2 1 1 1
1 1 0 0 1 1 0 0 1 1 1 1
Actual result:
--------------
NonPrepared Statment Results:
1919246959 1867516532
ickoski!robertpolickoski@sellingpower.comFredericksburgVA223
22406-112611672111110 1 822161665
�������elli -5 -5
251 251 251 -5 -5 0 101 108 108 105 110
103 32 30575-101-114 32:72: 2008-10-07 00:00:00 2009-02-09
16:54:30 4294966272-105-01 00 27694-101-102 116:95
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/?id=48857&edit=1