From: web at sellingpower dot com
Operating system: ubuntu5.6
PHP version: 5.2.10
PHP Bug Type: MySQLi related
Bug description: fetch mashes data when Stored Procedure uses prepared statments
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 bug report at http://bugs.php.net/?id=48857&edit=1
--
Try a CVS snapshot (PHP 5.2): http://bugs.php.net/fix.php?id=48857&r=trysnapshot52
Try a CVS snapshot (PHP 5.3): http://bugs.php.net/fix.php?id=48857&r=trysnapshot53
Try a CVS snapshot (PHP 6.0): http://bugs.php.net/fix.php?id=48857&r=trysnapshot60
Fixed in CVS: http://bugs.php.net/fix.php?id=48857&r=fixedcvs
Fixed in CVS and need be documented: http://bugs.php.net/fix.php?id=48857&r=needdocs
Fixed in release: http://bugs.php.net/fix.php?id=48857&r=alreadyfixed
Need backtrace: http://bugs.php.net/fix.php?id=48857&r=needtrace
Need Reproduce Script: http://bugs.php.net/fix.php?id=48857&r=needscript
Try newer version: http://bugs.php.net/fix.php?id=48857&r=oldversion
Not developer issue: http://bugs.php.net/fix.php?id=48857&r=support
Expected behavior: http://bugs.php.net/fix.php?id=48857&r=notwrong
Not enough info: http://bugs.php.net/fix.php?id=48857&r=notenoughinfo
Submitted twice: http://bugs.php.net/fix.php?id=48857&r=submittedtwice
register_globals: http://bugs.php.net/fix.php?id=48857&r=globals
PHP 4 support discontinued: http://bugs.php.net/fix.php?id=48857&r=php4
Daylight Savings: http://bugs.php.net/fix.php?id=48857&r=dst
IIS Stability: http://bugs.php.net/fix.php?id=48857&r=isapi
Install GNU Sed: http://bugs.php.net/fix.php?id=48857&r=gnused
Floating point limitations: http://bugs.php.net/fix.php?id=48857&r=float
No Zend Extensions: http://bugs.php.net/fix.php?id=48857&r=nozend
MySQL Configuration Error: http://bugs.php.net/fix.php?id=48857&r=mysqlcfg