FreeTDS Development Group

[Lapo Luchini <lapo AT lapo.it>]

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