Skip to Content.
Sympa Menu

freetds - Re: [freetds] are varchar and nvarchar actually the same? [Was: hex(92), freeTDS and MS SQL]

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] are varchar and nvarchar actually the same? [Was: hex(92), freeTDS and MS SQL]
  • Date: Mon, 29 Jan 2007 09:09:00 +0100

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





Archive powered by MHonArc 2.6.24.

Top of Page