Getting data with UTF-8 charset from MSSQL server using PHP FreeTDS extension
Solution 1
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.
Solution 2
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
Solution 3
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 .= ')';
Solution 4
You can also solve this issue by adding CharacterSet UTF-8 in the $connectionInfo before connecting to the DB.
$serverName = "MyServer";
$connectionInfo = array( "Database"=>"AdventureWorks", "CharacterSet" => "UTF-8");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
Worked fine NO additional encoding needed.
Solution 5
I had this problem and it solved by adding this line to my php script before connecting to MSSQL Server:
ini_set('mssql.charset', 'UTF-8');
Marius Grigaitis
Updated on July 07, 2022Comments
-
Marius Grigaitis almost 2 years
I can't seem to get data from MSSQL encoded as UTF-8 using FreeTDS extension.
Connecting:
ini_set('mssql.charset', 'UTF-8'); $this->_resource = mssql_connect($config['servername'], $config['username'], $config['password']);
I have no ability to use any other extension.
I've tried creating ~/.freetds.conf
[global] client charset = UTF-8
I've tried passing parameters to php:
php -d mssql.charset="UTF-8" index.php
Data is still not in UTF-8.
php -i
mssql MSSQL Support => enabled Active Persistent Links => 0 Active Links => 0 Library version => FreeTDS Directive => Local Value => Master Value mssql.allow_persistent => On => On mssql.batchsize => 0 => 0 mssql.charset => no value => no value mssql.compatability_mode => Off => Off mssql.connect_timeout => 5 => 5 mssql.datetimeconvert => On => On mssql.max_links => Unlimited => Unlimited mssql.max_persistent => Unlimited => Unlimited
Ideas?
-
Bakhtiyor over 10 yearsmillion of thanks to @user1903844. This solutions helped me with Ubuntu+MS SQL+PHP
-
John Kary over 10 yearsVersion >= 7.0 is required to set the config option
client charset = UTF-8
which might be why OP Marius Grigaitis's solution using it didn't work. See this gist forfreetds.conf
example: gist.github.com/johnkary/6643856 -
Aditya Sinha over 10 yearsMS SqlServer uses UCS2-LE encoding, not CP1252. Not ISO8859-1 (subset of CP1252) either. Not UTF-16 (came after UCS2) or 'Unicode' (WideChar/UTF-16 on Win) either.
-
Hannes Schneidermayer about 10 yearsYey, finally a working answer! Uncommented line, changed value to 7.0 and it worked. Didn't need the
ini_set
part. -
Shocklo over 8 yearsI cant believe that this is not the accepted answer, worked just fine and its so easy to do. if i could give you more than just +1 i would
-
Marco Marsala over 8 yearsGreat. Working! Probably FreeTDS version 7.0 supports automatic conversion between client format (UTF-8, ISO-8859-1 or something else) and UCS2-LE (the MSSQL charset), so this is transparent to the programmer.
-
Justin Hanley about 8 yearsAwesome, thank you for this answer, I looked all over for a solution and this works perfectly.
-
katalin_2003 over 7 yearsThis still doesn't work for me on Xenial Xerus, PHP 7, MSSQL 2008, FreeTDS v0.91
-
Admin almost 7 yearsThis solved it for me, combined with the conversions by @louis-huppenbauer.
-
José Manuel Blasco over 6 yearsYou saved my life. I will answer my question on how to use it with CodeIgniter
-
WhiteFloater over 6 yearsThis works! I'm saying because sometimes people ignore answers without too much votes.
-
Stijnster almost 6 yearsI totally agree; this should be the accepted answer!
-
serverSentinel over 3 yearsThis answer is correct for the SQLSRV library from Microsoft but isn't correct for FreeTDS library which the author specifically asked about.