Bug #52196 [Opn->Fbk]: MysqlSTMT could not effectively support dynamic SQL with Prepare Statement
| From: | andrey@php.net | Date: | Mon, 28 Jun 2010 10:57:34 +0000 |
| Subject: | Bug #52196 [Opn->Fbk]: MysqlSTMT could not effectively support dynamic SQL with Prepare Statement | ||
| References: | 1 | Groups: | php.bugs |
| Request: | Send a blank email to php-bugs+get-151922@lists.php.net to get a copy of this message | ||
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: Open
+Status: Feedback
Type: Bug
Package: MySQL related
Operating System: Linux
PHP Version: 5.3.2
-Assigned To:
+Assigned To: mysql
New Comment:
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
Previous Comments:
------------------------------------------------------------------------
[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