freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

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

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: Wed, 20 Dec 2006 11:02:54 +0100

>
> ZIGLIO, Frediano, VF-IT <Frediano.Ziglio <at> vodafone.com> writes:
>
> > I didn't use sql server 7 since some years but if you use protocol
> > version 7.0 you shouldn't have a iso_1 !!!
>
> I am using 7.0, but:
>
> 22:03:26.150333 Starting log file for FreeTDS 0.63
> on 2006-12-19 22:03:26 with debug level 99.
> 22:03:26.150435 names for ISO-8859-1: ISO-8859-1
> 22:03:26.150451 names for UTF-8: UTF-8
> 22:03:26.150464 names for UCS-2LE: UCS-2LE
> 22:03:26.150477 names for UCS-2BE: UCS-2BE
> 22:03:26.150489 iconv to convert client-side data to the
> "UTF-8" character set
> 22:03:26.150514 tds_iconv_info_init: converting "UTF-8"->"UCS-2LE"
> 22:03:26.150533 tds_iconv_info_init: converting
> "ISO-8859-1"->"UCS-2LE"
> 22:03:26.150558 Connecting to A.B.C.D port 1433, TDS 7.0.
> [...]

It would be useful to know some part of ... but I think I can reproduce
the problem.

Let me say however that Microsoft use cp1252 instead of iso8859-1. For
instance html page by default are encoded in iso8859-1 while Microsoft
can use right quotation (what you call 92) or euro. Very bad.

For sql... well, let's try this on mssql2k

-- select * from ::fn_helpcollations()

-- Latin1_General_CI_AI
create table #tmp1 (c varchar(20) collate Latin1_General_CI_AI)
insert into #tmp1 values(cast(cast(0x92 as varbinary(2)) as varchar(2) )
collate Latin1_General_CI_AI)
select * from #tmp1
select cast(cast(c as nvarchar(20)) as varbinary(20)) from #tmp1
drop table #tmp1

-- SQL_Latin1_General_CP1_CI_AI
create table #tmp2 (c varchar(20) collate SQL_Latin1_General_CP1_CI_AI)
insert into #tmp2 values(cast(cast(0x92 as varbinary(2)) as varchar(2) )
collate SQL_Latin1_General_CP1_CI_AI)
select * from #tmp2
select cast(cast(c as nvarchar(20)) as varbinary(20)) from #tmp2
drop table #tmp2

!!! the result is.... CP1 and Latin1 is encoded in cp1252 !!!

Next question is why does mssql2k work correctly ??

$ TDSDUMP=stdout ./tsql -S XXXXXX -U xxxxxx
locale is "en_US.UTF-8"
locale charset is "UTF-8"
Password:
10:07:57.233580 (util.c:292):Starting log file for FreeTDS
0.65.dev.20061213
on 2006-12-20 10:07:57 with debug flags 0x6fff.
10:07:57.234664 (iconv.c:195):names for ISO-8859-1: ISO-8859-1
10:07:57.234776 (iconv.c:195):names for UTF-8: UTF-8
10:07:57.234877 (iconv.c:195):names for UCS-2LE: UCS-2LE
10:07:57.234979 (iconv.c:195):names for UCS-2BE: UCS-2BE
10:07:57.235081 (iconv.c:361):iconv to convert client-side data to the
"utf8" character set
10:07:57.235308 (iconv.c:514):tds_iconv_info_init: converting
"UTF-8"->"UCS-2LE"
10:07:57.235528 (iconv.c:514):tds_iconv_info_init: converting
"ISO-8859-1"->"UCS-2LE"
10:07:57.235700 (net.c:185):Connecting to 10.41.48.46 port 1433 (TDS
version 8.0)
10:07:57.237057 (net.c:240):connect(2) returned "Operation now in
progress"
10:07:57.237294 (net.c:726):Sending packet
0000 12 01 00 34 00 00 00 00-00 00 15 00 06 01 00 1b |...4.... ........|
0010 00 01 02 00 1c 00 0c 03-00 28 00 04 ff 08 00 01 |........ .(......|
0020 55 00 00 00 4d 53 53 51-4c 53 65 72 76 65 72 00 |U...MSSQ LServer.|
0030 8c 7d 00 00 - |.}..|

10:07:57.238280 (net.c:501):Received header
0000 04 01 00 25 00 00 01 00- |...%....|

10:07:57.238615 (net.c:581):Received packet
0000 00 00 15 00 06 01 00 1b-00 01 02 00 1c 00 01 03 |........ ........|
0010 00 1d 00 00 ff 08 00 07-f7 00 00 02 00 |........ .....|

10:07:57.238867 (login.c:897):detected flag 2
10:07:57.239033 (write.c:134):tds_put_string converting 7 bytes of
"xxxxxxx"
10:07:57.239254 (write.c:162):tds_put_string wrote 14 bytes
10:07:57.239364 (write.c:134):tds_put_string converting 7 bytes of
"xxxxxxx"
10:07:57.239479 (write.c:162):tds_put_string wrote 14 bytes
10:07:57.239592 (write.c:134):tds_put_string converting 4 bytes of
"TSQL"
10:07:57.239704 (write.c:162):tds_put_string wrote 8 bytes
10:07:57.239812 (write.c:134):tds_put_string converting 7 bytes of
"XXXXXXX"
10:07:57.239955 (write.c:162):tds_put_string wrote 14 bytes
10:07:57.240059 (write.c:134):tds_put_string converting 11 bytes of
"TDS-Library"
10:07:57.240174 (write.c:162):tds_put_string wrote 22 bytes
10:07:57.240281 (write.c:134):tds_put_string converting 10 bytes of
"us_english"
10:07:57.240392 (write.c:162):tds_put_string wrote 20 bytes
10:07:57.240498 (write.c:162):tds_put_string wrote 0 bytes
10:07:57.240611 (login.c:791):quietly sending TDS 7.0 login packet
10:07:57.240798 (token.c:312):tds_process_login_tokens()
10:07:57.241236 (net.c:501):Received header
0000 04 01 01 75 00 39 01 00- |...u.9..|

10:07:57.241483 (net.c:581):Received packet
0000 e3 1b 00 01 06 4c 00 67-00 6e 00 57 00 73 00 68 |.....L.g .n.W.s.h|
0010 00 06 6d 00 61 00 73 00-74 00 65 00 72 00 ab 64 |..m.a.s. t.e.r..d|
0020 00 45 16 00 00 02 00 25-00 43 00 68 00 61 00 6e |.E.....% .C.h.a.n|
0030 00 67 00 65 00 64 00 20-00 64 00 61 00 74 00 61 |.g.e.d. .d.a.t.a|
0040 00 62 00 61 00 73 00 65-00 20 00 63 00 6f 00 6e |.b.a.s.e . .c.o.n|
0050 00 74 00 65 00 78 00 74-00 20 00 74 00 6f 00 20 |.t.e.x.t . .t.o. |
0060 00 27 00 4c 00 67 00 6e-00 57 00 73 00 68 00 27 |.'.L.g.n .W.s.h.'|
...
0090 e3 17 00 02 0a 75 00 73-00 5f 00 65 00 6e 00 67 |.....u.s ._.e.n.g|
00a0 00 6c 00 69 00 73 00 68-00 00 ab 68 00 47 16 00 |.l.i.s.h ...h.G..|
00b0 00 01 00 27 00 43 00 68-00 61 00 6e 00 67 00 65 |...'.C.h .a.n.g.e|
00c0 00 64 00 20 00 6c 00 61-00 6e 00 67 00 75 00 61 |.d. .l.a .n.g.u.a|
00d0 00 67 00 65 00 20 00 73-00 65 00 74 00 74 00 69 |.g.e. .s .e.t.t.i|
00e0 00 6e 00 67 00 20 00 74-00 6f 00 20 00 75 00 73 |.n.g. .t .o. .u.s|
00f0 00 5f 00 65 00 6e 00 67-00 6c 00 69 00 73 00 68 |._.e.n.g .l.i.s.h|
...
0110 35 00 00 00 00 ad 36 00-01 71 00 00 01 16 4d 00 |5.....6. .q....M.|
0120 69 00 63 00 72 00 6f 00-73 00 6f 00 66 00 74 00 |i.c.r.o. s.o.f.t.|
0130 20 00 53 00 51 00 4c 00-20 00 53 00 65 00 72 00 | .S.Q.L. .S.e.r.|
0140 76 00 65 00 72 00 00 00-00 00 08 00 07 f7 e3 13 |v.e.r... ........|
0150 00 04 04 34 00 30 00 39-00 36 00 04 34 00 30 00 |...4.0.9 .6..4.0.|
0160 39 00 36 00 fd 00 00 00-00 00 00 00 00 |9.6..... .....|

10:07:57.243234 (token.c:316):looking for login token, got
e3(ENVCHANGE)
10:07:57.243358 (token.c:107):tds_process_default_tokens() marker is
e3(ENVCHANGE)
10:07:57.243523 (token.c:316):looking for login token, got ab(INFO)
10:07:57.243635 (token.c:107):tds_process_default_tokens() marker is
ab(INFO)
10:07:57.243741 (token.c:2404):tds_process_msg() reading message from
server
10:07:57.243898 (token.c:2469):tds_process_msg() calling client msg
handler
10:07:57.244004 (token.c:2482):tds_process_msg() returning TDS_SUCCEED
10:07:57.244109 (token.c:316):looking for login token, got
e3(ENVCHANGE)
10:07:57.244213 (token.c:107):tds_process_default_tokens() marker is
e3(ENVCHANGE)
10:07:57.245032 (iconv.c:514):tds_iconv_info_init: converting
"UTF-8"->"CP1252"

<--------- HERE you can see libTDS detect cp1252 and all works fine

