How thinkphp calls sqlserver stored procedure to return multiple result sets

First install the extension

windows

Divided into two Steps

1. Find the pdo extension corresponding to your PHP version, download and unzip it, and enable the extension in php.ini. The issues you need to pay attention to are the PHP version and whether it is a safe version

2 .Download ODBC Driver https://docs.microsoft.com/zh-cn/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-2017, nothing to pay attention to. Just download the installation package for your system.

Linux is similar to windows. To install extensions, you can directly use pecl

When you successfully load it, you can see it in phpinfo(), of course. , if you install extensions and there are many problems, you will really have diarrhea.

thinkphp operates sqlsrv stored procedure

The tp version I use is 5.0 and I operate multiple databases. I hope it can be helpful to you

Configuration config file

 // 账号数据库
    'UserDBConn' =>  [
        'type'            => 'sqlsrv',
        // 服务器地址
        'hostname'        => '139.129.1.1',
        // 数据库名
        'database'        => 'DB3',
        // 用户名
        'username'        => 'xxxx',
        // 密码
        'password'        => 'tt123!@#',
        // 端口
        'hostport'        => '5188'
    ],
    // 金币数据库
    'ScoreDBConn' =>  [
        'type'            => 'sqlsrv',
        // 服务器地址
        'hostname'        => '139.129.1.1',
        // 数据库名
        'database'        => 'DB2',
        // 用户名
        'username'        => 'xxxx',
        // 密码
        'password'        => 'tt123!@#',
        // 端口
        'hostport'        => '5188'
    ],
    // 记录数据库
    'RecordDBConn' =>  [
        'type'            => 'sqlsrv',
        // 服务器地址
        'hostname'        => '139.129.1.1',
        // 数据库名
        'database'        => 'DB1',
        // 用户名
        'username'        => 'xxxx',
        // 密码
        'password'        => 'tt123!@#',
        // 端口
        'hostport'        => '5188'
    ],

Modify thinkphp/library/think/Model.php

Append

 /**
     * @param $DbconnName
     */
    protected function Dbconn($DbconnName){
        try{
            $conn = Db::connect($DbconnName);
        }catch (\InvalidArgumentException $e){
            echo '连接异常';
            die;
        }
        return $conn;
    }

Add model

Agent.php# at the end

## You can call query for both query and addition, deletion and modification. If you do not have the result set you want to obtain, you can call execute().

query() has a drawback. If the form of your binding parameters (non-parameter binding) is written directly into sql, it may determine that this is not a stored procedure;

Please check line 368 of thinkphp/library/think/db/Connection.php for specific implementation. Of course, no result set will be returned.

You can also call

procedure(). If this method is called, the result set will be returned.

At first, I had this problem. I did not submit in the form of bound parameters. If I wrote sql directly, I could not get the result set. Later, I added SET NOCOUNT ON; to my sql submission. I barely got the return. At the end of the article, I gave an example of the solution for the result set I initially obtained, but it was really diarrhea. You can take a look and don't complain.

class Agent extends Model
{
    public $Dbname = 'UserDBConn';
    public function GetIndirectAgentList($agentId,$strAccount,$strSuperior,$iPageIndex,$pagesize)
    {
        $conn = $this->Dbconn($this->Dbname);
        try{
            $TotalCount = 0;
            $res = $conn::query('exec [dbo].[Agent_GetAgentList] :agentId,:strAccount,:strSuperior,:iPageIndex,:pagesize,:TotalCount', [
                'agentId' => $agentId,
                'strAccount' => [$strAccount, PDO::PARAM_STR],
                'strSuperior' => [$strSuperior, PDO::PARAM_STR],
                'iPageIndex' => [$iPageIndex, PDO::PARAM_INT],
                'pagesize' => [$pagesize, PDO::PARAM_INT],
                'TotalCount' => [$TotalCount, PDO::PARAM_INPUT_OUTPUT],
            ]);
        }catch (PDOException $e)
        {
            return false;
        }
        return $res;
    }
}

The initial

Agent.php

Obviously the

@AgentID and @TotalCount will not be obtained here; He will only return the result set of Agent_GetAgentList

public function GetIndirectAgentList($agentId,$strAccount,$strSuperior,$iPageIndex,$pagesize)
    {
        $conn = $this->Dbconn($this->Dbname);
        try{
            $res = $conn->query('
                SET NOCOUNT ON;
                declare @AgentID int;
                declare @TotalCount int;
                exec [dbo].[Agent_GetAgentList] '.$agentId.',\''.$strAccount.'\',\''.$strSuperior.'\','.$iPageIndex.','.$pagesize.',@TotalCount output;
                select @AgentID as AgentID,@TotalCount as TotalCount
                ');
        }catch (PDOException $e)
        {
            return false;
        }
        return $res;
}

For more related ThinkPHP knowledge, please visit

ThinkPHP Tutorial!

The above is the detailed content of How thinkphp calls sqlserver stored procedure to return multiple result sets. For more information, please follow other related articles on the PHP Chinese website!