Skip to Content.
Sympa Menu

freetds - Re: [freetds] hex(92), freeTDS and MS SQL

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] hex(92), freeTDS and MS SQL
  • Date: Fri, 22 Dec 2006 09:43:43 +0100

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





Archive powered by MHonArc 2.6.24.

Top of Page