FreeTDS Development Group

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

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