PHP, ODBC, and nvarchar

By  on  

I stumbled upon an odd error using PHP's ODBC functions to query a SQL Server 2005 database. I was doing a basic SELECT statement to get the description of something when I encountered the following error:

Warning: odbc_exec() [function.odbc-exec]: SQL error: [unixODBC][FreeTDS][SQL Server]Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier., SQL state in SQLExecDirect in /home/web/file.php on line 4

It turns out that the PHP ODBC functions have a hard time pulling "nvarchar" data. Here's the ugly solution to getting nvarchar data:

SELECT CAST(CAST([DetailedDescription] AS VARCHAR(8000)) AS TEXT) AS ad FROM mytable WHERE active = 1

Not pretty but making it function is what counts.

Recent Features

  • By
    Responsive Images: The Ultimate Guide

    Chances are that any Web designers using our Ghostlab browser testing app, which allows seamless testing across all devices simultaneously, will have worked with responsive design in some shape or form. And as today's websites and devices become ever more varied, a plethora of responsive images...

  • By
    CSS Filters

    CSS filter support recently landed within WebKit nightlies. CSS filters provide a method for modifying the rendering of a basic DOM element, image, or video. CSS filters allow for blurring, warping, and modifying the color intensity of elements. Let's have...

Incredible Demos

  • By
    MooTools 1.2 OpenLinks Plugin

    I often incorporate tools into my customers' websites that allow them to have some control over the content on their website. When doing so, I offer some tips to my clients to help them keep their website in good shape. One of the tips...

  • By
    Check All/None Checkboxes Using MooTools

    There's nothing worse than having to click every checkbox in a list. Why not allow users to click one item and every checkbox becomes checked? Here's how to do just that with MooTools 1.2. The XHTML Note the image with the ucuc ID -- that...

Discussion

  1. THANKS!!!
    muchas gracias.. ahora puedo seguir trabajando tranquilo.. justamante estaba teniendo problemas con un campo nvarchar.

  2. umberleigh

    Casting to varchar limits you to 8000 characters in a field.

    I found just casting to text works better, like so:

    print("SELECT CAST([column_name] AS TEXT) AS column_0 FROM table_name");
  3. Thanks , nice solve problem from sql server

  4. San

    Cast did the job ….

    Nice post !!!

  5. mohi

    not work!

    Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near ‘ASâ’. (severity 15) in /var/www/vhosts/fvc.ir/httpdocs/mssql.php on line 11

  6. Thank you very very much!
    And here is how to write value:
    http://stackoverflow.com/questions/7255703/utf-8-in-sql-server-2008-database-php

    $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.'))';
    
  7. thanks a lot. i was not shure what was the problem. In the sql management studio the data looks fine and in php is not. my first impresion was user rights for the data .. but after 1 hour of hair pulling :) i wandered if culd be the odbc … and like this i found your post

    thanks again

  8. Riyas

    Thanks for this solution. I was stuck at this problem and finally reached here

Wrap your code in <pre class="{language}"></pre> tags, link to a GitHub gist, JSFiddle fiddle, or CodePen pen to embed!