FreeTDS Development Group

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

Lapo Luchini wrote:
> James K. Lowden <jklowden <at> freetds.org> writes:
> 
> > 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.  
> 
> The reason yesterday I told "SQL Server 7 knows nothing about it" is
> that I can't find anything about it...

It looks like we're both going to learn something.  Two suggestions:

1.  Cf.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_6ttf.asp
and
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_collation_8f8w.asp

2.  My sp_helpsort says:

        "Latin1-General, case-insensitive, accent-sensitive,
kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort
Order 52 on Code Page 1252 for non-Unicode Data"    

and sp_server_info says, in part:

        "charset=iso_1 sort_order=nocase_iso charset_num=1 sort_order_num=52"

Reading the above link, I think it may be that the server doesn't support
ISO 8859-1 as an encoding.  Encoding is based on 'code page', and 1252 is
the closest match.  

This led me to wonder if our server charset detection for TDS 7.0 is
working correctly.  Please remind me, what version of FreeTDS are you
using, and is upgrading an option?  

I added a little logging to this part of the code, see attached patch.  My
TDSDUMP log now shows:

token.c:107:tds_process_default_tokens() marker is e3(ENVCHANGE)
token.c:2312:server indicated charset change to "iso_1"
iconv.c:986:setting server single-byte charset to "ISO-8859-1"
iconv.c:514:tds_iconv_info_init: converting "US-ASCII"->"ISO-8859-1"

My server reports iso_1.  That matches what sp_server_info reports.  OK,
so what does Microsoft mean by iso_1?  I know your system reported it to
be ISO 8859-1, but look here, I dare you:

http://www.microsoft.com/globaldev/reference/cphome.mspx

Answer: They don't say.  Tell me I'm wrong.  

This I know for sure: cp1252 is not an ISO anything.  So I would like to
believe that iso_1 doesn't mean code page 1252.  

On my server, this is what I see:

$ TDSDUMP=dump build/src/apps/tsql -S varley -U jkl -P jkl 
locale is "C"
locale charset is "646"
1> use FreeTDS
2> go
1> create table ch(c char(1))
2> go
1> insert ch values (char(146)) select * from ch
2> go
c       
Msg 2403, Level 16, State 0, Server OpenClient, Line 0
WARNING! Some character(s) could not be converted into client's character
set. Unconverted bytes were changed to question marks ('?').
?       
1> 

The insert worked.  Should it?  char(192) (0x92) is not a valid ISO 8859-1
character.  But I can also insert char(144) (0x90) which is not even a
valid windows-1252 character!  

As we said before, the server lets you declare your charset, and reports
(after a fashion) what you declared.  It doesn't prevent you from
inserting mis-encoded data.  
 
> 1> sp_helpsort
> 2> go
> Unicode data sorting
> Locale ID = 1033
>  case insensitive, kana type insensitive, width insensitive
>  
>  
> Sort Order Description
> Character Set = 1, iso_1
>     ISO 8859-1 (Latin-1) - Western European 8-bit character set.
> Sort Order = 52, nocase_iso
>     Case-insensitive dictionary sort order for use with several We
>     stern-European languages including English, French, and German
>     .  Uses the ISO 8859-1 character set.
> Characters, in Order
>     ! " # $ % & ' ( ) * + , - . / : ; < = > ? @ [ \ ] ^ _ ` { | }
> Msg 2403, Level 16, State 0, Server OpenClient, Line 0
> WARNING! Some character(s) could not be converted into client's
> character set. U nconverted bytes were changed to question marks ('?').
> [follows the rest of the character table]

Wonderful.  Not.  This confirms our suspicion, what you called "confusion"
at Microsoft.  If only I thought it were so innocent.  

They say you're using ISO 8859-1.  In the same breath, they list the
characters in that charset, and FreeTDS complains it can't convert some of
them.  Why?  Because, I suspect, it's not *really* ISO conformant; it's
Microsoft's 1252 extension.  Embrace, extend, extinquish.  Or try to,
anyway.  

> OK. So you think to use Latin1, as you also tell to FreeTDS, which of
> course believes you, as it should. Only problem is, I don see no place
> to change (or even *see*) that setting in the server, and no reference
> in the online help too.

Sad to say, I think your server is set correctly.  And nonconformant.  

> So I tought: let's try nvarchar 

Good idea.  To eliminate FreeTDS from the processing, I did everything on
the server:

1> select cast(cast(cast(char(128) as nchar(1)) as char(1)) as binary)
2> go
        
800000000000000000000000000000000000000000000000000000000000    

That would seem to say that we're using windows-1252, because we convert
0x80, the Euro symbol in windows-1252, to Unicode (U+20ac) and then back
to our character set.  And lo and behold, it's 0x80, which doesn't exist
in ISO 8859-1 and *is* the Euro symbol in windows-1252.  I can use iconv
to demonstrate the same thing:

$ printf "\x80"  | iconv -f cp1252 -t UTF-16 | hexdump -C
00000000  fe ff 20 ac                                       |.. .|

ISO 8859-15 puts the Euro symbol in 0xa4:

$ printf "\xa4"  | iconv -f iso8859-15 -t UTF-16 | hexdump -C
00000000  fe ff 20 ac                                       |.. .|

$ printf "\x20\xac"  | iconv -t iso8859-15 -f UTF-16 | hexdump -C
00000000  a4                                                |.|

What does the server say about 0xa4 (decimal 164)?  In Unicode, U+00a4 is
the "currency symbol":

1> select cast(c as binary) from ch -- column [c] is char(1)
2> go
        
a40000000000000000000000000000000000000000000000000000000000    
800000000000000000000000000000000000000000000000000000000000    
1> delete nch
2> go
1> insert nch select * from ch
2> go
1> select cast(ch as binary) from nch -- column [ch] is nchar(1)
2> go
        
a40000000000000000000000000000000000000000000000000000000000    
ac2000000000000000000000000000000000000000000000000000000000    

That's a windows-1252 answer, too, cf.:

$ printf "\xa4"  | iconv -f cp1252 -t UTF-16 | hexdump -C
00000000  fe ff 00 a4    

So let's try something truly wild.  Let's use an invalid windows-1252
value:

$ printf "\x90"  | iconv -f cp1252 -t UTF-16 | hexdump -C
iconv: (stdin): cannot convert

Server says?

1> select cast(cast(cast(char(144) as nchar(1)) as char(1)) as binary)
2> go
        
900000000000000000000000000000000000000000000000000000000000    

Now, I'm sorry, but what can that mean?  Decimal 144 (0x90) has no meaning
in windows-1252 or ISO 8859-1.  How can it be converted to Unicode?  

+++

Conclusions:

1.  My server says it's configured as iso_1.  That's what sp_server_info
says, and that's what the ENVCHANGE packet says.  
2.  Unlike, say, Sybase, iso_1 behaves not as ISO 8859-1 but as
windows-1252.  
3.  Invalid windows-1252 values are "converted" on the server as
pass-through binary copies.  
4.  Anything can be stored in a char(1) field, no matter the collation or
charset declaration. 

Based on the above, I agree with Frediano: when we see iso_1 on a TDS 7.0+
connection, we have to treat it as windows-1252, pending better
information.  His patch is similar to yours but narrower and so IMO more
"correct".  

As I said at the top of this very long message, I think we both learned
something.  I just wish I like the answer better.  

Regards, 

--jkl

Attachment: iconv.diff
Description: Binary data