James K. Lowden
jklowden at freetds.org
Tue Jul 22 19:08:15 EDT 2008
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
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.
More information about the FreeTDS