Re: Troubles with charset ISO-8859-1 / UTF-8

2016-10-10 11:45 GMT+01:00 Pascal Moreau <[email protected]>:
> Hello,
>
> I try to access SYBASE database from a PostgreSQL database by using FreeTDS
> and tds_fdw (foreign tables) but I have troubles with charset in output of
> Freetds.
>
> The trouble is that I can't see in output of FreeTDS the french character
> with accent like é,è,à so in PostgreSQL the cells with this characters
> alphabet (é,è,à...) are incomplete in the foreign tables.
>

Quite hard to say. It would be worth knowing how tds_fdw is supposed to address
encoding. Looking at the logs below looks like they are using dblib so I would
expect them to assume utf-8 to support more encodings as possible.

> I have try many change in FreeTDS but it's always the same thing, I have
> try freetds-0.95.95 and freetds-1.00.15, I have try with ./configure
> --disable-libiconv or not, I have change freetds.conf and locale.conf
> options with ISO-8859-1, LATIN1 and UTF-8 but it's always the same result.
>

I would try to force utf-8.

> My configuration is:
> SYBASE 15.7 / Character set: ISO-8859-1 (LATIN-1) / Language: french
> PostgreSQL 9.4.4 / Encoding: UTF8 / LC_COLLATE & LC_TYPE: fr_FR.UTF8
>
> freetds.conf:
> [global]
>         dump file = /tmp/freetds.log
>         debug flags = 0xffff
>         text size = 64512
> [SYBDEV1]
>         host = SYBDEV1
>         port = 8025
>         tds version = 5.0
>         client charset = UTF-8
>
>
> locale.conf:
> [default]
>         date format = %d/%m/%Y %H:%M
>         language = French
>         charset = iso_1
>
>
> And here is what I can see in the freetds.log
>
> ...
> 11:54:06.816788 24919 (config.c:298):Found conf file
> '/usr/local/etc/freetds.conf' (default).
> 11:54:06.816793 24919 (config.c:506):Looking for section global.
> 11:54:06.816803 24919 (config.c:565):    Found section global.
> 11:54:06.816808 24919 (config.c:568):Got a match.
> 11:54:06.816813 24919 (config.c:591):    dump file = '/tmp/freetds.log'
> 11:54:06.816817 24919 (config.c:591):    debug flags = '0xffff'
> 11:54:06.816822 24919 (config.c:591):    text size = '64512'
> 11:54:06.816827 24919 (config.c:565):    Found section sybdev1.
> 11:54:06.816832 24919 (config.c:579):    Reached EOF
> 11:54:06.816836 24919 (config.c:506):Looking for section SYBDEV1.
> 11:54:06.816842 24919 (config.c:565):    Found section global.
> 11:54:06.816846 24919 (config.c:565):    Found section sybdev1.
> 11:54:06.816855 24919 (config.c:568):Got a match.
> 11:54:06.816860 24919 (config.c:591):    host = 'SYBDEV1'
> 11:54:06.817592 24919 (config.c:628):Found host entry SYBDEV1
> 11:54:06.817616 24919 (config.c:631):IP addr is 172.20.13.176.
> 11:54:06.817622 24919 (config.c:591):    port = '8025'
> 11:54:06.817627 24919 (config.c:591):    tds version = '5.0'
> 11:54:06.817631 24919 (config.c:915):Setting tds version to 5.0 (0x500).
> 11:54:06.817636 24919 (config.c:591):    client charset = 'UTF-8'
> 11:54:06.817640 24919 (config.c:646):tds_parse_conf_section: client charset
> is UTF-8.
> 11:54:06.817646 24919 (config.c:579):    Reached EOF
> 11:54:06.817650 24919 (config.c:302):Success: [SYBDEV1] defined in
> /usr/local/etc/freetds.conf.
> 11:54:06.817667 24919 (config.c:714):tds_config_login: database_name is
> eldro_encours.
> 11:54:06.817675 24919 (dblib.c:1256):tdsdbopen: Calling
> tds_connect_and_login(0x16c1130, 0x161b240)
> 11:54:06.817686 24919 (iconv.c:325):tds_iconv_open(0x16c1130, UTF-8)
> 11:54:06.817690 24919 (iconv.c:343):setting up conversions for client
> charset "UTF-8"
> 11:54:06.817694 24919 (iconv.c:345):preparing iconv for "UTF-8" <->
> "UCS-2LE" conversion
> 11:54:06.817707 24919 (iconv.c:384):tds_iconv_open: done
> 11:54:06.817721 24919 (net.c:216):Connecting to 172.20.13.176 port 8025
> (TDS version 5.0)
> 11:54:06.817850 24919 (net.c:242):tds_open_socket: connect(2) returned
> "Operation now in progress"
> 11:54:06.818557 24919 (net.c:343):tds_open_socket() succeeded
> ...
> 11:54:06.827003 24919 (token.c:306):looking for login token, got
> e3(ENVCHANGE)
> 11:54:06.827011 24919 (token.c:125):tds_process_default_tokens() marker is
> e3(ENVCHANGE)
> 11:54:06.827021 24919 (dblib.c:310):db_env_chg(0x16c1130, 1, master, tempdb)
> 11:54:06.827027 24919 (token.c:306):looking for login token, got  e5(EED)
> 11:54:06.827032 24919 (token.c:125):tds_process_default_tokens() marker is
> e5(EED)
> 11:54:06.827037 24919 (token.c:2328):tds_process_info() reading message
> 5701 from server
> 11:54:06.827043 24919 (token.c:2400):tds_process_info() calling client msg
> handler
> 11:54:06.827047 24919 (dbutil.c:74):_dblib_handle_info_message(0x16c0b60,
> 0x16c1130, 0x7fffb4df4ff0)
> 11:54:06.827052 24919 (dbutil.c:75):msgno 5701: "Le contexte de la base de
> données a été changé en 'tempdb'.
> "
> 11:54:06.827057 24919 (token.c:2417):tds_process_info() returning
> TDS_SUCCESS
> 11:54:06.827060 24919 (token.c:306):looking for login token, got
> e3(ENVCHANGE)
> 11:54:06.827064 24919 (token.c:125):tds_process_default_tokens() marker is
> e3(ENVCHANGE)
> 11:54:06.827069 24919 (token.c:2220):changing block size from  to 2048
> 11:54:06.827075 24919 (dblib.c:310):db_env_chg(0x16c1130, 4, , 2048)
> 11:54:06.827079 24919 (token.c:306):looking for login token, got
> ad(LOGINACK)
> 11:54:06.827084 24919 (token.c:344):server reports TDS version 5.0.0.0
> 11:54:06.827088 24919 (token.c:346):Product name for 0x5000000 is unknown
> 11:54:06.827093 24919 (token.c:378):Product version F070000
> 11:54:06.827098 24919 (token.c:306):looking for login token, got
> e2(CAPABILITY)
> 11:54:06.827101 24919 (token.c:125):tds_process_default_tokens() marker is
> e2(CAPABILITY)
> 11:54:06.827106 24919 (token.c:306):looking for login token, got  fd(DONE)
> 11:54:06.827109 24919 (token.c:125):tds_process_default_tokens() marker is
> fd(DONE)
> 11:54:06.827114 24919 (token.c:2065):tds_process_end: more_results = 0
>         was_cancelled = 0
>         error = 0
>         done_count_valid = 0
> 11:54:06.827118 24919 (token.c:2082):                rows_affected = 70
> 11:54:06.827122 24919 (token.c:2085):tds_process_end() state set to TDS_IDLE
> ...
> 11:54:06.828755 24919 (token.c:525):tds_process_tokens(0x16c1130,
> 0x7fffb4df555c, 0x7fffb4df5558, 0x6914)
> 11:54:06.828760 24919 (util.c:165):Changed query state from PENDING to
> READING
> 11:54:06.831974 24919 (packet.c:639):Received packet
> 0000 04 00 08 00 00 00 00 00-61 40 01 00 00 06 00 00 |........ a@......|
> 0010 0d 65 6c 64 72 6f 5f 65-6e 63 6f 75 72 73 03 64 |.eldro_e ncours.d|
> 0020 62 6f 07 63 5f 63 6f 6e-67 65 0b 63 63 6f 6e 67 |bo.c_con ge.ccong|
> 0030 65 5f 63 6f 64 65 10 00-00 00 01 00 00 00 2f 05 |e_code.. ....../.|
> 0040 00 00 0d 65 6c 64 72 6f-5f 65 6e 63 6f 75 72 73 |...eldro _encours|
> 0050 03 64 62 6f 07 63 5f 63-6f 6e 67 65 0b 63 63 6f |.dbo.c_c onge.cco|
> 0060 6e 67 65 5f 72 65 67 72-30 00 00 00 01 00 00 00 |nge_regr 0.......|
> 0070 27 05 00 00 0d 65 6c 64-72 6f 5f 65 6e 63 6f 75 |'....eld ro_encou|
> 0080 72 73 03 64 62 6f 07 63-5f 63 6f 6e 67 65 0c 63 |rs.dbo.c _conge.c|
> 0090 63 6f 6e 67 65 5f 6c 69-62 65 6c 30 00 00 00 02 |conge_li bel0....|
> 00a0 00 00 00 27 fa 00 00 0d-65 6c 64 72 6f 5f 65 6e |...'.... eldro_en|
> 00b0 63 6f 75 72 73 03 64 62-6f 07 63 5f 63 6f 6e 67 |cours.db o.c_cong|
> 00c0 65 11 63 63 6f 6e 67 65-5f 64 61 74 65 5f 64 65 |e.cconge _date_de|
> 00d0 62 75 74 30 00 00 00 25-00 00 00 7b 04 00 00 0d |but0...% ...{....|
> 00e0 65 6c 64 72 6f 5f 65 6e-63 6f 75 72 73 03 64 62 |eldro_en cours.db|
> 00f0 6f 07 63 5f 63 6f 6e 67-65 0f 63 63 6f 6e 67 65 |o.c_cong e.cconge|
> 0100 5f 64 61 74 65 5f 66 69-6e 30 00 00 00 25 00 00 |_date_fi n0...%..|
> 0110 00 7b 04 00 00 0d 65 6c-64 72 6f 5f 65 6e 63 6f |.{....el dro_enco|
> 0120 75 72 73 03 64 62 6f 07-63 5f 63 6f 6e 67 65 12 |urs.dbo. c_conge.|
> 0130 63 63 6f 6e 67 65 5f 6c-69 62 65 6c 5f 63 6f 75 |cconge_l ibel_cou|
> 0140 72 74 30 00 00 00 02 00-00 00 27 19 00 ae 06 00 |rt0..... ..'.....|
> 0150 00 00 00 00 00 00 d1 05-43 41 31 30 30 05 43 41 |........ CA100.CA|
> 0160 31 30 30 33 41 62 73 65-6e 63 65 20 70 6f 75 72 |1003Abse nce pour|
> 0170 20 63 65 73 73 61 74 69-6f 6e 20 63 6f 6e 63 65 | cessati on conce|
> 0180 72 74 e9 65 20 64 65 20-74 72 61 76 61 69 6c 20 |rt.e de  travail |
> 0190 28 67 72 e8 76 65 29 04-00 00 00 00 04 66 21 06 |(gr.ve). .....f!.|
> 01a0 00 05 47 72 e8 76 65 d1-05 43 41 34 30 30 05 43 |..Gr.ve. .CA400.C|
> 01b0 41 34 30 30 13 41 62 73-65 6e 63 65 20 69 72 72 |A400.Abs ence irr|
> 01c0 e9 67 75 6c 69 e8 72 65-04 00 00 00 00 04 66 21 |.guli.re ......f!|
> ...
>
>
>
> On ligne 0180 I should see "|rtée de  travail |" and on ligne 0190 It
> should be "|(grève). .....f!.|"

This is a binary dump, is not expected to support other than ASCII but
you can see that the column from the server is encoded with single
character. Looking at https://it.wikipedia.org/wiki/ISO/IEC_8859-1 the
encoding is fine.

>
> Then when I try to see the result in my databse on PostgreSQL I have the
> following result where "grève" is became "grve":
>
> [postgres@INST01] estim=# select * from eldroec_c_conge where cconge_code
> ='CA100';
>  cconge_code | cconge_regr |
> cconge_libel                    | cconge_date_debut | cconge_date_fin |
> cconge_libel_court
> -------------+-------------+---------------------------------------------------+-------------------+-----------------+--------------------
>  CA100       | CA100       | Absence pour cessation concerte de travail
> (grve) | 1900-01-01        | 2999-12-31      | Grve
> (1 ligne)
>
> Temps : 139,166 ms
>
>
> Can someome help me please ?
>
> Thanks.

From the log (which is partial without queries) everything is fine. Up
to tds_fdw.

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