Getting data with UTF-8 charset from MSSQL server using PHP FreeTDS extension
I had a similar problem and tried all settings i could find on the web - in vain.
In my case the problem was the configuration of FreeTDS itself. On Linux the file is /etc/freetds/freetds.conf
I had to change the Version to 7.0 (maybe other numbers work, too. i just tried 7.0)
[global]
# TDS protocol version
tds version = 7.0
After this, the driver seemed to accept changes of the charset like.
ini_set('mssql.charset', 'UTF-8');
Btw: the change immediately is in effect, no need to restart anything afterwards
MSSQL and UTF-8 are quite a pain in the ... sometimes. I had to convert it manually. The problem: MSSQL doesn't actually know and support UTF-8.
Convert from database value to UTF-8:
mb_detect_encoding($value, mb_detect_order(), true) === 'UTF-8' ? $value : mb_convert_encoding($value, 'UTF-8');
Converting from UTF-8 to database value:
mb_convert_encoding($value, 'UCS-2LE', mb_detect_encoding($value, mb_detect_order(), true));
Fortunately I was using Doctrine so all I had was to create a custom StringType implementation.
If you use freeTDS, you should change below lines on /etc/freetds/freetds.conf
:
[global]
# TDS protocol version
tds version = 4.2
To this:
[global]
# TDS protocol version
tds version = 8.0
;tds version = 4.2
and finally add this line:
# set charset
client charset = UTF-8
** clinet charset is in global [scope]
In your queries, you should use N character. like this:
$query = "INSERT INTO dbo.SMSOutbox (StationID, Dest, Text) VALUES ";
$query .= '(';
$query .= "'" . $this->stationId . "', ";
$query .= "'" . $this->destination . "', ";
$query .= "N'" . $this->text . "'";
$query .= ')';