FreeTDS Development Group

["ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>]

> 
> 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
> 

Well... there's no official 0.63 patch. 0.64 and HEAD are updated, there is a 
post 0.64 patch.

> 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???

I think so. 
The reason it's not so easy. You should solve with

mssql_query("UPDATE P SET c = '$var', n = N'$var' WHERE i = 1");

mssql seems to extract type doing some implicit conversions, then insert 
data. When mssql see in sql command something like 'xxx' it translate 'xxx' 
to a varchar using charset (I think charset sent by collation info or system 
charset for tds7), so you get a varchar (first conversion) then it insert in 
a nvarchar (in this case, second conversion).

> Is it storing nvarchar on disk differently from varchar really???
> 
> I'm really -really- puzzled.
> 

freddy77