FreeTDS Development Group

["Bryan Olmstead (imap)" <bryan AT chfund.org>]

Bryan Olmstead (imap) wrote:
>> Bryan Olmstead (imap) wrote:
>>> i recently ran a query that returned a row with a question mark and the
>>> line: "WARNING! Some character(s) could not be converted into client's
>>> character set. Unconverted bytes were changed to question marks ('?')".
>>>
>>> searching google and the mailing list i found that i should be able to
>>> fix this by either setting "client charset" in the freetds.conf file or
>>> the LANG environment variable.  i've tested this out and neither seems
>>> to have worked as i still receive the warning and the question mark.  if
>>> anyone could give me some advice i would greatly appreciate it.
>>>
>>> and before i forget, it's version 0.64 on slackware connecting to mssql.
>> ...
>>> just in case anyone is wondering, these are the en_US currently:
>>>
>>> en_US
>>> en_US.iso88591
>>>
>>> and the freetds.conf section:
>>>
>>> [test]
>>>         host = 127.0.0.1
>>>         port = 1434
>>>         tds version = 7.0
>>>         client charset = UTF-8
>> I think what's happening is caused by setting LC_ALL to POSIX.  That
>> supersedes freetds.conf and tells libc you want ASCII (a/k/a
>> ANSI_X3.4-1968).  
>>
>> http://www.opengroup.org/onlinepubs/007908799/xbd/envvar.html
>>
>>      "LANG
>> This variable determines the locale category for native language, local
>> customs and coded character set in the absence of the LC_ALL"
>>
>> I.e. LC_ALL supersedes LANG.  
>>
>> tsql reports the results of setlocale(3) and nl_langinfo(3).  If you set
>> your LC_ALL to en_US.iso88591 or en_US.UTF8 (maybe, depends on your iconv
>> implementation) tsql should report using it instead of 'locale charset is
>> "ANSI_X3.4-1968"'.  
> 
> thanks, that really helps put things into perspective.  unfortunately,
> i'm still getting 'locale charset is "ANSI_X3.4-1968"' when connecting!
> 
> i set both LANG and LC_ALL to en_IN.utf8 to test out a different one and
> just to make sure:
> 
> olmstead@alpha:~/ecw-sql/t> setenv | grep LC ; setenv | grep LANG
> LC_ALL=en_IN.utf8
> LANG=en_IN.utf8
> olmstead@alpha:~/ecw-sql/t> tsql -S test -U sa
> locale is "C"
> locale charset is "ANSI_X3.4-1968"
> Password:
> 1> exit
> olmstead@alpha:~/ecw-sql/t> head -12 dump.log
> util.c:288:Starting log file for FreeTDS 0.64
>         on 2007-09-27 08:54:02 with debug flags 0x4fff.
> iconv.c:195:names for ISO-8859-1: ISO-8859-1
> iconv.c:195:names for UTF-8: UTF-8
> iconv.c:195:names for UCS-2LE: UCS-2LE
> iconv.c:195:names for UCS-2BE: UCS-2BE
> iconv.c:361:iconv to convert client-side data to the "ANSI_X3.4-1968"
> character set
> iconv.c:514:tds_iconv_info_init: converting "US-ASCII"->"UCS-2LE"
> iconv.c:514:tds_iconv_info_init: converting "ISO-8859-1"->"UCS-2LE"
> net.c:168:Connecting to 127.0.0.1 port 1434.
> write.c:134:tds_put_string converting 5 bytes of "alpha"
> write.c:162:tds_put_string wrote 10 bytes
> 
> 
> then i tried unsetting both just to see:
> 
> olmstead@alpha:~/ecw-sql/t> setenv | grep LC ; setenv | grep LANG
> olmstead@alpha:~/ecw-sql/t> tsql -S test -U sa
> locale is "C"
> locale charset is "ANSI_X3.4-1968"
> Password:
> 1> exit
> olmstead@alpha:~/ecw-sql/t> head -12 dump.log
> util.c:288:Starting log file for FreeTDS 0.64
>         on 2007-09-27 08:51:47 with debug flags 0x4fff.
> iconv.c:195:names for ISO-8859-1: ISO-8859-1
> iconv.c:195:names for UTF-8: UTF-8
> iconv.c:195:names for UCS-2LE: UCS-2LE
> iconv.c:195:names for UCS-2BE: UCS-2BE
> iconv.c:361:iconv to convert client-side data to the "ANSI_X3.4-1968"
> character set
> iconv.c:514:tds_iconv_info_init: converting "US-ASCII"->"UCS-2LE"
> iconv.c:514:tds_iconv_info_init: converting "ISO-8859-1"->"UCS-2LE"
> net.c:168:Connecting to 127.0.0.1 port 1434.
> write.c:134:tds_put_string converting 5 bytes of "alpha"
> write.c:162:tds_put_string wrote 10 bytes
> 

after walking away, again, and reflecting i decided to check out the
version of glibc installed (as i remember reading somewhere and in my
package search this affects locale, iconv etc.).  the version was 2.3.2
and on another machine with freetds the version is 2.3.5.  since the
glibc version was different i tested it out, and when i set LC_ALL it
connected with the approriate locale and locale charset!  so, i upgraded
the slackware glibc packages to 2.3.5 and it worked!

olmstead@alpha:~/ecw-sql/t> setenv | grep LC ; setenv | grep LANG
LC_ALL=en_US.iso88591
LANG=C
olmstead@alpha:~/ecw-sql/t> tsql -S test -U sa
locale is "en_US.iso88591"
locale charset is "ISO-8859-1"
Password:
1> exit


then, it was time to move on and run the perl script that uses freetds
to pull the data and create a pdf.  at first it was not pulling the
questionable characters correctly.  but when i checked the dump log it
was using UTF-8, as was set in freetds.conf as the "client charset",
instead of using the LC_ALL environment variable.  so, i changed
freetds.conf to this:

[test]
        host = 127.0.0.1
        port = 1434
        tds version = 7.0
        client charset = ISO-8859-1

reran the script and the character appeard just like it was suppose to!
 (i even ran it a few times and from different shells to double check)

thanks for the pointers james, it really helped.

-bryan