10:07:57.245162 (token.c:316):looking for login token, got
e3(ENVCHANGE)
10:07:57.245272 (token.c:107):tds_process_default_tokens() marker is
e3(ENVCHANGE)
10:07:57.245397 (token.c:316):looking for login token, got ab(INFO)
10:07:57.245505 (token.c:107):tds_process_default_tokens() marker is
ab(INFO)
10:07:57.245611 (token.c:2404):tds_process_msg() reading message from
server
10:07:57.245743 (token.c:2469):tds_process_msg() calling client msg
handler
10:07:57.245849 (token.c:2482):tds_process_msg() returning TDS_SUCCEED
10:07:57.245951 (token.c:316):looking for login token, got ad(LOGINACK)
10:07:57.246075 (token.c:316):looking for login token, got
e3(ENVCHANGE)
10:07:57.246184 (token.c:107):tds_process_default_tokens() marker is
e3(ENVCHANGE)
10:07:57.246309 (token.c:2292):increasing block size from 4096 to 4096
10:07:57.246453 (token.c:316):looking for login token, got fd(DONE)
10:07:57.246568 (token.c:107):tds_process_default_tokens() marker is
fd(DONE)
10:07:57.246677 (token.c:2108):tds_process_end: more_results = 0
was_cancelled = 0
error = 0
done_count_valid = 0
10:07:57.246849 (token.c:2124):tds_process_end() state set to TDS_IDLE
10:07:57.246955 (util.c:120):Changing query state from IDLE to IDLE
10:07:57.247063 (token.c:389):leaving tds_process_login_tokens()
returning 1
10:07:57.247200 (util.c:120):Changing query state from IDLE to QUERYING
10:07:57.247320 (mem.c:567):tds_free_all_results()
10:07:57.247427 (write.c:134):tds_put_string converting 19 bytes of "set
textsize 64512 "
10:07:57.247546 (write.c:162):tds_put_string wrote 38 bytes
10:07:57.247663 (util.c:120):Changing query state from QUERYING to
PENDING
10:07:57.247772 (net.c:726):Sending packet
0000 01 01 00 2e 00 00 00 00-73 00 65 00 74 00 20 00 |........ s.e.t. .|
0010 74 00 65 00 78 00 74 00-73 00 69 00 7a 00 65 00 |t.e.x.t. s.i.z.e.|
0020 20 00 36 00 34 00 35 00-31 00 32 00 20 00 | .6.4.5. 1.2. .|

10:07:57.248187 (token.c:519):tds_process_tokens(0x9db8a30, 0xbfa489c8,
0xbfa489c4, 0x100)
10:07:57.248305 (util.c:120):Changing query state from PENDING to
READING
10:07:57.248519 (net.c:501):Received header
0000 04 01 00 11 00 39 01 00- |.....9..|

10:07:57.248718 (net.c:581):Received packet
0000 fd 00 00 be 00 00 00 00-00 |........ .|

10:07:57.248902 (token.c:534):processing result tokens. marker is
fd(DONE)
10:07:57.249016 (token.c:2108):tds_process_end: more_results = 0
was_cancelled = 0
error = 0
done_count_valid = 0
10:07:57.249227 (token.c:2124):tds_process_end() state set to TDS_IDLE
10:07:57.249330 (util.c:120):Changing query state from READING to IDLE
10:07:57.249438 (util.c:120):Changing query state from IDLE to PENDING
10:07:57.249545 (token.c:519):tds_process_tokens(0x9db8a30, 0xbfa489c8,
0xbfa489c4, 0x100)
10:07:57.249652 (token.c:522):tds_process_tokens() state is COMPLETED
1> select cast(cast(0x92 as varbinary(1)) as varchar(1)) collate
Latin1_General_CI_AI as c
2> go
10:08:00.025325 (util.c:120):Changing query state from IDLE to QUERYING
10:08:00.025497 (mem.c:567):tds_free_all_results()
10:08:00.025608 (write.c:134):tds_put_string converting 88 bytes of
"select cast(cast(0x92 as varbinary(1)) as varchar(1)) collate
Latin1_General_CI_AI as c
"
10:08:00.025758 (write.c:162):tds_put_string wrote 176 bytes
10:08:00.025864 (util.c:120):Changing query state from QUERYING to
PENDING
10:08:00.025969 (net.c:726):Sending packet
0000 01 01 00 b8 00 00 01 00-73 00 65 00 6c 00 65 00 |........ s.e.l.e.|
0010 63 00 74 00 20 00 63 00-61 00 73 00 74 00 28 00 |c.t. .c. a.s.t.(.|
0020 63 00 61 00 73 00 74 00-28 00 30 00 78 00 39 00 |c.a.s.t. (.0.x.9.|
0030 32 00 20 00 61 00 73 00-20 00 76 00 61 00 72 00 |2. .a.s. .v.a.r.|
0040 62 00 69 00 6e 00 61 00-72 00 79 00 28 00 31 00 |b.i.n.a. r.y.(.1.|
0050 29 00 29 00 20 00 61 00-73 00 20 00 76 00 61 00 |).). .a. s. .v.a.|
0060 72 00 63 00 68 00 61 00-72 00 28 00 31 00 29 00 |r.c.h.a. r.(.1.).|
0070 29 00 20 00 63 00 6f 00-6c 00 6c 00 61 00 74 00 |). .c.o. l.l.a.t.|
0080 65 00 20 00 4c 00 61 00-74 00 69 00 6e 00 31 00 |e. .L.a. t.i.n.1.|
0090 5f 00 47 00 65 00 6e 00-65 00 72 00 61 00 6c 00 |_.G.e.n. e.r.a.l.|
00a0 5f 00 43 00 49 00 5f 00-41 00 49 00 20 00 61 00 |_.C.I._. A.I. .a.|
00b0 73 00 20 00 63 00 0a 00- |s. .c...|

