PHP General Method for Calling sqlserver Stored Procedures Based on PDO [Based on Yii Framework]

  • 2021-08-10 07:07:13
  • OfStack

This article illustrates how PHP calls sqlserver stored procedures based on PDO. Share it for your reference, as follows:

Since the business side stored procedure 1 is directly above sqlserver, So call it with php, However, our local one is windows. And online is linux, 1 started to use Yii framework 1 some mechanisms to call found that local 1 straight is good, but to the line is not good, I found a lot of solutions, Finally, I found the scheme of pdo, and the driver used locally is dblib on sqlsrv line, so I need to pay attention to the driving form when linking pdo. When taking the result set, I pay attention to the difference between windows and linux. After I add set nocount on, if win directly takes the result, I can get the final one, but it is gone when I put it in linux. It is infuriating to say that I simply take all of them at last.

Share one method after finishing:


class StoredProcHelper
{
  private static $type = [
   'integer'=>PDO::PARAM_INT,
   'string'=>PDO::PARAM_STR,
   'null'=>PDO::PARAM_NULL,
   'boolean'=>PDO::PARAM_BOOL
  ];
  private $sql = '';// This variable is described below 
  private $params = [];// This variable is described below 
  private $connect_info;// This variable is described below 
  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 variables $sql and $params take the form shown in the example;

The variable $connect_info is of the following form: "Because I am using it under the Yii framework, Therefore, this variable is directly obtained according to Yii to obtain the database link configuration. If you are different, you can change the form and assignment form by yourself. It is convenient in the framework to obtain the configuration directly under different environments, which can obtain sqlsrv and dblib respectively, without changing it by yourself. ":


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

For more readers interested in PHP related contents, please check the topics on this site: "Summary of PHP Database Operation Skills Based on pdo", "Summary of php+Oracle Database Programming Skills", "Encyclopedia of PHP+MongoDB Database Operation Skills", "Introduction to php Object-Oriented Programming", "Introduction to php String (string) Usage", "Introduction to php+mysql Database Operation Skills" and "Summary of php Common Database Operation Skills"

I hope this article is helpful to everyone's PHP programming.


Related articles: