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

We're using FreeTDS 0.91 since many years now
with Sybase store configured to ISO-8859-1 and Client using utf-8.

Our OPG Client is running on Linux and Windows.

Conversion is absolutely no problem there.

-- 
Fredy Paquet
Mitglied der Geschäftsleitung
OPAG Informatik AG
Fabrikmattenweg 11
CH-4144 Arlesheim
http://www.opag.ch
Tel. ++41 61 716 92 22
Fax. ++41 61 716 92 29


On Mon, 2016-10-10 at 14:45 +0100, Frediano Ziglio wrote:
> 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

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