调用mysql存储过程后出错

uqcuzwp8  于 2023-11-16  发布在  Mysql
关注(0)|答案(2)|浏览(85)

对不起，我的英语和对不起，如果这条消息组成不正确-这是我的第一个问题。
我失去了2个晚上，试图解决一个有趣的问题与mysql断开连接，后过程调用。这将是更有趣的，当我说，这个问题只是与SELECT查询过程。
所以，我的例子。我有两个类和过程：
1)DBCONN -用于连接和处理查询。

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 -创建DBCONN对象并向其发送文本命令。

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)存储过程

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

型
好吧..
如果你执行下一个代码...

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

型
页面将返回给您

status = 1
status = Bye. 0-

型
但是如果您将更改“CALL Login_Actions（-1，'$email'，''，'$pass'，''）;”在查询哪个案例过程中使用这些参数“select md5（concat（md5（$pass），vcPasssalt））= vcPassword 'isTrue' from Login where vcEmail=$email;"，则会得到OK结果。

status = 1
status = 1

型
我不明白-为什么每次使用SELECT的PROCEDURE后mysql连接都会关闭？PROCERUDE中的SQL没有问题。请帮助-我正在撕裂我的头发。
UPD：GetData方法的“if（$stmt->prepare（$call）= error）$this->_error（）;”出现错误。第一个实现是OK的，其余都是坏的。

mysql

2条答案

按热度按时间
eqqqjvef

eqqqjvef1#

我找到了一些解决方案。它并不漂亮，但它的工作，并有助于解决一个问题，在一个连接中调用许多过程。
我需要在GetData方法中添加这一行。

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

return $arr;

字符串
最后一个类是：

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;
}
}

型
谢谢大家，极客们！

赞(0）分享  回复(0）举报  2023-11-16
zf2sa74q

zf2sa74q2#

正如你可能已经怀疑的那样，这个问题与'mysql'连接无关，这很好。之后，我安装了你的代码，我得到了类似的错误。
我有你所有的代码在这里，它的工作。然而，我已经把很多调试代码在它和它是相当不整洁目前。
你遇到的主要问题是'DBConn'类。实际的错误是'store_result'有效地返回false，而你将false视为错误。我还修改了代码以符合编码标准，例如所有'控制流语句'（如'if'）必须使用大括号。我稍微调整了代码，使其更容易阅读。

  • 我没有以任何方式改变整体逻辑。*
  • 现在它更清楚地显示错误 *

它是工作，但不是很好的测试，在PHP 5.3.18和MySQL 5.5.16在Windows XP上。任何问题，然后请张贴。我会整理出来。
DBConn类：

<?php // 24321955/error-after-calling-mysql-stored-procedures

class DBConn
{
    private $mysqlC = null;

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

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

        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($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)
    {
        //$this->CreateConnection();

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

        if ($stmt->prepare($call) === false) {
            $this->_error(__FILE__.__LINE__, $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(__FILE__.__LINE__, $stmt->errno, $stmt->error);
            }
        }

        if ($stmt->execute() === false) {
            $this->_error(__FILE__.__LINE__, $stmt->errno, $stmt->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)
    {
        $stmt = $this->mysqlC->stmt_init();

        if ($stmt->prepare($call) === false) {
            $this->_error(__FILE__.__LINE__, $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(__FILE__.__LINE__, $stmt->errno, $stmt->error);
            }
        }

        if ($stmt->execute() === false) {
            $this->_error(__FILE__.__LINE__, $stmt->errno, $stmt->error);
        }

        $result = $stmt->store_result();
        if ( $result === false && !empty($stmt->error) ) { // sometimes no result is ok!
            $this->_error(__FILE__.__LINE__, $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();

        //$this->CloseConnection();

        return $arr;
    }
}

字符串

赞(0）分享  回复(0）举报  2023-11-16
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com