freetds AT lists.ibiblio.org
Subject: FreeTDS Development Group
List archive
- From: "Jason Young (Morgon)" <morgon AT mygamercard.net>
- To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
- Subject: Re: [freetds] unicode
- Date: Tue, 22 Jul 2008 17:44:02 -0400
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
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
-
[freetds] unicode,
Murray, Laura (US), 07/22/2008
-
Re: [freetds] unicode,
Jason Young (Morgon), 07/22/2008
-
Re: [freetds] unicode,
James K. Lowden, 07/22/2008
-
Re: [freetds] unicode,
Jason Young (Morgon), 07/22/2008
- Re: [freetds] unicode, James K. Lowden, 07/22/2008
-
Re: [freetds] unicode,
Christos Zoulas, 07/22/2008
-
Re: [freetds] unicode,
James K. Lowden, 07/22/2008
-
Re: [freetds] unicode,
Christos Zoulas, 07/22/2008
-
Re: [freetds] unicode,
James K. Lowden, 07/22/2008
- Re: [freetds] unicode, ZIGLIO, Frediano, VF-IT, 07/23/2008
- Re: [freetds] unicode, James K. Lowden, 07/23/2008
- Re: [freetds] unicode, Murray, Laura (US), 07/23/2008
- Re: [freetds] unicode, Jason Young (Morgon), 07/23/2008
- Re: [freetds] unicode, James K. Lowden, 07/23/2008
-
Re: [freetds] unicode,
James K. Lowden, 07/22/2008
-
Re: [freetds] unicode,
Christos Zoulas, 07/22/2008
-
Re: [freetds] unicode,
James K. Lowden, 07/22/2008
-
Re: [freetds] unicode,
Jason Young (Morgon), 07/22/2008
-
Re: [freetds] unicode,
James K. Lowden, 07/22/2008
-
Re: [freetds] unicode,
Jason Young (Morgon), 07/22/2008
Archive powered by MHonArc 2.6.24.