Translations from sql server


I am trying to use the CDbmessageSource with sql server. If I retrieve the translation outside of the framework, it looks like this: actualización. Using Yii::t it looks like this: actualizaci�n. The message and translation columns are ntext to accomodate for utf8. Notice I am converting the ntext coluns to text columns in the query otherwise I would get sql server error.

Non Yii code:

$mssql = new PDO(‘dblib:host=JUSSLCITWS02;dbname=dbname’,‘user’,‘pass’);


$msquery= "select convert(text,translation) as translation from message";

$stmt= $mssql->query($msquery);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

      echo "<li>" . $row['translation'] . "</li>\n";


Yii code:


SELECT convert(text,t1.message) AS message, convert(text,t2.translation) AS translation

FROM {$this->sourceMessageTable} t1, {$this->translatedMessageTable} t2

WHERE AND t1.category=:category AND t2.language=:language







  foreach(&#036;rows as &#036;row){



Can anyone explain why the translation is showing funny?(actualizaci�n)


I believe I found the cause.

As far as I know, the only way to retrieve ntext values from the sql server db is to cast them to text or in the case of nvarchar to varchar.

Yii puts this line in the head tag: <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> Which causes utf8 characters cast to text to display funny.

I need the pages in utf8, so how do I get around this wonderful feature of sql server?

I converted the ntext columns to nvarchar columns and called utf8_encode on the result set. That is correctly displaying the translations.

Is this the correct way to handle this? It seems kind of hackish.


I had a similar problem with Mysql.

You have to run this query before retriving any data from db:


You can put this in db config like that:

			'connectionString' => 'mysql:host=localhost;dbname=db',

			'username' => 'root',

			'password' => 'root',

			'charset' => 'utf8',

			'initSQLs'=>array('SET NAMES utf8')

For encode you should always remember to:

  • use the tag <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

  • encode all your file in utf-8 without bom

  • use db in utf-8 with collation utf-8-general-ci

  • set charset utf8

  • SET NAMES utf8

With all this precautions all works fine.

Thanks zaccaria for your reply,

I’m somewhat new to sqlserver. Does anyone have a sqlserver version of these recommendations?

- use db in utf-8 (I’m using nvarchar, is there anything else I should do?)

- collation utf-8-general-ci (I currently have SQL_Latin1_General_CP1_CI_AS, utf-8-general-ci is not an option)

- set charset utf8 (does not work in sql server)

- SET NAMES utf8 (does not work in sql server)


When I see correct in db interface and then incorrect in Yii page (or viceversa) I know that a query ‘SET NAMES utf8’ is needed in mysql.

I don’t know the equivalent for mysql, but I can advice a dirty solution (with whom I worked fine for 2 years).

You can try building a simple interface for instert your translations with an Yii interface. Like that there will always been the mismatch, but at least the translation will be correctly shown in your application and incorrectly in db.

That is not a solution, but allows you to move forward.

In our languages (I am italian) there are only a few not-ascii character, and so you can even don’t care of it (like I did for years).

But when you go working in russia… you have to learn how to work correctly with encoding


Thanks again zaccaria,

At first I tried adding utf8-encode on the result and that correctly displayed the funny characters.

I did all sorts of things trying to get things working one of which was adding this to the connectstring: ;CharacterSet=UTF-8’.

Something I did caused the translations to come through correctly so that I did not need to utf8-encode the output.

Thanks again for your comments.

initSQLs does not seem to come with Yii 1.1 and returns: Property "CDbConnection.initSQLs" is not defined. (/home/yourownb/framework/base/CComponent.php:173) Is there a work around for this older version of Yii?

I am having an issue with German names such as Köln and cannot match them against DB records and I think initSQLs is what I need but is not supported by Yii 1.1



initSQLs is available since Yii 1.1.1 -

can’t you upgrade to that version?

Hmm. charset should actually do that for you. But it’s only used for sqlite, despite the comment tells otherwhise.


     * Initializes the open db connection.

     * This method is invoked right after the db connection is established.

     * The default implementation is to set the charset for MySQL and PostgreSQL database connections.

     * @param PDO the PDO instance


    protected function initConnection($pdo)


        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        if($this->emulatePrepare && constant('PDO::ATTR_EMULATE_PREPARES'))





                $pdo->exec('SET NAMES '.$pdo->quote($this->charset));




            foreach($this->initSQLs as $sql)




Looks like a bug to me.

Application is too big to re-write. But added the initSQLs check to my older framework. I think its not too risky to do so despite it is against Yii best practices…works as a charm with 1.1.0 now.

Mike could be quite right that it should have been handled by the charset.



Why rewrite? If you already work with 1.1.x upgrading should be seamless. Check the notes:

In the upgrade notes [ ] under the section Upgrading from v1.1.0 the second paragraph reads:

That’s what scares me! If you think that all my current DAO and AR will work on the new skeleton I might be willing to upgrade…otherwise I will leave it for some time in the future.



As I remember that was the time when the filtering was introduced in CGridView and for that the model had to have the search function…

Anyway all your old models and controller will work… it’s just that if you make new models/crud - they will be a bit different…

If you don’t want all this… you can take only the CDbConnection.php.