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: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] freebcp and I18N
  • Date: Fri, 28 Mar 2003 01:51:00 -0500

Testing freebcp reveals "interesting" behavior vis-a-vis UTF-8. I
expected crashology, but afaict I'm getting garbage.

I loaded my 256-byte string (containing values 0-255) into a table:

create table ascii (ascii varchar(255))

No matter what I do with "client charset" in freetds.conf, I get those
very same 256 bytes back, using freebcp. That's because:

1. Contrary to our documentation, Microsoft servers don't send all
character data in UCS-2. char/varchar are still sent in a single-byte
character set, presumably controlled by a server setting, and

2. libtds converts only UCS-2 data. Single-byte textual data are passed
through.

Then I inserted a new row into another table:

create table ucs2 (ucs2 nvarchar(255))
insert ucs2 select * from ascii

You'd think that would work, wouldn't you? I did, too. But
something funny happens to our varchar data for the 32-byte range
0x80-0x9F. To demonstrate, I've attached a query that shows them as
varbinary, leaving FreeTDS and iconv aside. I realize that's a
nonprintable range, analogous to 0-0x20, but I still think it's queer,
what Microsoft is doing. Can anyone explain it?

Then I used freebcp to extract the ucs2 row, using different values of
"client charset" in freetds.conf. (freebcp freetds..ucs2 out
/tmp/usc2.dat -c ...) Results:

charset data
ISO-8859-1 ISO-8859-1
UCS-2LE unable to log in
UTF-8 truncated to 255 bytes, nonsense in 0x80-0x9F.

The question for a future debugging session is, Whence the garbage? I
know what UTF-8 data should look like. Here's a sample of good data and
what freebcp gave me:

$ ./ascii |iconv -f ISO-8859-1 -t UTF-8 |hexdump -C
[...]
060 60 61 62 63 64 65 66 67 68 69 6a 6b 6c 6d 6e 6f |`abcdefghijklmno|
070 70 71 72 73 74 75 76 77 78 79 7a 7b 7c 7d 7e 7f |pqrstuvwxyz{|}~.|
080 c2 80 c2 81 c2 82 c2 83 c2 84 c2 85 c2 86 c2 87 |................|
090 c2 88 c2 89 c2 8a c2 8b c2 8c c2 8d c2 8e c2 8f |................|
0a0 c2 90 c2 91 c2 92 c2 93 c2 94 c2 95 c2 96 c2 97 |................|
0b0 c2 98 c2 99 c2 9a c2 9b c2 9c c2 9d c2 9e c2 9f |................|
0c0 c2 a0 c2 a1 c2 a2 c2 a3 c2 a4 c2 a5 c2 a6 c2 a7 |................|
0d0 c2 a8 c2 a9 c2 aa c2 ab c2 ac c2 ad c2 ae c2 af |................|
[...]

$ hexdump -C /tmp/utf8.dat # freebcp output
[...]
060 60 61 62 63 64 65 66 67 68 69 6a 6b 6c 6d 6e 6f |`abcdefghijklmno|
070 70 71 72 73 74 75 76 77 78 79 7a 7b 7c 7d 7e 7f |pqrstuvwxyz{|}~.|
080 e2 82 ac c2 81 e2 80 9a c6 92 e2 80 9e e2 80 a6 |................|
090 e2 80 a0 e2 80 a1 cb 86 e2 80 b0 c5 a0 e2 80 b9 |................|
0a0 c5 92 c2 8d c5 bd c2 8f c2 90 e2 80 98 e2 80 99 |................|
0b0 e2 80 9c e2 80 9d e2 80 a2 e2 80 93 e2 80 94 cb |................|
0c0 9c e2 84 a2 c5 a1 e2 80 ba c5 93 c2 9d c5 be c5 |................|
0d0 b8 c2 a0 c2 a1 c2 a2 c2 a3 c2 a4 c2 a5 c2 a6 c2 |................|
^^^^^ valid UTF-8 resumes
[...]

Unfortunately, the TDSDUMP log is consistent:

