Yii2 best Implementation when you want to consume MS SQL stored procedure

Question

I need advice on the best way to use yii2 with stored procedures. As this area is very grey.

I currently have a Yii 1 project implementation and like any developer, we are always looking out for new ways to speed up and write better code than we did the previous day.

I want to start off and port my new app to yii2 and was wondering what is the best way to consume stored procedure as that is the architecture currently in the organization and as much as I would with to interact with the database directly using Yii Active records it just impossible.

So in my current Yii 1 project I have created a class in component and imported it in config/main.php

'import' => array(
            'application.models.*',
            'application.components.SqlResource.*',
        ),

Then i proceeded to construct my class as

class SqlResource extends CApplicationComponent {

    const LOG_CAT = "ext.SqlResource";

    public $resources = array();
    public $db;
    public $mssql;

    /**
     * __construct
     *
     * @access public
     * @return void
     */
    public function __construct() {
        $serverUrl = Yii::app()->params->mssql['host'];
        $serverUser = Yii::app()->params->mssql['user'];
        $serverPass = Yii::app()->params->mssql['password'];
        $serverDb = Yii::app()->params->mssql['db_name'];
        try {
            $this->mssql = new PDO('dblib:host=' . $serverUrl . ';dbname=' . $serverDb, $serverUser, $serverPass);
            $this->mssql->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (PDOException $e) {
            Yii::log("Connection Error: @(DB connection was unavailable )[" . $e->getMessage() . "] ", "error", self::LOG_CAT);
            die($e->getMessage());
        }
    }


/**
 *
 * @param type $data array containg username and password from the login form
 * @return array
 */
public function ExampleSqlResourceFunction($username, $password) {
    if (!empty($username) && is_string($username)) {
        $procedure = "exec login_access @username = :username, @password = :password"; //Procedure to be called
        $query = $this->mssql->prepare($procedure); //PDO prepare a query for execution  using bind parameters to avaid leve 1 and level 2 sql injection
        $start = array_sum(explode(' ', round(microtime(TRUE) * 1000))); //start time to  calculate the time it takes to execute a query. the log time is logged for debugging process
        $query->bindValue(":username", $username, PDO::PARAM_STR); //bind the alias username from the prepared statment to the actual value and specify the datatype for this variable
        $query->bindValue(":password", $password, PDO::PARAM_STR); //bind the alias password from the prepared statment to the actual value and specify the datatype for this variable
        $execute = $query->execute(); //execute the query
        $stop = array_sum(explode(' ', round(microtime(true) * 1000))); //stop time to  calculate the time it takes to execute a query. the log time is logged for debugging process
        $totalMs = substr(($stop - $start), 0, 5); //total ms it took to execute the query
        $array = array(); //declare the return $array as an array
        if ($execute == TRUE) {//If query executes successfully return $return array $results
            $key_column = null; //format the retun array
            while ($obj = $query->fetch(PDO::FETCH_ASSOC)) {
                isset($obj[$key_column]) ? $array[$obj[$key_column]] = $obj : $array[] = $obj;
            }//log the how long it took to execute the query and the trace which procedure was executed
            Yii::log("Took $totalMs ms to fetch Login Details result set", "info", self::LOG_CAT);
            Yii::log("[login] " . '" ' . $procedure . '"', "trace", self::LOG_CAT);
            return $array;
        } else {
            $results = 'not execute';
            return $results;
        }
    }
}

After that i initialize my SqlResouce in my controller as follows

public function actionExampleControllerAction() {
        $sql = new SqlResource();
        $results = $sql->ExampleSqlResourceFunction();
        if (isset($results) && !empty($results)) {
            foreach ($results as $key => $value) {
                $array[$key] = array(
                    'type' => 'column',
                    'name' => $value["Department_Name"],
                    'value' => $value['Count'],
                );
            }
        }
        echo json_encode($array, JSON_NUMERIC_CHECK);
        Yii::app()->end();
    }

Answer 1

Use createCommand function inside Yii2 app.

$result = \Yii::$app->db->createCommand("exec login_access @username = :username, @password = :password") 
                  ->bindValue(':username' , $username)
                  ->bindValue(':password', $password)
                  ->execute();

To change db to another just create another component like dbMS

      //Your MySql db
        'db'=>
            [
                'class' => 'yii\db\Connection',
                'dsn' => 'mysql:host=localhost;dbname=my_database',
                'username' => 'root',
                'password' => '',
                'charset' => 'utf8',
            ],
        //Your MS SQL db
        'dbMS'=>
            [
                'class' => 'yii\db\Connection',
                'dsn' => 'dblib:host=mssqlserver;dbname=my_database',
                'username' => 'sa',
                'password' => 'superpass',
                'charset' => 'utf8',
            ],

So now you can easily and dynamically change your MySQL db to MSSQL dbMS on runtime.

$result = \Yii::$app->dbMS->createCommand("exec login_access @username = :username, @password = :password") 
                  ->bindValue(':username' , $username)
                  ->bindValue(':password', $password)
                  ->execute();