FreeTDS Development Group

["Thomas, Christopher (LLU)" <cwthomas AT llu.edu>]

This makes sense to me the database is using one charset and the text
being inserted is using another. 

I had a project where I was putting text into our database and couldn't
figure out why all the hyphens, single and double quotes were turned
into ?'s. By looking at the text in a hex editor, I figured out it
didn't use regular hyphens, single and double quotes. So I replaced them
before doing a sql insert or update. Now I know that I probably need to
change the charset.

Chris

-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of James K. Lowden
Sent: Monday, July 30, 2007 11:40 AM
To: FreeTDS Development Group
Subject: Re: [freetds] Charset issues...

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
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds