FreeTDS Development Group

["James K. Lowden" <jklowden AT schemamania.org>]

On 29 Mar 2003 21:06:06 +0100, Frediano Ziglio <freddyz77 AT tin.it> wrote:
> Tried with
>   perl -e 'print "\x9f"' | iconv -f cp1252 -t ucs2 | hexdump
> like commands, these bytes came from cp1252 -> ucs conversions.
> Your sql server store characters in cp1252, not in iso-8859-1

Thanks, Freddy, I see now.  For anyone following along, here's an easier
way to look at it.

Start with http://czyborra.com/charsets/codepages.html and download two
files:

        http://czyborra.com/charsets/cp1252.txt.gz and
        http://czyborra.com/charsets/iso8859-1.txt.gz
then
        $ diff --side-by-side --suppress-common-lines \
                cp1252.txt iso8859-1.txt
to get:

=80     U+20AC  EURO SIGN
        [no 81]
=82     U+201A  SINGLE LOW-9 QUOTATION MARK
=83     U+0192  LATIN SMALL LETTER F WITH HOOK
=84     U+201E  DOUBLE LOW-9 QUOTATION MARK
=85     U+2026  HORIZONTAL ELLIPSIS
=86     U+2020  DAGGER
=87     U+2021  DOUBLE DAGGER
=88     U+02C6  MODIFIER LETTER CIRCUMFLEX ACCENT
=89     U+2030  PER MILLE SIGN
=8A     U+0160  LATIN CAPITAL LETTER S WITH CARON
=8B     U+2039  SINGLE LEFT-POINTING ANGLE QUOTATION MARK
=8C     U+0152  LATIN CAPITAL LIGATURE OE
        [no 8D]
=8E     U+017D  LATIN CAPITAL LETTER Z WITH CARON
        [no 8F, 90]
=91     U+2018  LEFT SINGLE QUOTATION MARK
=92     U+2019  RIGHT SINGLE QUOTATION MARK
=93     U+201C  LEFT DOUBLE QUOTATION MARK
=94     U+201D  RIGHT DOUBLE QUOTATION MARK
=95     U+2022  BULLET
=96     U+2013  EN DASH
=97     U+2014  EM DASH
=98     U+02DC  SMALL TILDE
=99     U+2122  TRADE MARK SIGN
=9A     U+0161  LATIN SMALL LETTER S WITH CARON
=9B     U+203A  SINGLE RIGHT-POINTING ANGLE QUOTATION MARK
=9C     U+0153  LATIN SMALL LIGATURE OE
        [no 9D]
=9E     U+017E  LATIN SMALL LETTER Z WITH CARON
=9F     U+0178  LATIN CAPITAL LETTER Y WITH DIAERESIS

Note these conform precisely to what I found in the same range:

> ac20 8100 1a20 9201 1e20 2620 2020 2120 
> c602 3020 6001 3920 5201 8d00 7d01 8f00
> 9000 1820 1920 1c20 1d20 2220 1320 1420
> dc02 2221 6101 3a20 5301 9d00 7e01 7801

What's going on?  What does all this eyeblearing hexdumping tell us?  

I had created a string consisting of characters whose values were 0-255,
and bcp'd that string into a table with a single varchar(256) column.  I
then inserted that string into another table, defined as a single
nvarchar(256) column, allowing SQL Server to perform the conversion.  I
*thought* I had stored the Latin1 (ISO-8859-1) character set, and expected
that viewing my new nvarchar field as hex (with cast() on the server) I
would see 256 16-bit integers in the same 0-255 range.  

[If you've read Appendix B and the above doesn't make sense and you'd like
clarification, just say so.  I'm bothering to write this so interested
parties can see what we're dealing with.]

What I had in fact stored was not Latin1 but Microsoft's CP1252, which
unlike Latin1 uses the range 0x80-0x9F for many useful characters.  In
converting my varchar to nvarchar, the server converted its native CP1252
to UCS-2; it of course had to provide the appropriate USC-2 values for
characters in that 0x80-0x9F range.  

Example: The 129th byte of my varchar string was 0x80, undefined in Latin1
but used in CP1252 for the Euro sign.  In UCS-2, the Euro sign is 0x20AC,
the very value, sure enough, that the server chose for the 129th position
of my nvarchar target column.  

I was misled, unfortunately by SQL Server 7.0:

1> sp_helpsort     
2> go
Unicode data sorting
Locale ID = 1033
           [^^^^ I think this is a clue, albeit nonobvious.]
 case insensitive, kana type insensitive, width insensitive
 
Sort Order Description
Character Set = 1, iso_1
    ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 52, nocase_iso
    Case-insensitive dictionary sort order for use with 
    several Western-European languages including English, 
    French, and German.  Uses the ISO 8859-1 character set.

+++

What this means for FreeTDS, I'm not sure.  The best I can come up with so
far is, be careful.  

To create a useful UCS-2 string, an nvarchar(256) whose values are 0-255:

        declare @a nvarchar(256), @i int 
        select @i=0, @a='' 
        while @i < 256 begin 
                select @a= @a + nchar(@i) 
                select @i = @i+1 
        end 
        insert ucs2 values(@a)

If I set my client charset to UTF-8 and bcp out that row, I get 192 useful
characters.  No corruption, just truncation.  Not a bad start, really.  

--jkl