mysql过程中的游标循环后的select语句在pdo中没有输出但在phpmyadmin中有输出

Question

I'm attempting to run a procedure containing a cursor loop on a select query and an output select after that. but my "output select" has no output on pdo call while it has output when i call my procedure in phpmyadmin or workbench. here is my procedure:

   CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_test`(IN `_payment_id` BIGINT (20),IN `_saleId` BIGINT(20))

BEGIN

DECLARE finished INTEGER DEFAULT 0;

DECLARE  cv_user_id VARCHAR(250) DEFAULT '';
DECLARE  output_id VARCHAR(250) DEFAULT '';

DECLARE `noStocks_Cursor` CURSOR 
    FOR 
        SELECT * FROM `noStocks_temp`; 

DECLARE exit handler for sqlexception
BEGIN
GET DIAGNOSTICS CONDITION 1
@p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
SELECT @p1 AS RETURNED_SQLSTATE, @p2 AS MESSAGE_TEXT;
ROLLBACK;
END;

DECLARE exit handler for sqlwarning
BEGIN
GET DIAGNOSTICS CONDITION 1
@p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
SELECT @p1 AS RETURNED_SQLSTATE, @p2 AS MESSAGE_TEXT;
ROLLBACK;
END;        

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

START TRANSACTION;

    DROP TEMPORARY TABLE IF EXISTS `noStocks_temp`;
    CREATE TEMPORARY TABLE IF NOT EXISTS 
        `noStocks_temp` ENGINE=MEMORY  
        AS  
        (
            SELECT `users`.`id` from `users` where `users`.`id`=108 
        );
    OPEN `noStocks_Cursor`; **// my cursor loop**
    noStocks_Loop: LOOP
        FETCH noStocks_Cursor INTO cv_user_id;
        IF finished=1 THEN 
            LEAVE noStocks_Loop;
        END IF;         

         UPDATE `payments` 
        SET `sale_id`=500
        WHERE `id`=_payment_id;
        SET  output_id=cv_user_id; 
    END LOOP noStocks_Loop;
    CLOSE `noStocks_Cursor`;     **// my cursor loop end**
    SELECT  output_id AS output_txt;    **// my output select**
COMMIT;

END

And finally this is my php code:

$_dbHandle = ' ';
$_PDOst = '';
function query($query)
{
    global $_PDOst;
    global $_dbHandle;
    $_PDOst = $_dbHandle->prepare($query);  
    $p_1='1134';
    $p_2='7';
    $_PDOst->bindParam(1, $p_1, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT);        
    $_PDOst->bindParam(2, $p_2, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT);        
    $result=$_PDOst->execute();
    $numOfRows = $_PDOst->rowCount();
    echo $numOfRows;
}
function connect($address, $account, $pwd, $name)  
{
    global $_dbHandle;

    if(isset($_SESSION['db_connection_counter']))
    $_SESSION['db_connection_counter']=$_SESSION['db_connection_counter']+1;

    $options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'); 
    try
    {
        $_dbHandle = new PDO("mysql:host={$address};dbname={$name};charset=utf8", $account, $pwd,$options );
        $_dbHandle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
        $_dbHandle->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        $_dbHandle->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); 
        return 1;
    }
    catch (PDOException $e)
    {
        die("no_connection");
        return 0;
    }

}
connect('127.0.0.1','root','','db1');
query("CALL `pr_test` (?,?)");

"Query" function in my php code returns 0 which is the number of output records that should be 1 due to the last select of procedure.

Answer 1

I found that! This attribute of pdo should be true as it's default value which i had changed that to false for sql injection attack.

setAttribute(PDO::ATTR_EMULATE_PREPARES, true);