Getting data with UTF-8 charset from MSSQL server using PHP FreeTDS extension

Question

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?

score 31 · Answer 1 · answered Dec 14 '12 at 11:25

31

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

answered Dec 14 '12 at 11:25

mschraudolph

329
1
2
6

3

million of thanks to @user1903844. This solutions helped me with Ubuntu+MS SQL+PHP – Bakhtiyor Sep 03 '13 at 14:46
6

Version >= 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 for `freetds.conf` example: https://gist.github.com/johnkary/6643856 – John Kary Sep 20 '13 at 21:05
Yey, finally a working answer! Uncommented line, changed value to 7.0 and it worked. Didn't need the `ini_set` part. – Hannes Schneidermayer May 13 '14 at 14:10
1

Great. 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. – Marco Marsala Dec 11 '15 at 12:17
This still doesn't work for me on Xenial Xerus, PHP 7, MSSQL 2008, FreeTDS v0.91 – katalin_2003 Nov 10 '16 at 17:36

score 16 · Accepted Answer · edited Jan 30 '19 at 21:07

16

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.

edited Jan 30 '19 at 21:07

Giacomo1968

25,759
11
71
103

answered Nov 14 '12 at 11:27

Louis Huppenbauer

3,719
1
18
24

1

MS 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. – Henrik Jan 25 '14 at 16:26
You saved my life. I will answer my [question](https://stackoverflow.com/questions/47977752/invalid-column-name-column-accents-in-ms-sql-server/) on how to use it with CodeIgniter – José Manuel Blasco Dec 27 '17 at 10:48

score 16 · Answer 3 · edited Feb 26 '15 at 12:48

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 .= ')';

This solved it for me, combined with the conversions by @louis-huppenbauer. — barryvanveen, Jul 17 '17 at 08:58

score 15 · Answer 4 · answered Jun 17 '14 at 14:07

15

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.

answered Jun 17 '14 at 14:07

Magnus Johansson

151
1
2

1

I 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 – Shocklo Nov 10 '15 at 14:49
Awesome, thank you for this answer, I looked all over for a solution and this works perfectly. – Justin Hanley Mar 21 '16 at 17:09
I totally agree; this should be the accepted answer! – Stijnster Jun 15 '18 at 14:49
This answer is correct for the SQLSRV library from Microsoft but isn't correct for FreeTDS library which the author specifically asked about. – serverSentinel Nov 25 '20 at 22:32

score 2 · Answer 5 · answered Aug 14 '14 at 18:23

2

It seem version 7.0 or great is required. iconv() also seems to work well, but is tedious.

$query = $this->db->query($sql);
$result = $query->fetchAll(PDO::FETCH_OBJ);
foreach ($result as $row) {
    foreach (get_object_vars($row) as $key => $value) {
    $row->$key = (mb_detect_encoding($value, mb_detect_order(), true) === 'UTF-8') 
            ? $value : iconv('iso-8859-1', 'utf-8', $value);
    }
    $results[] = $row;
}

answered Aug 14 '14 at 18:23

jjwdesign

3,272
8
41
66

This works! I'm saying because sometimes people ignore answers without too much votes. – WhiteFloater Feb 11 '18 at 19:29

score 2 · Answer 6 · answered Jul 25 '15 at 07:21

2

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');

answered Jul 25 '15 at 07:21

Sina

431
4
7

score 0 · Answer 7 · answered Nov 02 '17 at 17:40

0

You should change your TDS version based on what version of SQL server your using. Check out the installation guide for details.

http://www.freetds.org/userguide/choosingtdsprotocol.htm

answered Nov 02 '17 at 17:40

bstory

852
9
28

score 0 · Answer 8 · answered Jan 21 '20 at 12:12

I used same as above but windows 1250, so:

$query = $this->db->query($sql);
$result = $query->fetchAll(PDO::FETCH_OBJ);
foreach ($result as $row) {
    foreach (get_object_vars($row) as $key => $value) {
    $row->$key = (mb_detect_encoding($value, mb_detect_order(), true) === 'UTF-8') 
            ? $value : iconv('windows-1250', 'utf-8', $value);
    }
    $results[] = $row;
}

And then it worked, but I use polish characters

Getting data with UTF-8 charset from MSSQL server using PHP FreeTDS extension

8 Answers8

Linked

Related