10:08:00.026934 (token.c:519):tds_process_tokens(0x9db8a30, 0xbfa48a18,
(nil), 0x6914)
10:08:00.027054 (util.c:120):Changing query state from PENDING to
READING
10:08:00.027649 (net.c:501):Received header
0000 04 01 00 27 00 39 01 00- |...'.9..|

10:08:00.027949 (net.c:581):Received packet
0000 81 01 00 00 00 01 00 a7-01 00 09 04 f0 00 00 01 |........ ........|
0010 63 00 d1 01 00 92 fd 10-00 c1 00 01 00 00 00 |c....... .......|

10:08:00.028237 (token.c:534):processing result tokens. marker is
81(TDS7_RESULT)
10:08:00.028365 (token.c:1418):processing TDS7 result metadata.
10:08:00.028468 (mem.c:567):tds_free_all_results()
10:08:00.028586 (token.c:1442):set current_results (1 column) to
tds->res_info
10:08:00.028691 (token.c:1453):setting up column 0
10:08:00.028826 (token.c:3339):adjust_character_column_size:
Server charset: CP1252
Server column_size: 1
Client charset: UTF-8
Client column_size: 4
10:08:00.029062 (token.c:1388):tds7_get_data_info:
colname = c (1 bytes)
type = 39 (varchar)
server's type = 167 (xvarchar)
column_varint_size = 2
column_size = 4 (1 on server)
10:08:00.029312 (util.c:120):Changing query state from READING to
PENDING
c
10:08:00.029448 (token.c:519):tds_process_tokens(0x9db8a30, 0xbfa48a18,
(nil), 0x6914)
10:08:00.029559 (util.c:120):Changing query state from PENDING to
READING
10:08:00.029666 (token.c:534):processing result tokens. marker is
d1(ROW)
10:08:00.029770 (token.c:680):tds_process_tokens::SET_RETURN stopping on
current token
10:08:00.029876 (util.c:120):Changing query state from READING to
PENDING
10:08:00.029984 (token.c:519):tds_process_tokens(0x9db8a30, 0xbfa48a18,
(nil), 0x1508)
10:08:00.030097 (util.c:120):Changing query state from PENDING to
READING
10:08:00.030202 (token.c:534):processing result tokens. marker is
d1(ROW)
10:08:00.030314 (token.c:2074):tds_process_row(): reading column 0
10:08:00.030422 (token.c:1826):tds_get_data: type 39, varint size 2
10:08:00.030530 (token.c:1883):tds_get_data(): wire column size is 1
10:08:00.030670 (util.c:120):Changing query state from READING to
PENDING
'

<-------- HERE you can see your right quote

10:08:00.030846 (token.c:519):tds_process_tokens(0x9db8a30, 0xbfa48a18,
(nil), 0x1508)
10:08:00.030961 (util.c:120):Changing query state from PENDING to
READING
10:08:00.031068 (token.c:534):processing result tokens. marker is
fd(DONE)
10:08:00.031178 (token.c:2108):tds_process_end: more_results = 0
was_cancelled = 0
error = 0
done_count_valid = 1
10:08:00.031396 (token.c:2124):tds_process_end() state set to TDS_IDLE
10:08:00.031499 (util.c:120):Changing query state from READING to IDLE
10:08:00.031605 (token.c:2140): rows_affected = 1
10:08:00.031707 (util.c:120):Changing query state from IDLE to PENDING
10:08:00.031843 (token.c:519):tds_process_tokens(0x9db8a30, 0xbfa48a18,
(nil), 0x6914)
10:08:00.031955 (token.c:522):tds_process_tokens() state is COMPLETED
1>

Using protocol 7.0

$ TDSVER=7.0 TDSDUMP=stdout ./tsql -S XXXXXXX -U xxxxxxx
locale is "en_US.UTF-8"
locale charset is "UTF-8"
Password:
10:15:02.182725 (util.c:292):Starting log file for FreeTDS
0.65.dev.20061213
on 2006-12-20 10:15:02 with debug flags 0x6fff.
10:15:02.184430 (iconv.c:195):names for ISO-8859-1: ISO-8859-1
10:15:02.184771 (iconv.c:195):names for UTF-8: UTF-8
10:15:02.185168 (iconv.c:195):names for UCS-2LE: UCS-2LE
10:15:02.185521 (iconv.c:195):names for UCS-2BE: UCS-2BE
10:15:02.185914 (iconv.c:361):iconv to convert client-side data to the
"utf8" character set
10:15:02.186469 (iconv.c:514):tds_iconv_info_init: converting
"UTF-8"->"UCS-2LE"
10:15:02.186920 (iconv.c:514):tds_iconv_info_init: converting
"ISO-8859-1"->"UCS-2LE"
10:15:02.187334 (net.c:185):Connecting to 10.41.48.46 port 1433 (TDS
version 7.0)
10:15:02.189005 (net.c:240):connect(2) returned "Operation now in
progress"
10:15:02.189599 (write.c:134):tds_put_string converting 7 bytes of
"xxxxxxx"
10:15:02.190015 (write.c:162):tds_put_string wrote 14 bytes
10:15:02.190352 (write.c:134):tds_put_string converting 7 bytes of
"xxxxxxx"
10:15:02.190746 (write.c:162):tds_put_string wrote 14 bytes
10:15:02.191099 (write.c:134):tds_put_string converting 4 bytes of
"TSQL"
10:15:02.191491 (write.c:162):tds_put_string wrote 8 bytes
10:15:02.191829 (write.c:134):tds_put_string converting 7 bytes of
"XXXXXXX"
10:15:02.192232 (write.c:162):tds_put_string wrote 14 bytes
10:15:02.192569 (write.c:134):tds_put_string converting 11 bytes of
"TDS-Library"
10:15:02.192967 (write.c:162):tds_put_string wrote 22 bytes
10:15:02.193305 (write.c:134):tds_put_string converting 10 bytes of
"us_english"
10:15:02.193721 (write.c:162):tds_put_string wrote 20 bytes
10:15:02.194058 (write.c:162):tds_put_string wrote 0 bytes
10:15:02.194439 (login.c:791):quietly sending TDS 7.0 login packet
10:15:02.194888 (token.c:312):tds_process_login_tokens()
10:15:02.196028 (net.c:501):Received header
0000 04 01 01 9c 00 38 01 00- |.....8..|

