FreeTDS Development Group

["Thompson, Bill D (London)" <ThompBil AT exchange.uk.ml.com>]

You're up late, James!

> -----Original Message-----
> From: James K. Lowden [SMTP:jklowden AT schemamania.org]
> Sent: Friday, March 28, 2003 6:51 AM
> To:   FreeTDS Development Group
> Subject:      Re: [freetds] freebcp and I18N
> 
> 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
> 
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds