调用mysql存储过程后出错

Question

Sorry for my english and sorry if this message composed incorrect - it is my first question.

I had lost 2 evenings when tried to resolve one interesting problem with mysql disconnecting, after procedure call. It's going to be more interesting, when I'll say, that problem is only with SELECT queries in procedures.

So, my example. I have 2 classes and procedure:

1) DBCONN - for connection and handle queries.

class DBCONN
{
    private $mysqlC = null;

    public function __construct()
    {
        $this->CreateConnection();
    }

    public function __destruct() 
    {
        //$this->mysqlC->close();
    }

    private function CreateConnection()
    {
        $mC = new mysqli("localhost", "root", "root", "root");

        if ($mC->connect_error)
            die('Bye. '.$mC->connect_errno."-".$mC->connect_error);
        else 
            $mC->set_charset("utf8");

        $this->mysqlC = $mC;
    }

    private function CloseConnection()
    {
        $this->mysqlC->close();
    }

    private function _error()
    {
        die('Bye. '.$this->mysqlC->connect_errno."-".$this->mysqlC->connect_error);
    }

    public function SetData($call, $types = null, $params = null)
    {
        //$this->CreateConnection();

        $stmt = $this->mysqlC->stmt_init();

        if ($stmt->prepare($call) === FALSE) 
            $this->_error();

        if ($params && call_user_func_array(array($stmt, "bind_param"),     array_merge(array($types), $params)) === FALSE)
            $this->_error();
        if ($stmt->execute() === FALSE) 
            $this->_error();

        $insid = $stmt->insert_id;
        $affrows = $stmt->affected_rows;

        $stmt->close();

        //$this->CloseConnection();

        return array($insid, $affrows);
    }

    public function GetData($call, $types = null, $params = null)
    {
        //$this->CreateConnection();
        //#LOOK HERE BEGIN
        print 'status = '.$this->mysqlC->ping();
        //#LOOK HERE END
        //print $call;

        $stmt = $this->mysqlC->stmt_init();

        if ($stmt->prepare($call) === FALSE) 
            $this->_error();

        if ($params && call_user_func_array(array($stmt, "bind_param"), array_merge(array($types), $params)) === FALSE)
            $this->_error();

        if ($stmt->execute() === FALSE) 
            $this->_error();
        if ($stmt->store_result() === FALSE) 
            $this->_error();

        $meta = $stmt->result_metadata();

        while ($field = $meta->fetch_field())
             $var[] = &$row[$field->name];

        call_user_func_array(array($stmt, 'bind_result'), $var);

        $arr = null;

        while ($stmt->fetch())
        {
            foreach($row as $key => $val)
                $c[$key] = $val;

            $arr[] = $c;
        }

        $stmt->close();

        //$this->CloseConnection();

        return $arr;        
    }

}

2) BASEACTIONS - creates DBCONN object and sends text commands to it.

class BASEACTIONS
{
    private $conn = null;
    public function __construct() {
        $this->conn = new DBCONN();
    }

    private function CheckPassword($email = '', $pass = '')
    {
        //#LOOK HERE BEGIN
        $arr = $this->conn->GetData("CALL Login_Actions(-1, '$email', '', '$pass', '');"); 
        $arr = $this->conn->GetData("CALL Login_Actions(-1, '$email', '', '$pass', '');");
        //#LOOK HERE END

        return ($arr[0]['isTrue']==1 ? true : false);
    }

    private function UpdateSession($email)
    {
        if (!session_regenerate_id()) return false;
        $session = session_id();

        $this->conn->SetData(
            "CALL Login_Session(2, ?, ?)",
            "ss", 
            array(&$email, &$session)
        );

        return true;
    }


    public function LoginUser($email = '', $pass = '')
    {
        if (!$this->UpdateSession($email)) return false;
        if (!$this->CheckPassword($email, $pass)) return false;

        return true;
    }
}

3) Stored procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `Login_Actions`(
_action INT, 
_vcEmail varchar(50),
_vcNickname varchar(20),
_vcPassword varchar(255),
_vcPasssalt varchar(10)
)
BEGIN

