FreeTDS Development Group

["James K. Lowden" <jklowden AT freetds.org>]

Jason Young (Morgon) wrote:
> 
> In my short experience (and through confirmation with SQL Server MVPs at
> 
> Microsoft), inserting UTF8 strings into nvarchar fields require N'' 
> prefixed strings.
> 
> I know that FreeTDS doesn't care about the N prefix, but when doing my 
> tests, I was only able to fully extract inserted 'UTF8' data when 
> inserting using the N prefix into an nvarchar field.  

Well, there are a lot of moving parts.  

I have no experience with ideographic scripts.  Using Microsoft's Query
Analyzer and sqsh, this works just fine with a varchar column:

        insert u values ('Å')

If that doesn't work for you, I'd be interested in seeing a TDSDUMP log to
make sure it's not a client-side configuration issue.  

The form N'utf8text' is *invalid* as far as the server is concerned.  The
server has no way of knowing the string is utf-8 encoded.  It *assumes* --
it has to assume something -- it's UCS-2 encoded.  

The server cannot store UTF-8 encoded text per se.  It can store it, just
as it stores a PDF file: as a blob.  But it can't index it or sort it or
compare it.  

When you do:

mssql_query("INSERT INTO utf8test (textfield) VALUES ('<UTF8 Text>')"); 

FreeTDS converts the SQL text from your encoding to UCS-2.  The server
never sees the UTF-8 data.  It should see UCS-2 data *if* your client
encoding is set to UTF-8.  

You can store random byte-pairs verbatim in an nvarchar column.  That's
what the server promises to do: not to infer an encoding   And they'll
sort and compare, just not meaningfully.  

--jkl