title: How to use PHP PDO ODBC with SQL Server and Unicode Characters?
body: <p>PHP PDO ODBC doesn't seem to be able to store NVARCHAR characters (UTF-16) without some manual encoding. This seems like a pretty commun "bug" all around the internet and no one seems to have a definitive solution.</p>
  
  <p><strong>How to reproduce the bug</strong></p>
  
  <ol>
  <li>Try to insert the following japanese characters using PDO : <code>こんにちは</code> (It means hello)</li>
  <li>The following will be stored in your database : <code>ã“ã‚“ã«ã¡ã¯</code></li>
  <li>Then retrieve it via PDO and print it on the screen, you will get : <code>こんにちは</code></li>
  </ol>
  
  <p>It's not so bad but it's not good either. PHP works but when you have other applications that are not in PHP that access that information from your database they will get the wrong string : <code>ã“ã‚“ã«ã¡ã¯</code>.</p>
  
  <p>Ideally you would want to have <code>こんにちは</code> everywhere.</p>
  
  <p><strong>The symptom</strong></p>
  
  <p>It seems that PDO doesn't have any notion of NVARCHAR, aka characters that are encoded using 16 bits. In fact, everything that you pass to or retrieve from SQL SERVER via PDO will be by chunk of 8 bits. How to "prove" it? Here it is :</p>
  
  <ol>
  <li>You start with your japanese string <code>こんにちは</code>.</li>
  </ol>
  
  <p>You first have to know that PHP consider string as binary and that (if it's set like that) it will store them in UTF-8. </p>
  
  <p>So if we look at the binary representation of <code>こんにちは</code>, you will get <code>E38193E38293E381ABE381A1E381AF</code> which is also the binary representation that SQL SERVER will give you for <code>ã“ã‚“ã«ã¡ã¯</code>. (depends on your collation)</p>
  
  <ol start="2">
  <li><p>Next, let's put it into UTF-16 because that's the format of NVARCHAR.</p>
  
  <p>$utf16_string = mb_convert_encoding('こんにちは', 'UTF-16LE');</p></li>
  </ol>
  
  <p>The following will change the binary representation in PHP of <code>こんにちは</code> to <code>533093306B3061306F30</code> which is exactly the binary representation of <code>こんにちは</code> in SQL SERVER NVARCHAR.</p>
  
  <ol start="3">
  <li>Next try to save it in SQL via PDO and you will get the following : <code>S0“0k0a0o0</code></li>
  </ol>
  
  <p>The SQL SERVER binary representation of <code>S0“0k0a0o0</code> in VARCHAR is <code>533093306B3061306F30</code> which is also the binary representation of <code>こんにちは</code> in NVARCHAR.</p>
  
  <p><strong>A dirty solution</strong></p>
  
  <p>You can use the following to save and retrieve unicode data in SQL SERVER via PDO ODBC but it's ugly...</p>
  
  <ol>
  <li><p>You want to transform the data into exactly the same binary representation that SQL SERVER NVARCHAR
  will store it</p>
  
  <p>mb_convert_encoding('こんにちは', 'UTF-16LE');</p></li>
  <li><p>You want to receive it as a binary on SQL SERVER side and then transform it to NVARCHAR.</p>
  
  <p>@binary VARBINARY(40)
  SELECT @string = CONVERT(NVARCHAR(20), @binary);</p></li>
  <li><p>At this point you have <code>こんにちは</code> in your database. To retrieve it you want to resend it to PHP as a binary</p></li>
  <li><p>Once you get the binary in PHP, PHP will already have transform it into a hex string... So, you want to convert the hex string into a binary and then change the encoding from utf-16 to utf-8</p>
  
  <p>$result = mb_convert_encoding(hex2bin($string), 'UTF-8', 'UTF-16LE');</p></li>
  </ol>
  
  <p>And you will be back with your <code>こんにちは</code> when you echo it to your webpage.</p>
  
  <p>Basically, that's what the SQL driver should be doing for me instead of me doing it manually. </p>
  
  <p><strong>Did I forgot to configure something or I have to do it manually?</strong></p>