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: Lapo Luchini <lapo AT lapo.it>
  • To: freetds AT lists.ibiblio.org
  • Subject: Re: [freetds] hex(92), freeTDS and MS SQL
  • Date: Tue, 19 Dec 2006 21:29:00 +0000 (UTC)

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

Even using the "search" function of the online help I can find no reference at
all of the following words:
charset, 1252, cp1252, windows 1252, latin1, latin 1, iso-8859-1

And about the following:
unicode: only links about the Data Transformation Services
collation: only a single link about "linked servers"

I also navigated all the tabs of the "properties" dialogs of: table, database,
server.
In the mail tab of the server "properties" dialog there is a label: "codepage:
1252".

And that's kinda all of it.

Then I searched over and over in microsoft.com docs, and I managed to discover
the following text:
http://www.microsoft.com/technet/community/chats/trans/sql/sql0305.mspx
with a vague reference to the command "sp_helpsort", which I executed, with
the
following result:

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]

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.

So I tought: let's try nvarchar (which would not solve my problem, because I
can't convert "the real" database, but I already "solved" the work issue with
that crude patch: now it's between ME and HIM, and I want to sort this out
;-)).

CREATE TABLE [P] (
[i] [int] IDENTITY (1, 1) NOT NULL,
[c] [varchar] (50) NULL ,
[n] [nvarchar] (50) NULL
)

Then, using the management console I inserted two rows:
1,‘’à“”,‘’à“”
2,私,私
(in the probable case line 2 gets mangled, it was meant to be the kanji for
"watashi", that is Unicode 79C1)

Using a "clean" FreeTDS and UTF-8 on the client just to be sure that anything
can be converted, I ran the following PHP script:

mssql_query("INSERT INTO P VALUES ('私','私')");
$q = mssql_query('SELECT * FROM P');
while ($row = mssql_fetch_row($q)) {
$row[11] = bin2hex($row[1]);
$row[12] = bin2hex($row[2]);
print_r($row);
}

with the following result:

Array
(
[0] => 1
[1] => ‘’à“”
[2] => ‘’à“”
[11] => c291c292c3a0c293c294
[12] => e28098e28099c3a0e2809ce2809d
)
Array
(
[0] => 5
[1] => ?
[2] => ?
[11] => 3f
[12] => 3f
)
Array
(
[0] => 10
[1] => ?
[2] => ?
[11] => 3f
[12] => 3f
)

At the very least column "2", being transferred on the wire in UCS-2, arrives
"safe", but I wonder why the absolutely-regular-unicode japanese character
U+79C1 can't both be received and sent.
It almost seems as tough SQLServer7 is trying to store that as CP1252 even in
a
nvarchar column??? Nay, I can't believe that myself, but I wonder.

BTW: using my "dirty patch" also column 1 arrives ok, with identical content
to
column 2, no change at all with the second row

And btw, yes, FreeTDS does send it OKAY on the wire:

22:03:26.175219 Sending packet
0000 01 01 00 46 00 00 01 00-49 00 4e 00 53 00 45 00 |...F.... I.N.S.E.|
0010 52 00 54 00 20 00 49 00-4e 00 54 00 4f 00 20 00 |R.T. .I. N.T.O. .|
0020 50 00 20 00 56 00 41 00-4c 00 55 00 45 00 53 00 |P. .V.A. L.U.E.S.|
0030 20 00 28 00 27 00 c1 79-27 00 2c 00 20 00 27 00 | .(.'.Áy '.,. .'.|
0040 c1 79 27 00 29 00 - |Áy'.).|

The two inverted 79C1 UCS-2 character are quite visible and seem correct to
me.

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

Well, AFAICT it can't be changed, but I still hope to be wrong.

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

I sure do =)

Lapo





Archive powered by MHonArc 2.6.24.

Top of Page