FreeTDS Development Group

["James K. Lowden" <jklowden AT freetds.org>]

Michael Higgins wrote:
> How do I troubleshoot charset issues? 

A TDSDUMP log will show you the client and server charset names.  There
are some character sets that 0.64 interprets as ISO 8859-1 but that are
really Windows 1252.  That is corrected in the current snapshot.  (see
https://lists.ibiblio.org/sympa/arc/freetds/2006q4/020945.html)

> Where does freetds determine the charset?

Cf. src/tds//locale.c::tds_get_locale

The client charset is taken from the LANG environment variable and can be
overridden by freetds.conf.  (We should use CODESET in nl_langinfo(3), but
we didn't know that at the time.)  

The server charset is set at connection time and can (in TDS 8.0) be
overridden per-column in the resultset metadata.  

Your TDSDUMP log should show the names of both at conversion time.  

> I understand MS SQL Server produces data in a charset.... what happens
> when it comes via freedts? When I look it up in the mysql database there
> is a mismatch.

We might have a terminological misunderstanding.  All data are numbers,
and *all* "text" data are numbers interpretted as letters according to an
encoding scheme i.e. a charset.  So yes, the server "produces data in a
charset".  So do you when you write your email.  

When the data arrive on a FreeTDS client, the library maps, to the extent
possible, the server's charset on the client's.  That is, letters that
exist in both sets are mapped from one to the other.  Letters that don't
exist in the client's set are mapped to '?' and a message is produced.  

> Little Man Inc. => 1112
> name not found: Miller?s Hobbies, Inc. at estes.pl line 100.
> 
> Where did that '?' come from?

If you look up the encodings for ISO 8859-1 and Windows 1252, I believe
you'll find that so-called "smart quotes" are among the differences:
they're defined in 1252 and not in 8859-1.  Specifically, see 0x92.  (The
log will show the arriving data as presented by the server.)  Note that
values > 0x7f are not defined by ASCII.  

http://www.microsoft.com/globaldev/reference/sbcs/1252.mspx

>  locale -a
> C
> POSIX
> en_US.utf8
> 
> tsql shell reports:
> 
> locale is "C"
> locale charset is "ANSI_X3.4-1968

I think I understand that output.  'locale -a' lists locales your libc
understands, three in all.  You haven't set up a locale -- no LANG
environment variable or similar.  tsql calls setlocale(3), 

        setlocale(LC_ALL, "");

which should "use the native environment".  In your case, it's the
default: the 'C' locale, the encoding (charset) for which is ASCII a/k/a
ANSI_X3.4-1968.  

Voila!  When FreeTDS attempts to convert your smart quote to ASCII, it
fails, and you get '?' instead.  

HTH.  

--jkl