10:15:02.196994 (net.c:581):Received packet
0000 e3 1b 00 01 06 4c 00 67-00 6e 00 57 00 73 00 68 |.....L.g .n.W.s.h|
0010 00 06 6d 00 61 00 73 00-74 00 65 00 72 00 ab 64 |..m.a.s. t.e.r..d|
0020 00 45 16 00 00 02 00 25-00 43 00 68 00 61 00 6e |.E.....% .C.h.a.n|
0030 00 67 00 65 00 64 00 20-00 64 00 61 00 74 00 61 |.g.e.d. .d.a.t.a|
0040 00 62 00 61 00 73 00 65-00 20 00 63 00 6f 00 6e |.b.a.s.e . .c.o.n|
0050 00 74 00 65 00 78 00 74-00 20 00 74 00 6f 00 20 |.t.e.x.t . .t.o. |
0060 00 27 00 4c 00 67 00 6e-00 57 00 73 00 68 00 27 |.'.L.g.n .W.s.h.'|
...
0090 65 00 6e 00 67 00 6c 00-69 00 73 00 68 00 00 ab |e.n.g.l. i.s.h...|
00a0 68 00 47 16 00 00 01 00-27 00 43 00 68 00 61 00 |h.G..... '.C.h.a.|
00b0 6e 00 67 00 65 00 64 00-20 00 6c 00 61 00 6e 00 |n.g.e.d. .l.a.n.|
00c0 67 00 75 00 61 00 67 00-65 00 20 00 73 00 65 00 |g.u.a.g. e. .s.e.|
00d0 74 00 74 00 69 00 6e 00-67 00 20 00 74 00 6f 00 |t.t.i.n. g. .t.o.|
00e0 20 00 75 00 73 00 5f 00-65 00 6e 00 67 00 6c 00 | .u.s._. e.n.g.l.|
00f0 69 00 73 00 68 00 2e 00-07 XX XX XX XX XX XX XX |i.s.h... .XXXXXXX|
0100 XX XX XX XX XX XX 00 00-00 00 e3 0f 00 03 05 69 |XXXXXX.. .......i|
0110 00 73 00 6f 00 5f 00 31-00 01 00 00 e3 0b 00 05 |.s.o._.1 ........|
0120 04 31 00 30 00 33 00 33-00 00 e3 0f 00 06 06 31 |.1.0.3.3 .......1|
0130 00 39 00 36 00 36 00 30-00 39 00 00 ad 36 00 01 |.9.6.6.0 .9...6..|
0140 07 00 00 00 16 4d 00 69-00 63 00 72 00 6f 00 73 |.....M.i .c.r.o.s|
0150 00 6f 00 66 00 74 00 20-00 53 00 51 00 4c 00 20 |.o.f.t. .S.Q.L. |
0160 00 53 00 65 00 72 00 76-00 65 00 72 00 00 00 00 |.S.e.r.v .e.r....|
0170 00 08 00 07 f7 e3 13 00-04 04 34 00 30 00 39 00 |........ ..4.0.9.|
0180 36 00 04 34 00 30 00 39-00 36 00 fd 00 00 00 00 |6..4.0.9 .6......|
0190 00 00 00 00 - |....|

10:15:02.205639 (token.c:316):looking for login token, got
e3(ENVCHANGE)
10:15:02.205818 (token.c:107):tds_process_default_tokens() marker is
e3(ENVCHANGE)
10:15:02.206041 (token.c:316):looking for login token, got ab(INFO)
10:15:02.206151 (token.c:107):tds_process_default_tokens() marker is
ab(INFO)
10:15:02.206263 (token.c:2404):tds_process_msg() reading message from
server
10:15:02.206417 (token.c:2469):tds_process_msg() calling client msg
handler
10:15:02.206527 (token.c:2482):tds_process_msg() returning TDS_SUCCEED
10:15:02.206632 (token.c:316):looking for login token, got
e3(ENVCHANGE)
10:15:02.206735 (token.c:107):tds_process_default_tokens() marker is
e3(ENVCHANGE)
10:15:02.206887 (token.c:316):looking for login token, got ab(INFO)
10:15:02.206991 (token.c:107):tds_process_default_tokens() marker is
ab(INFO)
10:15:02.207103 (token.c:2404):tds_process_msg() reading message from
server
10:15:02.207234 (token.c:2469):tds_process_msg() calling client msg
handler
10:15:02.207341 (token.c:2482):tds_process_msg() returning TDS_SUCCEED
10:15:02.207442 (token.c:316):looking for login token, got
e3(ENVCHANGE)
10:15:02.207549 (token.c:107):tds_process_default_tokens() marker is
e3(ENVCHANGE)
10:15:02.207903 (iconv.c:514):tds_iconv_info_init: converting
"UTF-8"->"ISO-8859-1"