5d 00 5e 00 5f 00 60 00 61 00 62 00 63 00 64 00 |].^._.`. a.b.c.d.|
65 00 66 00 67 00 68 00 69 00 6a 00 6b 00 6c 00 |e.f.g.h. i.j.k.l.|
6d 00 6e 00 6f 00 70 00 71 00 72 00 73 00 74 00 |m.n.o.p. q.r.s.t.|
75 00 76 00 77 00 78 00 79 00 7a 00 7b 00 7c 00 |u.v.w.x. y.z.{.|.|
7d 00 7e 00 7f 00 ac 20 81 00 1a 20 92 01 1e 20 |}.~.... ... ... |
^^^^^ What's this?
26 20 20 20 21 20 c6 02 30 20 60 01 39 20 52 01 |& ! .. 0 `.9 R.|
8d 00 7d 01 8f 00 90 00 18 20 19 20 1c 20 1d 20 |..}..... . . . . |
22 20 13 20 14 20 dc 02 22 21 61 01 3a 20 53 01 |" . . .. "!a.: S.|
9d 00 7e 01 78 01 a0 00 a1 00 a2 00 a3 00 a4 00 |..~.x... ........|
^^^^^ valid UCS-2 resumes

My conclusions thus far:

1. We're truncating UTF-8 output, no news there.
2. 0x80 - 0x9F may present some challenges.
3. If we fix #1, freebcp might be able to handle UTF-8.

--jkl

Query demonstrating tables are OK:

1> select cast(cast(ucs2 as varchar(256)) as varbinary(512)) from ucs2
2> select cast(ucs2 as varbinary(512)) from ucs2
3> go # reformatted for your viewing comfort.

000102030405060708090a0b0c0d0e0f
101112131415161718191a1b1c1d1e1f
202122232425262728292a2b2c2d2e2f
303132333435363738393a3b3c3d3e3f
404142434445464748494a4b4c4d4e4f
505152535455565758595a5b5c5d5e5f
606162636465666768696a6b6c6d6e6f
707172737475767778797a7b7c7d7e7f
808182838485868788898a8b8c8d8e8f
909192939495969798999a9b9c9d9e9f
a0a1a2a3a4a5a6a7a8a9aaabacadaeaf
b0b1b2b3b4b5b6b7b8b9babbbcbdbebf
c0c1c2c3c4c5c6c7c8c9cacbcccdcecf
d0d1d2d3d4d5d6d7d8d9dadbdcdddedf
e0e1e2e3e4e5e6e7e8e9eaebecedeeef
f0f1f2f3f4f5f6f7f8f9fafbfcfdfeff

00000100020003000400050006000700080009000a000b000c000d000e000f00
10001100120013001400150016001700180019001a001b001c001d001e001f00
20002100220023002400250026002700280029002a002b002c002d002e002f00
30003100320033003400350036003700380039003a003b003c003d003e003f00
40004100420043004400450046004700480049004a004b004c004d004e004f00
50005100520053005400550056005700580059005a005b005c005d005e005f00
60006100620063006400650066006700680069006a006b006c006d006e006f00
70007100720073007400750076007700780079007a007b007c007d007e007f00
[ok so far, then...]
ac2081001a2092011e20262020202120c60230206001392052018d007d018f00
9000182019201c201d20222013201420dc02222161013a2053019d007e017801
[and things return to nomal]
a000a100a200a300a400a500a600a700a800a900aa00ab00ac00ad00ae00af00
b000b100b200b300b400b500b600b700b800b900ba00bb00bc00bd00be00bf00
c000c100c200c300c400c500c600c700c800c900ca00cb00cc00cd00ce00cf00
d000d100d200d300d400d500d600d700d800d900da00db00dc00dd00de00df00
e000e100e200e300e400e500e600e700e800e900ea00eb00ec00ed00ee00ef00
f000f100f200f300f400f500f600f700f800f900fa00fb00fc00fd00fe00ff00




Archive powered by MHonArc 2.6.24.

Top of Page