使用ODBC从MSSQL获取UTF-8

Question

I have problems selecting text from a MSSQL Database. I am connecting through ODBC with the SQL Server Native Client 10.0 driver. The Data is stored in a ntext column with UTF-8 characters and I need to output it correctly with PHP, if you are curious it's a download script. I've tried several tricks and tips like converting the character encoding but none of them really worked.

I have to mention that I am not able to change anything in the database.

<?php
odbc_connect();
odbc_execute();
odbc_fetch_row();
header("Content-type: application/octet-stream");
echo odbc_result(...., 'ntext-column');

Answer 1

Finally I got the solution. You have to cast your column to VARBINARY in the select query. If you have a ntext column you have to cast it to nvarchar first.

SELECT CAST(CAST(column AS NVARCHAR(MAX)) AS VARBINARY(MAX)) AS column FROM ...

Then you have to enable the binary mode for odbc and set the expected length of the binary data. Finally you have to convert the binary data from the charset UCS-2LE to UTF-8, then it's done.

...
odbc_execute($query, ...);
odbc_longreadlen($query, 1000000); // Set the expected length to 1 Megabyte
odbc_binmode($query, ODBC_BINMODE_RETURN); // Enable binary mode
... // Fetch the row
$data = iconv('UCS-2LE', 'UTF-8', odbc_result($query, 'column'));