Thursday, July 18, 2013

PHP, MSSQL, nvarchar (fetch and write UTF-8 with ODBC)

Today I found pretty annoying thing in PHP MSSQL ODBC driver (I use it via PDO).
This driver doesn't support nvarchar fields (or nvarchar(max) only, doesn't matter for me - I can't change database schema just because of using PHP).
After 2 hours of googling I found solution. It's hack, but it works fine with nvarchar, decimal, varchar, int and others.

To read 

I found it accidentally in this blog and I'm very thankful to David Walsh:
SELECT CAST(CAST([field] AS VARCHAR(8000)) AS TEXT) AS field FROM table

To write

It's from StackOverflow:
$value = 'ŽČŘĚÝÁÖ';
$value = iconv('UTF-8', 'UTF-16LE', $value); //convert into native encoding 
$value = bin2hex($value); //convert into hexadecimal
$query = 'INSERT INTO some_table (some_nvarchar_field)  VALUES(CONVERT(nvarchar(MAX), 0x'.$value.'))'; 

4 comments:

  1. This is a brilliant idea. It was a single solution for my MS SQL & PHP project!

    ReplyDelete
  2. Thanks for posting this: PHP+MSSQL+ODBC+PDO !
    In some fields there are no problems, but in other SELECT returns NULL. Pure magic.
    I'm fit with just a single cast for field nvarchar(20):
    SELECT CAST(field AS VARCHAR(40)) AS field FROM table

    ReplyDelete
  3. This still doesnt work for me. I am getting ??????? series of question marks instead of regional language characters stored in ms sql table, when i am retrieving it in sqlsrv_fetch_assoc and echoing them on screen. Please help

    ReplyDelete
  4. Thanks for posting this

    ReplyDelete