Sunday, December 29, 2013

Yii Framework: Using multiple db connections in a controller method

I have a mixed database where the majority of the tables are UTF-8 encoding but there are a few tables that are required to store the data in a specific encoding that this not UTF-8. 

In Yii you are defining you global database connection in the protected/config/main.php file, and this is global for the entire web application.This is causing some challenges for me since it is very important that I deliver data bitwise compatible according to the data stored in the table. 

I was not able to find any solution to this in my first few Google attempts, so I had to dive into the code.

First I tried in the controller action method by changing the charset on the global CDbConnection directly using 


this did not work since the database connection is already initialized at this point.

Going through the Yii Framework source code I first found CApplication->getDB(), then searching for the function getComponent() lead me to CModule->getComponent().

My next though was "If there is a getComponent(), I wonder if there is not a setComponent() function as well?" - and behold CModule->setComponent().

Armed with this knowledge it was easy to change the connection temporarily to use a new initialized database connection with a different charset.

public function loadModel($id)
    // Save the original database connection for later reuse
    $originalDbConnection = Yii::app()->db;
    // Create a new database connection based on the original database connection
    // change the charset to latin1
    $latin1DbConnection = Yii::createComponent(array(      
        'connectionString' => Yii::app()->db->connectionString,
        'emulatePrepare' => Yii::app()->db->emulatePrepare,
        'username' => Yii::app()->db->username,
        'password' => Yii::app()->db->password,
        'charset' => 'latin1',

    // Set the application wide database connection for this Apache/PHP webrequest to use this special database connection
    // Do the queries against the database
    $model = $someModel->findByPk($id);
    // Restore the original database connection 
    // note: 
    // This is actually not needed in this simple case, due to the fact that Apache/PHP rebuilds the entire web application on each request
    // Since I am not doing additional request in this logic here after returning the result everything is torn down and discarded.
    // However I have added it as part of this code for completeness and also in case someone else out there is doing more complex logic with multiple 
    // request that needs different charsets

        throw new CHttpException(404,'The requested page does not exist.');
    return $model;