Character encoding issue with PDO_ODBC

Question

When accessing a Microsoft SQL Database from PHP using PDO_ODBC with the following code, I have an encoding issue. When outputed the text from the DB is garbled.

$dsn = "odbc:DRIVER={SQL Server};SERVER=$hostname;DATABASE=$database;charset=UTF-8";
$pdo = new PDO($dsn,$username,$password);
$sql = "SELECT text FROM atable";
$result = $PDO->query($sql);
while($data = $result->fetchObject()){
  $values[] = $data->text; 
}
dpm($values);

_{(source: bayimg.com)}

This is done from a Drupal module. Everything in Drupal is made to work with UTF-8. The cleanest solution would be to able to retrieve the data from the database in UTF-8 or to convert it UTF-8 before outputting.

I tried these without any success

• $dsn = "odbc:DRIVER={SQL Server};SERVER=$hostname;DATABASE=$database;client_charset=utf-8"
• $dsn = "odbc:DRIVER={SQL Server};SERVER=$hostname;DATABASE=$database;charset=utf-8"
• $pdo->exec('SET NAMES utf8') after new PDO(...)
• $pdo->exec('SET CHARACTER SET utf8'); after new PDO(...)

PS: The code is currently developped on Windows but it has to work on GNU/Linux too.

Gilt

Answer 1

When running on Linux and using the FreeTDS driver, the charset for the client can be configured with the client charset setting in the freetds.conf file. In order for the freetds.conf file to be used when using PDO ODBC and unixODBC, one needs to configure the ODBC datasource using ODBC-combined configuration. When ODBC-only configuration is used to configure the ODBC datasource, the file freetds.conf is not used. With this I was able to retrieve and insert UTF-8 data from/into the MS SQL Server database.

Being a Linux/Unix guy, I was unable to understand/find a way how to configure the charset used when PDO ODBC is used on Windows. My vague understanding is that when configured at the system level, an ODBC datasource can be configured to use either de SQL Server database's charset or convert to the client computer charset.

Gilt

If I use the ODBC-only configuration and connect with PHP PDO odbc, I can't get UTF-8 data. – Sematic

Answer 2

When setting the character encoding to UTF-8, you will also need to encode your queries as UTF-8 and decode the results. The charset is telling the driver that you speak UTF8 natively. As such, you need to convert the UTF8 back to what PHP understands (ASCII or mbstring).

$dsn = "odbc:DRIVER={SQL Server};SERVER=$hostname;DATABASE=$database;charset=UTF-8";
$pdo = new PDO($dsn,$username,$password);
$sql = utf8_encode("SELECT text FROM atable");
$result = $PDO->query($sql);
while($data = $result->fetchObject()){
  $values[] = utf8_decode($data->text); 
  // possibly also: $values[] = utf8_decode($data[utf8_encode('text')]);
}
dpm($values);

Delacourt

Well, as said, this is done from a Drupal module. Drupal expect all strings to be UTF-8 encoded, it also outputs its pages with their encoding set to UTF-8. Once the ODBC datasource is configured to use UTF-8 (see my answer), PDO returns UTF-8 encoded data properly outputted with the dpm() function. Calling utf8_decode() on the data from PDO will produce garbled outputs since the string will be displayed in an UTF-8 encoded page. – Gilt

Answer 3

You can set desired character encoding with ClientCharset attribute (without a space) in connection string. In my case it was UTF-8.

$connection_string = "DRIVER={FreeTDS};SERVER={$serverip};Port=1433;DATABASE={$dbname};ClientCharset={UTF-8}";

$conn = odbc_connect($connection_string,$username,$pwd);

Please refer to following page for details..

https://www.freetds.org/userguide/dsnless.html

Raddatz

Answer 4

You can use this code to fix your problem:

$result = odbc_exec($this->con, $sql);    
$data = fetch2Array($result);

 private function fetch2Array($result){    
    $rows = array();

    while($myRow = odbc_fetch_array( $result )){ 
        $rows[] = $this->arrayToUTF($myRow);
    }
    return $rows;
}

private function arrayToUTF($arr){
    foreach ($arr as $key => $value) {
        $arr[$key] = utf8_encode($value);
    }
    return $arr;
}

Tripinnate

The question request for a solution with PDO, this answer is not using PDO. Also, the suggested solution is redundant with Veggivore's one fron years ago (ie. convert the values with utf8_encode()). – Gilt

Answer 5

You can use this code to fix your problem:

First Post Data Convert

'$word = iconv("UTF-8","Windows-1254",$_POST['search']);'

And Read Data Convert

while($data = $result->fetchObject()){
  $values[] = iconv("Windows-1254", "UTF-8",$data->text));
}

SQL String

$sql = "SELECT * FROM yourtables WHERE text LIKE '%{$word}%'";
or
$sql = "SELECT * FROM yourtables";

Camarena