Encoding SQL_Latin1_General_CP1_CI_AS into UTF-8

Question

I'm generating a XML file with PHP using DomDocument and I need to handle asian characters. I'm pulling data from the MSSQL2008 server using the pdo_mssql driver and I apply utf8_encode() on the XML attribute values. Everything works fine as long as there's no special characters.

The server is MS SQL Server 2008 SP3

The database, table and column collation are all SQL_Latin1_General_CP1_CI_AS

I'm using PHP 5.2.17

Here's my PDO object:

$pdo = new PDO("mssql:host=MyServer,1433;dbname=MyDatabase", user123, password123);

My query is a basic SELECT.

I know storing special characters into SQL_Latin1_General_CP1_CI_AS columns isn't great, but ideally it would be nice to make it work without changing it, because other non-PHP programs already use that column and it works fine. In SQL Server Management Studio I can see the asian characters correctly.

Considering all the details above, how should I process the data?

Solution 1

I found how to solve it, so hopefully this will be helpful to someone.

First, SQL_Latin1_General_CP1_CI_AS is a strange mix of CP-1252 and UTF-8. The basic characters are CP-1252, so this is why all I had to do was UTF-8 and everything worked. The asian and other UTF-8 characters are encoded on 2 bytes and the php pdo_mssql driver seems to hate varying length characters so it seems to do a CAST to varchar (instead of nvarchar) and then all the 2 byte characters become question marks ('?').

I fixed it by casting it to binary and then I rebuild the text with php:

SELECT CAST(MY_COLUMN AS VARBINARY(MAX)) FROM MY_TABLE;

In php:

//Binary to hexadecimal
$hex = bin2hex($bin);

//And then from hex to string
$str = "";
for ($i=0;$i<strlen($hex) -1;$i+=2)
{
    $str .= chr(hexdec($hex[$i].$hex[$i+1]));
}
//And then from UCS-2LE/SQL_Latin1_General_CP1_CI_AS (that's the column format in the DB) to UTF-8
$str = iconv('UCS-2LE', 'UTF-8', $str);

Solution 2

I know this post is old, but the only thing that work for me was iconv("CP850", "UTF-8//TRANSLIT", $var); I had the same issues with SQL_Latin1_General_CP1_CI_AI, maybe it work for SQL_Latin1_General_CP1_CI_AS too.

Solution 3

You can try so:

header("Content-Type: text/html; charset=utf-8");
$dbhost   = "hostname";
$db       = "database";
$query = "SELECT *
    FROM Estado
    ORDER BY Nome";
$conn = new PDO( "sqlsrv:server=$dbhost ; Database = $db", "", "" );
$stmt = $conn->prepare( $query, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL, PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE => PDO::SQLSRV_CURSOR_BUFFERED, PDO::SQLSRV_ENCODING_SYSTEM) );
$stmt->execute();
while ( $row = $stmt->fetch( PDO::FETCH_ASSOC ) )
{
// CP1252 == code page Latin1
print iconv("CP1252", "ISO-8859-1", "$row[Nome] <br>");
}

Solution 4

No need for crazy stuff. Collation SQL_Latin1_General_CP1_CI_AS character encoding is: Windows-1252

This works perfect for me: $str = mb_convert_encoding($str, 'UTF-8', 'Windows-1252');

Solution 5

For me, none of the above was the direct solution--though I did use parts of above solutions. This worked for me with the Vietnamese alphabet. If you come across this post and none of the above work for you, try:

    $req = "SELECT CAST(MY_COLUMN as VARBINARY(MAX)) as MY_COLUMN FROM MY_TABLE"; 
    $stmt = $conn->prepare($req);
    $stmt->execute();
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $str = pack("H*",$row['MY_COLUMN']);
        $str = mb_convert_encoding($z, 'HTML-ENTITIES','UCS-2LE');
        print_r($str);
    }

And a little bonus--I had to json_encode this data and was (duh) getting html code instead of the special characters. to fix just use html_entity_decode() on the strings before sending with json_encode.

View more solutions

Comments

[joelpittet]

This one worked for me! Thank you: print iconv("CP1252", "UTF-8", "$row[Nome] <br>");

[Jo Smo]

None of the PDO::SQLSRV_* parameters work for me on SQL server 2008. I get some error, that it's undefined or something like that.

[icc97]

but.. but.. that's mental. There's really no better way?, not mb_convert_encoding

[Nilebac]

This was the only solution that worked for me for Korean & Russian.