FreeTDS Development Group

["ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>]

> 
> 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.  
> 

So I think we all agree to use cp1252 instead of iso8859-1 for "iso".
As usual you can find patch post 0.64 at
http://freetds.sourceforge.net/.

freddy77