<----- HERE libTDS detect iso8859-1

10:15:02.208021 (token.c:316):looking for login token, got
e3(ENVCHANGE)
10:15:02.208129 (token.c:107):tds_process_default_tokens() marker is
e3(ENVCHANGE)
10:15:02.208250 (token.c:316):looking for login token, got
e3(ENVCHANGE)
10:15:02.208357 (token.c:107):tds_process_default_tokens() marker is
e3(ENVCHANGE)
10:15:02.208476 (token.c:316):looking for login token, got ad(LOGINACK)
10:15:02.208601 (token.c:316):looking for login token, got
e3(ENVCHANGE)
10:15:02.208734 (token.c:107):tds_process_default_tokens() marker is
e3(ENVCHANGE)
10:15:02.208859 (token.c:2292):increasing block size from 4096 to 4096
10:15:02.209000 (token.c:316):looking for login token, got fd(DONE)
10:15:02.209114 (token.c:107):tds_process_default_tokens() marker is
fd(DONE)
10:15:02.209225 (token.c:2108):tds_process_end: more_results = 0
was_cancelled = 0
error = 0
done_count_valid = 0
10:15:02.209430 (token.c:2124):tds_process_end() state set to TDS_IDLE
10:15:02.209547 (util.c:120):Changing query state from IDLE to IDLE
10:15:02.209657 (token.c:389):leaving tds_process_login_tokens()
returning 1
10:15:02.209790 (util.c:120):Changing query state from IDLE to QUERYING
10:15:02.209907 (mem.c:567):tds_free_all_results()
10:15:02.210015 (write.c:134):tds_put_string converting 19 bytes of "set
textsize 64512 "
10:15:02.210137 (write.c:162):tds_put_string wrote 38 bytes
10:15:02.210255 (util.c:120):Changing query state from QUERYING to
PENDING
10:15:02.210362 (net.c:726):Sending packet
0000 01 01 00 2e 00 00 00 00-73 00 65 00 74 00 20 00 |........ s.e.t. .|
0010 74 00 65 00 78 00 74 00-73 00 69 00 7a 00 65 00 |t.e.x.t. s.i.z.e.|
0020 20 00 36 00 34 00 35 00-31 00 32 00 20 00 | .6.4.5. 1.2. .|

10:15:02.210759 (token.c:519):tds_process_tokens(0x93fda30, 0xbf8e8868,
0xbf8e8864, 0x100)
10:15:02.210877 (util.c:120):Changing query state from PENDING to
READING
10:15:02.211132 (net.c:501):Received header
0000 04 01 00 11 00 38 01 00- |.....8..|

10:15:02.211360 (net.c:581):Received packet
0000 fd 00 00 be 00 00 00 00-00 |........ .|

10:15:02.211552 (token.c:534):processing result tokens. marker is
fd(DONE)
10:15:02.211661 (token.c:2108):tds_process_end: more_results = 0
was_cancelled = 0
error = 0
done_count_valid = 0
10:15:02.211871 (token.c:2124):tds_process_end() state set to TDS_IDLE
10:15:02.211972 (util.c:120):Changing query state from READING to IDLE
10:15:02.212078 (util.c:120):Changing query state from IDLE to PENDING
10:15:02.212181 (token.c:519):tds_process_tokens(0x93fda30, 0xbf8e8868,
0xbf8e8864, 0x100)
10:15:02.212316 (token.c:522):tds_process_tokens() state is COMPLETED
1> select cast(cast(0x92 as varbinary(1)) as varchar(1)) collate
Latin1_General_CI_AI as c
2> go
10:15:05.515046 (util.c:120):Changing query state from IDLE to QUERYING
10:15:05.515216 (mem.c:567):tds_free_all_results()
10:15:05.515334 (write.c:134):tds_put_string converting 88 bytes of
"select cast(cast(0x92 as varbinary(1)) as varchar(1)) collate
Latin1_General_CI_AI as c
"
10:15:05.515484 (write.c:162):tds_put_string wrote 176 bytes
10:15:05.515589 (util.c:120):Changing query state from QUERYING to
PENDING
10:15:05.515694 (net.c:726):Sending packet
0000 01 01 00 b8 00 00 01 00-73 00 65 00 6c 00 65 00 |........ s.e.l.e.|
0010 63 00 74 00 20 00 63 00-61 00 73 00 74 00 28 00 |c.t. .c. a.s.t.(.|
0020 63 00 61 00 73 00 74 00-28 00 30 00 78 00 39 00 |c.a.s.t. (.0.x.9.|
0030 32 00 20 00 61 00 73 00-20 00 76 00 61 00 72 00 |2. .a.s. .v.a.r.|
0040 62 00 69 00 6e 00 61 00-72 00 79 00 28 00 31 00 |b.i.n.a. r.y.(.1.|
0050 29 00 29 00 20 00 61 00-73 00 20 00 76 00 61 00 |).). .a. s. .v.a.|
0060 72 00 63 00 68 00 61 00-72 00 28 00 31 00 29 00 |r.c.h.a. r.(.1.).|
0070 29 00 20 00 63 00 6f 00-6c 00 6c 00 61 00 74 00 |). .c.o. l.l.a.t.|
0080 65 00 20 00 4c 00 61 00-74 00 69 00 6e 00 31 00 |e. .L.a. t.i.n.1.|
0090 5f 00 47 00 65 00 6e 00-65 00 72 00 61 00 6c 00 |_.G.e.n. e.r.a.l.|
00a0 5f 00 43 00 49 00 5f 00-41 00 49 00 20 00 61 00 |_.C.I._. A.I. .a.|
00b0 73 00 20 00 63 00 0a 00- |s. .c...|

10:15:05.516657 (token.c:519):tds_process_tokens(0x93fda30, 0xbf8e88b8,
(nil), 0x6914)
10:15:05.516776 (util.c:120):Changing query state from PENDING to
READING
10:15:05.520092 (net.c:501):Received header
0000 04 01 00 22 00 38 01 00- |...".8..|

10:15:05.520403 (net.c:581):Received packet
0000 81 01 00 00 00 01 00 a7-01 00 01 63 00 d1 01 00 |........ ...c....|
0010 92 fd 10 00 c1 00 01 00-00 00 |........ ..|

10:15:05.520650 (token.c:534):processing result tokens. marker is
81(TDS7_RESULT)
10:15:05.520772 (token.c:1418):processing TDS7 result metadata.
10:15:05.520879 (mem.c:567):tds_free_all_results()
10:15:05.521032 (token.c:1442):set current_results (1 column) to
tds->res_info
10:15:05.521145 (token.c:1453):setting up column 0
10:15:05.521281 (token.c:3339):adjust_character_column_size:
Server charset: ISO-8859-1
Server column_size: 1
Client charset: UTF-8
Client column_size: 4
10:15:05.521525 (token.c:1388):tds7_get_data_info:
colname = c (1 bytes)
type = 39 (varchar)
server's type = 167 (xvarchar)
column_varint_size = 2
column_size = 4 (1 on server)
10:15:05.521767 (util.c:120):Changing query state from READING to
PENDING
c
10:15:05.521933 (token.c:519):tds_process_tokens(0x93fda30, 0xbf8e88b8,
(nil), 0x6914)
10:15:05.522045 (util.c:120):Changing query state from PENDING to
READING
10:15:05.522153 (token.c:534):processing result tokens. marker is
d1(ROW)
10:15:05.522255 (token.c:680):tds_process_tokens::SET_RETURN stopping on
current token
10:15:05.522360 (util.c:120):Changing query state from READING to
PENDING
10:15:05.522467 (token.c:519):tds_process_tokens(0x93fda30, 0xbf8e88b8,
(nil), 0x1508)
10:15:05.522580 (util.c:120):Changing query state from PENDING to
READING
10:15:05.522683 (token.c:534):processing result tokens. marker is
d1(ROW)
10:15:05.522794 (token.c:2074):tds_process_row(): reading column 0
10:15:05.522901 (token.c:1826):tds_get_data: type 39, varint size 2
10:15:05.523009 (token.c:1883):tds_get_data(): wire column size is 1
10:15:05.523157 (util.c:120):Changing query state from READING to
PENDING

<------ HERE you can see a wrong conversion...

10:15:05.523335 (token.c:519):tds_process_tokens(0x93fda30, 0xbf8e88b8,
(nil), 0x1508)
10:15:05.523450 (util.c:120):Changing query state from PENDING to
READING
10:15:05.523553 (token.c:534):processing result tokens. marker is
fd(DONE)
10:15:05.523664 (token.c:2108):tds_process_end: more_results = 0
was_cancelled = 0
error = 0
done_count_valid = 1
10:15:05.523869 (token.c:2124):tds_process_end() state set to TDS_IDLE
10:15:05.523970 (util.c:120):Changing query state from READING to IDLE
10:15:05.524077 (token.c:2140): rows_affected = 1
10:15:05.524178 (util.c:120):Changing query state from IDLE to PENDING
10:15:05.524289 (token.c:519):tds_process_tokens(0x93fda30, 0xbf8e88b8,
(nil), 0x6914)
10:15:05.524398 (token.c:522):tds_process_tokens() state is COMPLETED
1>

So perhaps a better solution is:
- assume cp1252 is mssql say iso8859-1...

