Re: Sybase: Charset conversion

2009/11/26 Sebastian Podjasek <[email protected]>:
> Dnia 2009-11-26, czw o godzinie 07:17 +0100, Frediano Ziglio pisze:
>> > What does the top of your TDSDUMP log say about the conversions?  What
>> > encoding do you think the server expects, and what does the client want?
>
>> Which database server are you using? Sybase or MSSQL? Which version?
>> Which platform?
>> Which client platform are you using? Big endian or little endian?
>>
>> I suspect client is big endian and server send utf16 coded as big endian
>> using UNIVARCHAR. Which database type are you using? Could you post a
>> TDSDUMP?
>
>
>
>> This is the description of the tables in xxxxxx.db v17, released beginning of xxxxxx. This database
>> is released with version 10 of Adaptive Server Anywhere. All character fields in this version of the
>> database are converted to Unicode character fields. Please consider this change when connecting to
>> the database, and always perform several tests with your software before working on live data.
>
> I use Perl DBD::Sybase with following connection string:
> DBI->connect('dbi:Sybase:' . $dsn, 'xxx', 'xxx', {
>    RaiseError => 1,
>    syb_show_sql => 1,
>    syb_show_eed => 1,
>    LongReadLen => 4000,
> }) or die $DBI::errstr;
>
> My client is standard x86_64 laptop running kernel 2.6.31-15-generic
> from ubuntu distro - so it's a standard little endian hardware.
> Regarding server hardware I have no idea about its Endianness.
>
> Below are few interesting lines from TDSDUMP
>
> log.c:190:Starting log file for FreeTDS 0.82
>        on 2009-11-24 17:09:44 with debug flags 0x4fff.
> iconv.c:197:names for ISO-8859-1: ISO-8859-1
> iconv.c:197:names for UTF-8: UTF-8
> iconv.c:197:names for UCS-2LE: UCS-2LE
> iconv.c:197:names for UCS-2BE: UCS-2BE
> iconv.c:363:iconv to convert client-side data to the "UTF-8" character
> set
> iconv.c:516:tds_iconv_info_init: converting "UTF-8"->"UCS-2LE"
> net.c:210:Connecting to x.x.x.x port x (TDS version 5.0)
> net.c:264:tds_open_socket: connect(2) returned "Operation now in
> progress"
> net.c:303:tds_open_socket() succeeded
> util.c:162:Changed query state from DEAD to IDLE
>
> ....
>
> net.c:671:Received packet
> 0000 e3 07 00 03 04 75 74 66-38 00 ad 16 00 05 05 00 |.....utf 8.......|
> 0010 00 00 0c 53 51 4c 20 41-6e 79 77 68 65 72 65 0a |...SQL A nywhere.|
> 0020 00 00 00 e2 16 00 01 09-00 00 0e 61 01 ff ff fe |........ ...a....|
> 0030 e6 02 09 01 fe 7f 1e e2-68 00 00 0a fd 00 00 01 |........ h.......|
> 0040 00 00 00 00 00         -                        |.....|
>
> token.c:316:looking for login token, got  e3(ENVCHANGE)
> token.c:108:tds_process_default_tokens() marker is e3(ENVCHANGE)
> token.c:2356:server indicated charset change to "utf8"
> iconv.c:985:setting server single-byte charset to "UTF-8"
> iconv.c:516:tds_iconv_info_init: converting "ISO-8859-1"->"UTF-8"
> token.c:316:looking for login token, got  ad(LOGINACK)
> token.c:316:looking for login token, got  e2(CAPABILITY)
> token.c:108:tds_process_default_tokens() marker is e2(CAPABILITY)
> token.c:316:looking for login token, got  fd(DONE)
> token.c:108:tds_process_default_tokens() marker is fd(DONE)
>
>
> After executing some simple SQL 'SELECT' with Unicode column, I get
> following (some values has to be obfuscated for legal reasons)
>
>
> net.c:671:Received packet
> 0000 61 73 00 00 00 02 00 07-xx xx xx xx xx xx xx 0c |as...... xxxxxxx.|
> 0010 xx xx xx xx xx xx xx xx-xx xx xx xx 03 64 62 61 |xxxxxxxx xxxx.dba|
> 0020 0b xx xx xx xx xx xx xx-xx xx xx xx 07 xx xx xx |.xxxxxxx xxxx.xxx|
> 0030 xx xx xx xx 20 00 00 00-50 00 00 00 6f 08 00 06 |xxxx ... P...o...|
> 0040 xx xx xx xx xx xx 0c xx-xx xx xx xx xx xx xx xx |xxxxxx.x xxxxxxxx|
> 0050 xx xx xx 03 64 62 61 0b-xx xx xx xx xx xx xx xx |xxx.dba. xxxxxxxx|
> 0060 xx xx xx 06 xx xx xx xx-xx xx 20 00 00 00 22 00 |xxx.xxxx xx ...".|
> 0070 00 00 e1 80 02 00 00 00-d1 08 c5 9c 00 00 60 cf |........ ......`.|
> 0080 c0 00 3e 00 00 00 00 50-00 6f 01 42 01 05 00 63 |..>....P .o.B...c|
> 0090 00 7a 00 65 00 6e 00 69-00 65 00 20 00 7a 00 20 |.z.e.n.i .e. .z. |
> 00a0 00 54 00 43 00 47 00 20-00 6a 00 65 00 73 00 74 |.T.C.G.  .j.e.s.t|
> 00b0 00 20 00 7a 00 61 00 6b-01 42 00 75 00 63 00 6f |. .z.a.k .B.u.c.o|
> 00c0 00 6e 00 65 fd 10 00 01-00 01 00 00 00          |.n.e.... .....|
>
> token.c:510:processing result tokens.  marker is  61(ROWFMT2)
> token.c:1713:tds5_process_result
> mem.c:563:tds_free_all_results()
> token.c:1737:num_cols=2
> token.c:1840:col 0:
> token.c:1841:   column_name=[dt_made]
> token.c:1847:   flags=20 utype=80 type=111 varint=1
> token.c:1850:   colsize=8 prec=0 scale=0
> token.c:3294:adjust_character_column_size:
>        Server charset: UCS-2LE
>        Server column_size: 640
>        Client charset: UTF-8
>        Client column_size: 1280
> token.c:1840:col 1:
> token.c:1841:   column_name=[c_text]
> token.c:1847:   flags=20 utype=34 type=225 varint=5
> token.c:1850:   colsize=1280 prec=0 scale=0
> util.c:162:Changed query state from READING to PENDING
> ct.c:1157:ct_results() process_result_tokens returned 1 (type 4049)
> token.c:495:tds_process_tokens(0x13daf90, 0x7fff09c47d5c,
> 0x7fff09c47d58, 0x6914)
> util.c:162:Changed query state from PENDING to READING
>
>
> And here you can find values returned by DBI and my 'conversion flow' to
> receive expected result (polish string)...
>
>> Data returned by DBI...
>> > '倀漀䈁ԁ挀稀攀渀椀攀 稀 吀䌀䜀 樀攀猀琀 稀愀欀䈁甀挀漀渀攀'
>> 0x00000000 (00000)   e58080e6 bc80e488 81d481e6 8c80e7a8   ................
>> 0x00000010 (00016)   80e69480 e6b880e6 a480e694 80e28080   ................
>> 0x00000020 (00032)   e7a880e2 8080e590 80e48c80 e49c80e2   ................
>> 0x00000030 (00048)   8080e6a8 80e69480 e78c80e7 9080e280   ................
>> 0x00000040 (00064)   80e7a880 e68480e6 ac80e488 81e79480   ................
>> 0x00000050 (00080)   e68c80e6 bc80e6b8 80e69480            ............
>> Conversion from UTF-8 to UCS-2LE
>> > 'Po B czenie z TCG jest zak Bucone'
>> 0x00000000 (00000)   0050006f 01420105 0063007a 0065006e   .P.o.B...c.z.e.n
>> 0x00000010 (00016)   00690065 0020007a 00200054 00430047   .i.e. .z. .T.C.G
>> 0x00000020 (00032)   0020006a 00650073 00740020 007a0061   . .j.e.s.t. .z.a
>> 0x00000030 (00048)   006b0142 00750063 006f006e 0065       .k.B.u.c.o.n.e
>> Conversion from UCS-2BE to UTF-8
>> > 'Połączenie z TCG jest zakłucone'
>> 0x00000000 (00000)   506fc582 c485637a 656e6965 207a2054   Po....czenie z T
>> 0x00000010 (00016)   4347206a 65737420 7a616bc5 8275636f   CG jest zak..uco
>> 0x00000020 (00032)   6e65                                  ne
>
> same thing done with TSQL
>
>> seba:~/$ LANG=en_US.UTF-8 tsql -S xxxxxx -U xxxxx -P xxxxx
>> locale is "en_US.UTF-8"
>> locale charset is "UTF-8"
>> 1> select xxxxxx ltext from dba.XXXXXXX where xxxxx > '2009-11-18'
>> 2> go
>> ltext
>> e58080e6bc80e1a880e1a880e68c80e7a880e69480e6b880e6a480e69480e28080e7a880e28080e59080e48c80e49c80e28080e6a880e69480e78c80e79080e28080e7a880e68480e6ac80e1a880e79480e68c80e6bc80e6b880e69480
>> (1 row affected)
>
> I hope that helps...
>

I tried this test

create table #tmp1(a univarchar(10))
insert into #tmp1 values(u&'\0041\0141')
select * from #tmp1

I'm sorry to say but my Sybase 15 returns data in little endian format
so it seems a problem of your database. It can be possible however
that is a problem inserting data.

freddy77
_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds