FreeTDS Development Group

[Lapo Luchini <lapo AT lapo.it>]

Lapo Luchini wrote:
> James K. Lowden wrote:
>> As I said at the top of this very long message, I think we both learned
>> something.  I just wish I like the answer better.  
> 
> I don't seem to be able to put non-CP1252 characters in a nvarchar,
> either. But I don't know if I want to be sure about that

"Curiousity is one of Satan's favorite paths, to lead man to sin."
  -- Tinel (just randomly found on wikiquote, but seems appropriate)

Server: SQL server 7
Using FreeTDS 0.63 with official patch to use CP1252 on server "iso1"
Client encoding: UTF-8

Problem: varchar and nvarchar show an identical behaviour regarding
"strange" characters, thus rending totally unpurposeful using the bigger
nvarchar.

Test database:
CREATE TABLE [P] (
        [i] [int] IDENTITY (1, 1) NOT NULL ,
        [c] [varchar] (50) NULL ,
        [n] [nvarchar] (50) NULL
)

Test script:
$var =
"\xC3\xA0\xE2\x80\x98\xE2\x80\x99\xE2\x80\x9C\xE2\x80\x9D\xC3\x85\xC5\xAE\xE7\xA7\x81";
mssql_query("UPDATE P SET c = '$var', n = '$var' WHERE i = 1");
echo 'W:' . h($var) . "\n";
$q = mssql_query('SELECT * FROM P WHERE i = 1');
$row = mssql_fetch_row($q);
echo 'C:' . h($row[1]) . "\n";
echo 'N:' . h($row[2]) . "\n";

Result:
W: c3 a0 e2 80 98 e2 80 99 e2 80 9c e2 80 9d c3 85 c5 ae e7 a7 81
C: c3 a0 e2 80 98 e2 80 99 e2 80 9c e2 80 9d c3 85 55 3f
N: c3 a0 e2 80 98 e2 80 99 e2 80 9c e2 80 9d c3 85 55 3f

I won't write any non-ASCII character for the fear that the
mail-mailinglist-newsgroup break it, but it is easy to convert to-from
"spaced hex UTF-8 representation" and "real characters" no this page:
http://people.w3.org/rishida/scripts/uniview/conversion.php

What I'm writing is (Unicode, UTF-8, description):
E0   - C3A0   - 'a' with acute accent
2018 - E28098 - left single quotation mark
2019 - E28099 - right single quotation mark
201C - E2809C - left double quotation mark
201D - E2809D - right double quotation mark
C5   - C385   - 'A' with ring above
016E - C5AE   - 'U' with ring above (outside CP1252)
79C1 - E7A781 - Japanese kanji for watashi (!CP1252)

with the meaning that "U with ring above" is a character just "sligthly
strange" while the Japanese character was meant to "go all the way into
character strangeness" (from the point of view of an US product such as
SQLServer7, that is - it's not at all strange for Japanese people of
course ^_^).

As you can see form the result everything is good up to "A with ring
above", that is: every character present in CP1252 is accepted well.
Things begin to go murky thereafter:
"U with ring above" really becomes "standard U" and
"watashi" becomes the usual "question mark".

What amazes me is the identical result from varchar and nvarchar fields:
is the the 'n' in 'nvarchar' a TOTAL WASTE OF SPACE in SQLServer7????

Let's freetds.log:

write.c:162:tds_put_string wrote 110 bytes
util.c:119:Changing query state from QUERYING to PENDING
net.c:673:Sending packet
0000 01 01 00 76 00 00 01 00-55 00 50 00 44 00 41 00 |...v.... U.P.D.A.|
0010 54 00 45 00 20 00 50 00-20 00 53 00 45 00 54 00 |T.E. .P.  .S.E.T.|
0020 20 00 63 00 20 00 3d 00-20 00 27 00 e0 00 18 20 | .c. .=.  .'.à.. |
0030 19 20 1c 20 1d 20 c5 00-6e 01 c1 79 27 00 2c 00 |. . . Å. n.Áy'.,.|
0040 20 00 6e 00 20 00 3d 00-20 00 27 00 e0 00 18 20 | .n. .=.  .'.à.. |
0050 19 20 1c 20 1d 20 c5 00-6e 01 c1 79 27 00 20 00 |. . . Å. n.Áy'. .|
0060 57 00 48 00 45 00 52 00-45 00 20 00 69 00 20 00 |W.H.E.R. E. .i. .|
0070 3d 00 20 00 31 00      -                        |=. .1.|

And that really confirms that what is sent, is correct (in this case
UCS2 = Unicode, as there's any character not representable in a single
UCS2 16 bit value).

What's going on there, then?
Is SQLServer7 purposefully converting what it is received in CP1252 only
to be converted back to UCS2 thereafter???
Is it storing nvarchar on disk differently from varchar really???

I'm really -really- puzzled.