From:
Operating system: Linux
PHP version: 5.3.2
Package: MySQL related
Bug Type: Bug
Bug description:MysqlSTMT could not effectively support dynamic SQL with
Prepare Statement
Description:
------------
Hi, I found this problem but i could not find any suggestion in google, so
I think that was probable a bug in Mysqli_STMT:
first,I create a dynamic SQL Procedure example:
CREATE UP_Get_PagedSoftware(
IN VI_PageSize INT,
IN VI_PageNow INT,
OUT OV_ROWS INT
)
BEGIN
DECLARE UV_BeginRow INT DEFAULT 0;
DECLARE UV_dynamicSQL VARCHAR(1000);
SET UV_BeginRow = (VI_PageNow-1)*VI_PageSize;
SELECT COUNT(id) INTO OV_ROWS FROM software;
SET UV_dynamicSQL = CONCAT_WS(' ','SELECT
Name,Size,Desp
FROM software LIMIT',UV_BeginRow,',',VI_PageSize);
SET @dynamicSQL = UV_dynamicSQL;
PREPARE pager_stmt FROM @dynamicSQL;
EXECUTE pager_stmt;
DEALLOCATE PREPARE pager_stmt;
END
I directly called this procedure in Mysql Command Line that was OK,
But I called this procedure in PHP page, the code is below:
$softlist = array();
if($this->link){
$this->link->query("set names 'utf8'");
$this->link->query("SET @count");
$stmt = $this->link->stmt_init();
$stmt = $this->link->prepare('CALL
UP_Get_PagedSoftware(?,?,@count)');
if($stmt){
$stmt->bind_param('ii',$this->pagesize,$currentpage);
$stmt->execute();
$stmt->store_result();
if($this->link->more_results()){
$this->link->next_result();
$rs = $this->link->query('SELECT
@count');
list($count) =
$rs->fetch_array(MYSQLI_NUM);
$this->pagecount=(int)$count;
$rs->free();
}
$stmt->bind_result($name,$size,$desp);
while($stmt->fetch()){
$softlist[]=array($name,$size,$desp);
}
$stmt->free_result();
$stmt->close();
}
}
return $softlist;
just like this, but the recoreds returned look like
this:'ostWEB304KB$æä½³åCache,æµè§ç½é¡µé度快Clever
Terminal655KBé常好çTelNet软件 ä¸æ论å'
it seemed the Mysqli_STMT returned all records according to start and end
index and allocate into one filed
--
Edit bug report at http://bugs.php.net/bug.php?id=52196&edit=1
--
Try a snapshot (PHP 5.2):
http://bugs.php.net/fix.php?id=52196&r=trysnapshot52
Try a snapshot (PHP 5.3):
http://bugs.php.net/fix.php?id=52196&r=trysnapshot53
Try a snapshot (trunk):
http://bugs.php.net/fix.php?id=52196&r=trysnapshottrunk
Fixed in SVN:
http://bugs.php.net/fix.php?id=52196&r=fixed
Fixed in SVN and need be documented:
http://bugs.php.net/fix.php?id=52196&r=needdocs
Fixed in release:
http://bugs.php.net/fix.php?id=52196&r=alreadyfixed
Need backtrace:
http://bugs.php.net/fix.php?id=52196&r=needtrace
Need Reproduce Script:
http://bugs.php.net/fix.php?id=52196&r=needscript
Try newer version:
http://bugs.php.net/fix.php?id=52196&r=oldversion
Not developer issue:
http://bugs.php.net/fix.php?id=52196&r=support
Expected behavior:
http://bugs.php.net/fix.php?id=52196&r=notwrong
Not enough info:
http://bugs.php.net/fix.php?id=52196&r=notenoughinfo
Submitted twice:
http://bugs.php.net/fix.php?id=52196&r=submittedtwice
register_globals:
http://bugs.php.net/fix.php?id=52196&r=globals
PHP 4 support discontinued: http://bugs.php.net/fix.php?id=52196&r=php4
Daylight Savings: http://bugs.php.net/fix.php?id=52196&r=dst
IIS Stability:
http://bugs.php.net/fix.php?id=52196&r=isapi
Install GNU Sed:
http://bugs.php.net/fix.php?id=52196&r=gnused
Floating point limitations:
http://bugs.php.net/fix.php?id=52196&r=float
No Zend Extensions:
http://bugs.php.net/fix.php?id=52196&r=nozend
MySQL Configuration Error:
http://bugs.php.net/fix.php?id=52196&r=mysqlcfg
