PHP generic method for calling sqlserver stored procedures based on PDO [based on Yii framework], pdoyii

PHP generic method for calling sqlserver stored procedures based on PDO [based on Yii framework], pdoyii

This example describes how PHP calls the SQL Server Stored Procedure Based on PDO. We will share this with you for your reference. The details are as follows:

Because the stored procedures on the business side are always on SQL Server, we need to use php to call it. However, we use windows locally, while online systems use linux, at the beginning, I used some mechanisms of the Yii framework to call and found that it was always good locally. However, I couldn't do it online. I found many solutions and finally found the pdo solution, the local driver is sqlsrv online and dblib. Therefore, pay attention to the driver format when connecting to pdo. Note that windows and linux seem to be different when obtaining the result set, after I add set nocount on, win can get the final result if the result is obtained directly. However, if I put it in linux, I will not be able to get the final result. In the end, I will retrieve all the results;

One method for sharing and sorting is as follows:

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 describes private $ params = [] below; // This variable describes private $ connect_info below; // This variable describes private $ pdo_connect; public function _ construct ($ connect_info, $ SQL, $ params) below) {$ this-> SQL = 'set NOCOUNT ON ;'. $ SQL; $ this-> params = $ params; $ this-> co Nnect_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 ;}}

Example:

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 $ SQL and $ params variables are shown in the example;

The form of the variable $ connect_info is as follows [because I am using the Yii framework, this variable is directly obtained based on Yii for database link configuration, if you have different options, you can change the form and value assignment form. In the framework, you can obtain sqlsrv and dblib in different environments without changing them ]:

['Dsn '=> 'sqlsrv: Server = xxxxxxxxxx; Database = xxxxxx', 'username' => 'xxxxxxxxxxxxxxxxxxxx ', 'charset' => 'utf8',] // or ['dsn '=> 'dblib: host = xxxxxxxxxx; dbname = xxxx', 'username' => 'xxxxxx ', 'Password' => 'xxxxxxxxxxxxxxxxxxxxxxxx', 'charset' => 'utf8',],