> 22:03:26.172954 processing TDS7 result. set current_results
> to tds->res_info
> 22:03:26.172972 tds_get_string: reading 2 from wire to give 1
> to client.
> 22:03:26.172989 tds7_get_data_info:1558:
> colname = i (1 bytes)
> type = 56 (int)
> server's type = 56 (int)
> column_varint_size = 0
> column_size = 4 (4 on server)
> 22:03:26.173008 adjust_character_column_size:
> Server charset: ISO-8859-1
> Server column_size: 50
> Client charset: UTF-8
> Client column_size: 200
>
> > With your patch you are just trying to set client charset to
> > cp1252. This can be done configuring correctly your freetds
> connection
> > (using freetds.conf).
>
> Nope, I changed the source encoding (the 1-byte on-the-wire
> format), not the
> destinatio (the client's), or at least... that's what I have
> meant to do (it was
> my first peek at FreeTDS source, so I may well be wrong).
>
> > You are using PHP so probably you have to set html
> > page output to cp1252 too. I don't see utf-8 at all...
>
> I used this .conf entry:
>
> host = A.B.C.D
> port = 1433
> tds version = 7.0
> client charset = UTF-8
>
> and the PHP subsystem is properly setup to use UTF-8 and
> output (even if this
> database is CP-silly-1252, I still have Unicode data floating
> around those pages
> and prefer to use for everything UTF-8 anyway).
>
> BTW, to everybody: thanks for the kind help, it's not so
> commonly found.
> Not at all =)
>

More on this

select * from master..syscharsets where name like 'cp%' order by name

!!! there is no cp1252 !!!

This patch should solve your problem (it just add 3 lines).

diff -u -1 -0 -r1.127 iconv.c
--- src/tds/iconv.c 7 Aug 2006 19:37:59 -0000 1.127
+++ src/tds/iconv.c 20 Dec 2006 09:52:50 -0000
@@ -970,20 +970,23 @@

/* change singlebyte conversions according to server */
void
tds_srv_charset_changed(TDSSOCKET * tds, const char *charset)
{
#if HAVE_ICONV_ALWAYS
TDSICONV *char_conv = tds->char_convs[client2server_chardata];

const char *canonic_charset =
tds_canonical_charset_name(charset);

+ if (tds->major_version >= 7 && strstr(charset, "iso") != 0)
+ canonic_charset = "CP1252";
+
/* ignore request to change to unknown charset */
if (!canonic_charset) {
tdsdump_log(TDS_DBG_FUNC, "tds_srv_charset_changed: what
is charset \"%s\"?\n", charset);
return;
}

if (strcmp(canonic_charset, char_conv->server_charset.name) ==
0)
return;

/* find and set conversion */

Perhaps it would be better, something like (this detect better
iso8859-1)

diff -u -1 -0 -r1.127 iconv.c
--- src/tds/iconv.c 7 Aug 2006 19:37:59 -0000 1.127
+++ src/tds/iconv.c 20 Dec 2006 09:59:09 -0000
@@ -968,27 +968,32 @@
return info;
}

/* change singlebyte conversions according to server */
void
tds_srv_charset_changed(TDSSOCKET * tds, const char *charset)
{
#if HAVE_ICONV_ALWAYS
TDSICONV *char_conv = tds->char_convs[client2server_chardata];

- const char *canonic_charset =
tds_canonical_charset_name(charset);
+ int canonic_charset_num = tds_canonical_charset(charset);
+ const char *canonic_charset;
+
+ if (tds->major_version >= 7 && canonic_charset_num ==
TDS_CHARSET_ISO_8859_1)
+ canonic_charset_num = TDS_CHARSET_CP1252;

/* ignore request to change to unknown charset */
- if (!canonic_charset) {
+ if (canonic_charset_num < 0) {
tdsdump_log(TDS_DBG_FUNC, "tds_srv_charset_changed: what
is charset \"%s\"?\n", charset);
return;
}
+ canonic_charset = canonic_charsets[canonic_charset_num].name;

if (strcmp(canonic_charset, char_conv->server_charset.name) ==
0)
return;

/* find and set conversion */
char_conv = tds_iconv_get_info(tds, canonic_charset);
if (char_conv)
tds->char_convs[client2server_chardata] = char_conv;

/* if sybase change also server conversions */

Oh... before I forgot. You can choose encoding in mssql7 during
installation but I don't know any way to change encoding after install.
Under mssql2k you can change a database encoding (not server one) with a
command.

bye
freddy77

Re: [freetds] hex(92), freeTDS and MS SQL, Lapo Luchini, 12/18/2006
- Re: [freetds] hex(92), freeTDS and MS SQL, James K. Lowden, 12/18/2006
  - Re: [freetds] hex(92), freeTDS and MS SQL, Lapo Luchini, 12/19/2006
    - Re: [freetds] hex(92), freeTDS and MS SQL, James K. Lowden, 12/19/2006
      - Re: [freetds] hex(92), freeTDS and MS SQL, Lapo Luchini, 12/19/2006
        
        Re: [freetds] hex(92), freeTDS and MS SQL, James K. Lowden, 12/21/2006
- <Possible follow-up(s)>
- Re: [freetds] hex(92), freeTDS and MS SQL, ZIGLIO, Frediano, VF-IT, 12/19/2006
  - Re: [freetds] hex(92), freeTDS and MS SQL, Lapo Luchini, 12/19/2006
- Re: [freetds] hex(92), freeTDS and MS SQL, ZIGLIO, Frediano, VF-IT, 12/20/2006
- Re: [freetds] hex(92), freeTDS and MS SQL, ZIGLIO, Frediano, VF-IT, 12/22/2006