FreeTDS Development Group

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

Lapo Luchini wrote:
> James K. Lowden <jklowden <at> freetds.org> writes:
> 
> > Moral: don't let the client put windows-1252 data in a column encoded
> > as ISO 8859-1.  Don't declare to the server that your windows-1252
> > data are ISO 8859-1.  Be honest!  Say what you've got, and maybe use a
> > unicode datatype e.g. NVARCHAR to store it.  Then things will go much
> > easier.  
> 
> Of course using NVARCHAR is the *correct* solution, but if you happen to
> have to wokrk with an existing and huge SqlServer 7 database "that
> displays the accents right with EVERY OTHER application", your new
> little PHP app *have* to work as well with (existing) Windows-1252 data.
> 
> AFAIK SQLServer7 doesn't know anything about the ISO-1-ness of the
> fields: they just "are".

The encoding of a (varchar, say) field is controlled by its "collation". 
In SQL Server 7.0, I believe this is per-database or per-server.  The
documents aren't online anymore, so I can't check.  

*If* -- this is important to know for sure --  you are storing
windows-1252 data in a column that the server "knows" to be ISO 8859-1
(and that can't be changed), then your patch is "necessary" in the sense
that it will work for that particular broken setup.  

The server communicates its single-byte charset encoding to the client
with an ENVCHANGE packet.  This is processed in
src/tds/iconv.c::tds_srv_charset_changed() and captured in the
TDSSOCKET::char_convs array of iconv descriptors.  (You see this happen in
the TDSDUMP log.)  Whenever FreeTDS reads single-byte charset data from
the wire, it passes that descriptor to iconv(3) to convert the data to the
client's encoding.  

As you can see, FreeTDS is paying attention to how the server says the
data are encoded.  If they are not encoded according to the server's
declaration, the conversion to the client's encoding cannot be done
correctly.  

> And, in fact, they "are" Windows-1252, whatever he states in the
> wire-protocol: all its tool show them as CP1252. There is probably some
> heavy confusion in some Microsoft folk, that tought that since the two
> formats are 90% equal, they are perfectly equal...

They are close, and arguably windows-1252 is a better default for us,
because that's Microsoft's default.  OTOH this is one of those areas where
"helpful" defaults only defer the problem until later, when it's harder to
discover and remedy.  IMHO, of course.  

But I don't think Microsoft is confused on this point, not at all.  The
real questions in my mind are whether the server and client are correctly
configured, such that FreeTDS can convert correctly, and whether the
client is presenting to FreeTDS misencoded data (client set to ISO 8859-1
but data are Windows-1252, say).  If the configuration is correct and the
data are encoded according to the configuration, it should just work.  If
it doesn't, that's a bug I'd want to fix. 

I'm happy to help you with this if you want to get to the bottom of it.  

Regards, 

--jkl