Re: unicode

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.

I'm most certainly open to examples (re-creatable code/environment) 
where this is not needed, I was just reflecting what worked in my 
experience.

Here's what I have --
CREATE TABLE dbo.utf8test (
    textfield nvarchar(50) NOT NULL
)

<?php
$MSDB = mssql_connect($DBHost, $DBLogin $DBPassword) or die ("Could not 
connect to server\n");
mssql_select_db($DBDatabase);

mssql_query("INSERT INTO utf8test (textfield) VALUES ('<UTF8 Text>')"); 
//Japanese in this particular case.

$Result = mssql_query("SELECT * FROM utf8test");
print_r(mssql_fetch_row($Result));
?>

This always returns question marks, both CLI/CGI (browser) and in the 
table itself.
When I place the N in front of the quoted UTF8 text, it is stored and 
displayed properly.

Do you have different results, James?

Thanks for your help
Jason


James K. Lowden wrote:
> Jason Young (Morgon) wrote:
>   
>> SQL Server is a pain in regards to sending UTF strings, as you'll need 
>> to prefix all of your quoted strings with capital N, for example:
>> INSERT INTO utf8_table (utf8string) VALUES (N'???')
>>     
>
> I don't see what difference the 'N' prefix makes.  
>
> http://msdn.microsoft.com/en-us/library/ms179899.aspx
>
> 	"Unicode strings have a format similar to character strings but are
> preceded by an N identifier (N stands for National Language in the SQL-92
> standard). The N prefix must be uppercase. For example, 'Michél' is a
> character constant while N'Michél' is a Unicode constant. Unicode
> constants are interpreted as Unicode data, and are not evaluated by using
> a code page."
>
> Remember that Win32 clients use USC-2.  The SQL text is sent to the server
> as USC-2.  That is, the server sees 18 bytes:
>
> $ iconv -f iso8859-1 -t ucs-2 | hexdump -C 
> N'Michél'
> 00000000  4e 00 27 00 4d 00 69 00  63 00 68 00 e9 00 6c 00 
> |N.'.M.i.c.h...l.|
> 00000010  27 00
>
> No UTF-8 anywhere in sight.  
>
> I don't know how to interepret Microsoft's statement though.  *All* SQL
> text is transmitted as UCS-2.  When that 'e9 00' arrives, the server knows
> it's UCS-2, regardless of whether the string is preceded by a 'N' or not. 
> Once the constants are parsed out, each one has to be converted to the
> column's encoding, based on its "collation" (unless it's a UCS-2 column,
> of course).  So I don't see any "interpretation" happening based on the
> 'N' prefix.  
>
> FreeTDS of course has to convert from the client's encoding to UCS-2.  It
> will do so regardless of the data; the 'N' prefix means nothing to
> FreeTDS.  The result is precisely as above: the data arrive as UCS-2, and
> I see no effect of using the 'N' prefix or not.  
>
> --jkl
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
>