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: Frediano Ziglio <freddyz77 AT tin.it>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] freebcp and I18N
  • Date: 28 Mar 2003 08:39:40 +0100

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





Archive powered by MHonArc 2.6.24.

Top of Page