FreeTDS Development Group

[Frediano Ziglio <freddyz77 AT tin.it>]

Il ven, 2003-03-28 alle 07:51, James K. Lowden ha scritto:
> 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
> 

It's true. Also mssql2k can send every column with a different charset.

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

I'm working on this. I added iconv_t members to process single-byte (or
utf8) characters.
I noted this problem. What should we return for column_size ?
If server send latin1 and we convert to utf8 a char(3) like 'èèè' is
translated (or should be) to 6 byte. Now column_size is 3 but data_size
is 6. Same problem with insert...

> 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.
> 

Bug in tds_get_string (see FIXME comment in read.c). Now tds_get_string
have 3 parameters:
- tds
- destination buffer
- destination buffer length (same as string to read from wire)
I think we should separate destination buffer length from length to read
(this is we we truncate data...).

> 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:
> 

How do you produce this?

> 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.
> 

Also libtds...

> --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...]
> 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

Perhaps: you put characters in iso-8859-1 and server store then using
cp1252...


> [and things return to nomal]
> a000a100a200a300a400a500a600a700a800a900aa00ab00ac00ad00ae00af00
> b000b100b200b300b400b500b600b700b800b900ba00bb00bc00bd00be00bf00
> c000c100c200c300c400c500c600c700c800c900ca00cb00cc00cd00ce00cf00
> d000d100d200d300d400d500d600d700d800d900da00db00dc00dd00de00df00
> e000e100e200e300e400e500e600e700e800e900ea00eb00ec00ed00ee00ef00
> f000f100f200f300f400f500f600f700f800f900fa00fb00fc00fd00fe00ff00
> 

freddy77