Bug #52196 [Asn]: MysqlSTMT could not effectively support dynamic SQL with Prepare Statement

Edit report at http://bugs.php.net/bug.php?id=52196&edit=1 ID: 52196 Updated by: andrey@php.net Reported by: lanpioneer at 126 dot com Summary: MysqlSTMT could not effectively support dynamic SQL with Prepare Statement Status: Assigned Type: Bug Package: MySQL related Operating System: Linux PHP Version: 5.3.2 Assigned To: mysql New Comment: Hi, I needed to do some changes to the attached script. MySQL knows utf8 but not utf-8. Because I use CLI for testing I added some \n-s in the printfs. Also, set @count is not a valid SQL statement, returns an error, should be set @count=NULL. After these changes, here is my output on the console (it seems right?): > php failedpagedrecoreds.php.patch.txt <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>title</title> </head> <body> <table border="1"> <tr> <td>Name</td> <td>Size</td> <td>Desp</td> </tr> <li>name=BoostWEB</li> <li>name=Clever Terminal</li> <li>name=中文论坛</li> <li>name=WinGate</li> <li>name=Copernic 99</li> <tr><td>BoostWEB</td><td>304KB</td><td>最佳化Cache,浏览网页速度快</td></tr> <tr><td>Clever Terminal</td><td>655KB</td><td>非常好的TelNet软件</td></tr> <tr><td>中文论坛</td><td>655KB</td><td>建立BBS的好软件</td></tr> <tr><td>WinGate</td><td>2523KB</td><td>多台电脑共用一个MODEN上网的软件</td></tr> <tr><td>Copernic 99</td><td>2404KB</td><td>Search工具</td></tr> </table> </body> </html> Previous Comments: ------------------------------------------------------------------------ [2010-06-28 14:33:54] lanpioneer at 126 dot com I used mysqlnd and Linux version is Linux version 2.6.9-78.ELsmp (brewbuilder@hs20-bc2-3.build.redhat.com) Also, i tried to test it on windows but it still like that. ------------------------------------------------------------------------ [2010-06-28 12:57:33] andrey@php.net Hi, can you give us more data: - Do you use mysqlnd or libmysql? (--with-mysqli=mysqlnd or --with-mysqli) - What is the server version? - Can you provide us with an SQL dump which includes the some data in the software table, as well as the SP you have already provided - And then a minimal script, which shows the problem. Thank you! Andrey ------------------------------------------------------------------------ [2010-06-27 06:45:05] lanpioneer at 126 dot com 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 this bug report at http://bugs.php.net/bug.php?id=52196&edit=1