Introduction to how PHP uses PDO to call sqlserver stored procedures based on the Yii framework

This article mainly introduces the general method of PHP calling sqlserver stored procedures based on PDO, and analyzes the relevant operation steps and implementation skills of using pdo to call sqlserver stored procedures based on the Yii framework based on the example. Friends in need can refer to the following

The example in this article describes how PHP calls the sqlserver stored procedure based on PDO. Share it with everyone for your reference. The details are as follows:

Since the stored process on the business side has always been on sqlserver, we need to use php to call it. However, our local one is windows, and the online one is linux. I started using some mechanisms of the Yii framework to make calls and found that it was always good locally but not online. I looked for many solutions and finally found the pdo solution. The driver used locally is sqlsrv and dblib online. So You need to pay attention to the driver form when linking to pdo. When fetching the result set, note that windows and linux seem to be different. After I add set nocount on, if win directly fetches the result, it can get the final one. However, if it is put in linux, It’s all gone, I said angrily, I might as well take them all in the end;

Share a compiled method:

class StoredProcHelper
{
  private static $type = [
   'integer'=>PDO::PARAM_INT,
   'string'=>PDO::PARAM_STR,
   'null'=>PDO::PARAM_NULL,
   'boolean'=>PDO::PARAM_BOOL
  ];
  private $sql = '';//此变量在下方说明
  private $params = [];//此变量在下方说明
  private $connect_info;//此变量在下方说明
  private $pdo_connect;
  public function __construct($connect_info,$sql,$params){
    $this->sql = 'SET NOCOUNT ON;'.$sql;
    $this->params = $params;
    $this->connect_info = $connect_info;
    if(!empty($this->connect_info->dsn) && !empty($this->connect_info->username) && !empty($this->connect_info->password)){
      $this->pdo_connect = new PDO($this->connect_info->dsn,$this->connect_info->username, $this->connect_info->password);
    }
  }
  public function ExecuteProc(){
    $link = $this->pdo_connect->prepare($this->sql);
    foreach ($this->params as $key => $value){
      $link->bindParam($key,$value,self::$type[strtolower(gettype($value))]);
    }
    $link->execute();
    $i = 1;
    $res[0] = $link->fetchAll();
    while($link->nextRowset()){
      $res[$i] = $link->fetchAll();
      $i++;
    }
    return $res;
  }
}

Examples of use :

public static function Example($connect_info,$mobile){
    $sql='declare @customParam int;exec you_proc @Mobile = :mobile,@OutParam=@customParam out;select @customParam as outName;';
    $params = [
      ':mobile'=>$mobile
    ];
    $pdo = new StoredProcHelper($connect_info,$sql,$params);
    $res = $pdo->ExecuteProc();
    var_dump($res);
  }

The form of variables $sql and $params is as shown in the example;

The form of variable $connect_info is as follows [Because I am in the Yii framework It is used under Yii, so this variable is directly obtained from Yii to obtain the database link configuration. If it is different, you can change the form and assignment form yourself. What is convenient in the framework is that the configuration can be obtained separately in different environments by directly obtaining it. It’s sqlsrv and dblib, you don’t need to change it yourself]:

[
  'dsn' => 'sqlsrv:Server=xxxxxxxxxx;Database=xxxxx',
  'username' => 'xxxxx',
  'password' => 'xxxxxxxxxxxxxxxxxxxx',
  'charset' => 'utf8',
]
//或
[
  'dsn' => 'dblib:host=xxxxxxxxxx;dbname=xxxxx',
  'username' => 'xxxxx',
  'password' => 'xxxxxxxxxxxxxxxxxxxx',
  'charset' => 'utf8',
],

The above is the detailed content of Introduction to how PHP uses PDO to call sqlserver stored procedures based on the Yii framework. For more information, please follow other related articles on the PHP Chinese website!