Store UTF8 in Microsoft SQL Server

Yii 2.0 General Discussions
1

I am developing a web application with Yii2, that uses SQL Server 2012 as database (via ODBC). The application must generate HTML encoded in UTF8. All my source files are also UTF8 encoded.

I have created a migration that creates and fills a "countries" table:




$this->createTable("countries", [

	"id" 		=> "int not null",

	"country" 	=> "string(200) default ''",

	"pais" 		=> "string(150) default ''",

	"iso" 		=> "string(150) default ''",

	"sepa" 		=> "string(150) default ''",

	"isoBic"	=> "string(2) default ''",

	"isoIban"	=> "string(2) default ''"

]);


$this->batchInsert('countries',['id','country','pais','iso','sepa','isoBic','isoIban'],[

	["205","SPAIN","ESPAÑA","ES","1","ES","ES"],

	["154","NETHERLANDS","PAÍSES BAJOS","NL","1","NL","NL"]

]);

After executing the migration, I use the "Microsoft SQL Server Management Studio" client to get the contents of the countries table:




id	country	        pais	        iso	sepa	isoBic	isoIban

205	SPAIN	        ESPAÑA	        ES	1	ES	ES

154	NETHERLANDS	PAÍSES BAJOS	NL	1	NL	NL

As you can see, some characters are not properly stored.

After some research, I have found that MS SQL uses UTF-16 instead of UTF-8 to store Unicode characters.

I have tried to set the connection encoding through the Connection::$charset property, in the db.php config file:




'db' => [

    'dsn' => 'odbc:mydatabase',

    'driverName' => 'sqlsrv',

    'username' => 'aname',

    'password' => 'somepass',

    'charset' => 'utf16'

]

This had no effect. In fact, the documentation of Connection::$charset says:

"The property is only used for MySQL, PostgreSQL and CUBRID databases. Defaults to null, meaning using default charset as specified by the database."

My question is:

¿Is there a way to configure the database connection in Yii2 so the queries executed by Migration and ActiveQuery are automatically converted between UTF8 and UTF16?