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

Tue, 06 Jul 2010 04:21:43 -0700 

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

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



Previous Comments:
------------------------------------------------------------------------
[2010-07-06 13:20:29] and...@php.net

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>

------------------------------------------------------------------------
[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
(brewbuil...@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] 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

Reply via email to