Bug #52196 [Fbk->Opn]: MysqlSTMT could not effectively support dynamic SQL with Prepare Statement

[lanpioneer at 126 dot com]

Edit report at http://bugs.php.net/bug.php?id=52196&edit=1

 ID:               52196
 User updated by:  lanpioneer at 126 dot com
 Reported by:      lanpioneer at 126 dot com
 Summary:          MysqlSTMT could not effectively support dynamic SQL
                   with Prepare Statement
-Status:           Feedback
+Status:           Open
 Type:             Bug
 Package:          MySQL related
 Operating System: Linux
 PHP Version:      5.3.2
 Assigned To:      mysql

 New Comment:

I used mysqlnd and Linux version is Linux version 2.6.9-78.ELsmp
(brewbuil...@hs20-bc2-3.build.redhat.com)

Also, i tried to test it on windows but it still like that.


Previous Comments:
------------------------------------------------------------------------
[2010-06-28 12:57:33] and...@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