FreeTDS Development Group

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

On Tue, 22 Nov 2011 10:05:17 +0000 (UTC)
Chris Ballard <cballard AT datix.co.uk> wrote:

> PHP has native iconv functionality which I've tried 
> using, but to no avail.  The collation that's used is
> Latin1_General_CI_AS - I don't think this is the issue though since
> the characters save ok when running the statement against the DB
> directly (i.e. via Management Studio), so I'd guess it's something to
> do the way the driver is interpreting/presenting these characters?

You're right in supposing it's "something to do the way the driver is
interpreting/presenting these characters".  But let's not guess.  

http://msdn.microsoft.com/en-us/library/cc195054.aspx

I didn't find a definitive statement on MSDN, but
I'm pretty sure Latin1_General_CI_AS is based on Latin1, which to
Microsoft is Code Page 1252.  Your "smart quotes" are 0x93 and 0x94.  

> > I've attempted to remedy this by adding a client charset definition,
> > but setting this to CP1252 results in the connection to SQL Server
> > not working at all.  

In the light of knowing the encoding used on the server, this statement
has new meaning.  

A simple tool like xterm(1) sees a stream of bytes that it
interprets as character data.  Those bytes are numbers.  Each sequence
of one or more bytes is mapped to a character.  That mapping -- which
characters are denoted by which numbers -- is an encoding.  

Encoding is not encryption.  The information isn't hidden in any way.
It's just held in a different form, like Captain Kirk on the way to the
planet's surface.  As with the Transporter, the hoped-for outcome is
that the information is decoded according to the same plan which it
was encoded, thereby rendering it with perfect fidelity.  

Thus the highest hope of a programmer: to change nothing.  

To change nothing requires client and server to agree on an encoding.
To make that easier, many popular character encodings have names.
Your server is using CP1252.  Because that information is conveyed in
the TDS bytestream, FreeTDS knows how to decode the data.  

However: what if the client doth not the encoding key possess?  What if
the client prefers or conventionally uses some other encoding?  Is our
goal to change nothing to end in tears before the lighting of the first
phosphor?  

Actually, all is not lost.  A couple of things come to the rescue.  

1.  Many encodings overlap, i.e. they use the same number-character map
for many shared characters.  For a great many, 'A' is 65, for example.
Thus even when the information is decoded according a different plan,
the mapping is similar enough that a human being can interpret the
information.  The rest is 21st-century visual noise.  

2.  Some encodings can be mapped onto other encodings.  How
well depends on their shared definition of the *set of characters* they
encode.  Each encoding addresses its set of characters.  The
intersection of those sets can be be mapped between them.  The
characters that fall outside that intersection cannot be mapped.  The
job of mapping one encoding onto another is what iconv is hired for.  

FreeTDS uses iconv, and must perforce have a policy to deal
with failed mappings.  That policy is:

1.  On fetching from the server, convert unmapped characters to '?'.
To the best of my knowledge, all encodings include that character, and
it means the same thing in all languages.  At least I hope so.  

2.  On sending data to the server, conversion failures raise an error
and the operation fails.  Any data arriving at your server from a
FreeTDS client was converted perfectly (if at all).  That goes for '?',
too.  

That you see '?' characters where they don't belong indicates -- if
FreeTDS put them there -- that the client encoding it used does not
include those characters.  That would be the case if, say, your client
uses ISO 8859-1.  

How does FreeTDS know what encoding the client uses?  POSIX
systems have a locale.  A great many programs -- including the C
standard library and the X window system -- coordinate their settings
around the locale. The locale is controlled per-process through 
environment variables, e.g. LANG.  FreeTDS interrogates the locale
through the C standard library function setlocale(3).  tsql reports the
locale and encoding.  

Sometimes, though, the results of that interrogation are not what is
desired.  Sometimes the client isn't set up right.  Sometimes the
client is irrelevant, because the "client" is just an intermediary.
Webservers are like that: a web browser does not and cannot care what
locale a webserver uses.  Rather, the webserver expresses the
document's encoding explicitly via HTTP and HTML.  

If we've learned anything since 11 September 2001, it's that when
interrogation fails, more force is needed.  That's what FreeTDS's
client charset option offers: force.  The client charset setting in
freetds.conf tells FreeTDS how to encode character data arriving from
the server.  It does not change anything on the server and, equally
important, it changes nothing on the client!  

But we've learned something else, too: force doesn't always achieve the
desired end.  

Back to our xterm.  We left it striving heroically to display a
stream of bytes arriving from the server via our humble friend
FreeTDS.  Unlike FreeTDS, xterm *assumes* an encoding (a charset). It
must assume, because there's no intrinsic information in a stream of
bytes to express an encoding.  It's just a stream of bytes!  

On what is that assumption based?  The locale.  Is your client is set
up to use CP1252?  If locale(1) says No, that might explain why telling
FreeTDS to use it didn't help.  

In other words your best bet is to elicit cooperation.  

Now you know what to do:

1.  The server uses CP1252.  Nothing to do there.  That's a gimme. 
2.  Find out what encoding your client is using.  
3.  Make sure you're linked to an iconv library that supports both
encodings.  Verify that both define "double left quotation mark"
and "double right quotation mark".  
4.  Make sure FreeTDS reports successful encoding setup.  
5.  Relax, secure in the knowledge that the deterministic machine holds
no mysteries for those prepared to understand.  

and what not to do:

1.  Guess.

The answer you seek lies beneath your very fingers.  TDSDUMP and tsql
are your tireless friends.  I hope they, coupled with this brief
tutorial, suffice to meet your need.  

--jkl