case _action
    when -1 then
        select md5(concat(md5(_vcPassword), vcPasssalt)) = vcPassword 'isTrue' from Login where vcEmail=_vcEmail;
    when 0 then
        select iId, vcEmail, vcNickname from Login;
    when 1 then
        insert into Login(vcEmail, vcNickname, dtDateAdd, vcPassword, vcPasssalt) values(_vcEmail, _vcNickname, UTC_TIMESTAMP(), md5(concat(md5(_vcPassword), _vcPasssalt)), _vcPasssalt);
end case;

END

Well... I've marked for you 2 blocks in these code like '//#LOOK HERE' - please, find it before.

If you will implement next code...

$BASE = new BASEACTIONS();
$BASE->LoginUser("[e-mail]", "mypassword");

...page will return to you

status = 1
status = Bye. 0-

But if you will change "CALL Login_Actions(-1, '$email', '', '$pass', '');" on query which case procedure with these parameters "select md5(concat(md5($pass), vcPasssalt)) = vcPassword 'isTrue' from Login where vcEmail=$email;", you'll get OK result.

status = 1
status = 1

I can't understand - why mysql connection close everytime after PROCEDURE with SELECT? There are no problems with INSERT in PROCERUDE. Please, help - I'm tearing my hairs.

UPD: Error occurs in "if ($stmt->prepare($call) === FALSE) $this->_error();" of GetData method. First implement of it is OK, all the rest is bad.

Answer 1

I found some solution. It is't pretty, but it works and helps to solve a problem with calling of many procedures in 1 connection.

I need to add this line in my GetData method.

$stmt->close();
while(mysqli_more_results($this->mysqlC))  //<<<<---- this line
    mysqli_next_result($this->mysqlC); //<<<<---- this line

return $arr;

So, final class is:

class DBConn
{
private $mysqlC = null;

public function __construct()
{
    $mC = new mysqli("localhost", "user", "password", "database");

    if ($mC->connect_error)
        $this->Error("Bye. ", $mC->connect_errno, $mC->connect_error);
    else
        $mC->set_charset("utf8");

    $this->mysqlC = $mC;
}

public function __destruct()
{
    $this->mysqlC->close();
}

private function IsConnected()
{
    return $this->mysqlC->ping();
}

private function Error($msg = '', $errno = 0, $error = '')
{
    die("Bye. {$msg} ".
               ($errno != 0 ? "errno: {$errno} - {$error}"
                            : "errno: {$this->mysqlC->errno} - {$this->mysqlC->error}"));
}

public function SetData($call, $types = null, $params = null)
{
    $stmt = $this->mysqlC->stmt_init();

    if ($stmt->prepare($call) === false) {
        $this->Error("", $stmt->errno, $stmt->error);
    }

    if ($params) {
        $result = call_user_func_array(array($stmt, "bind_param"),
                                              array_merge(array($types), $params));
        if ($result === false) {
            $this->Error("", $stmt->errno, $stmt->error);
        }
    }

    if ($stmt->execute() === false) {
        $this->Error("", $stmt->errno, $stmt->error);
    }

    $insid = $stmt->insert_id;
    $affrows = $stmt->affected_rows;

    $stmt->close();

    return array($insid, $affrows);
}

public function GetData($call, $types = null, $params = null)
{
    $stmt = $this->mysqlC->stmt_init();

    if ($stmt->prepare($call) === false) {
        $this->Error("", $stmt->errno, $stmt->error);
    }

    if ($params) {
        $result = call_user_func_array(array($stmt, "bind_param"),
                                       array_merge(array($types), $params));
        if ($result === false) {
            $this->Error("", $stmt->errno, $stmt->error);
        }
    }

    if ($stmt->execute() === false) {
        $this->Error("", $stmt->errno, $stmt->error);
    }

    $result = $stmt->store_result();
    if ( $result === false && !empty($stmt->error) ) { // failing!!! and throw away result
        $this->Error("", $stmt->errno, $stmt->error);
    }

    $meta = $stmt->result_metadata();

    while ($field = $meta->fetch_field()) {
         $var[] = &$row[$field->name];
    }

    call_user_func_array(array($stmt, 'bind_result'), $var);

    $arr = null;

    while ($stmt->fetch()) {
        foreach($row as $key => $val)
            $c[$key] = $val;

        $arr[] = $c;
    }

    $stmt->close();
    while(mysqli_more_results($this->mysqlC))  //<<<<---- this line
        mysqli_next_result($this->mysqlC); //<<<<---- this line

    return $arr;
}
}

Thank you all, geeks!