Skip to Content.
Sympa Menu

freetds - Re: [freetds] freebcp and I18N

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "James K. Lowden" <jklowden AT schemamania.org>
  • To: TDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] freebcp and I18N
  • Date: Sun, 30 Mar 2003 00:07:15 -0500

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




Archive powered by MHonArc 2.6.24.

Top of Page