Bug #52196 [NEW]: MysqlSTMT could not effectively support dynamic SQL with Prepare Statement
| From: | lanpioneer at 126 dot com | Date: | Sun, 27 Jun 2010 04:45:06 +0000 |
| Subject: | Bug #52196 [NEW]: MysqlSTMT could not effectively support dynamic SQL with Prepare Statement | ||
| Groups: | php.bugs | ||
| Request: | Send a blank email to php-bugs+get-151908@lists.php.net to get a copy of this message | ||
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