FreeTDS Development Group

[Riccardo Penco <r.penco AT scasinet.com>]

James K. Lowden ha scritto:
> Riccardo Penco wrote:
>
> > If in a php script I write:
> >
> > $sql = "SELECT Città from PDV";
> > $res = mssql_query($sql, $conn);
> >
> > I obtain the following error message:
> > Warning: mssql_query(): Error converting client characters into server's
> >
> > character set. Some character(s) could not be converted. (severity 16)
>
>
> Does the query work in tsql?  It should.  

Yes, it works

and note that if I connect using "SERVER_NAME" as configured in 
freetds.conf (ie tds version = 8.0 and client charset = UTF-8) then the 
script doesn't works BUT if I use the IP address (ie using tds version = 
4.2) then it works!

$conn = mssql_connect("SERVER_NAME", user, pass) => doesn't work

$conn = mssql_connect("IP_ADDRESS", user, pass) => works

> SQL Server 2000 encodes database object names in UCS-2, which obviously
> can represent 'Città'.  The error message may be telling you that the à is
> arriving not as UTF-8 (2 bytes) but as ISO 8859-1 (1 byte, high bit on). 
> Attempting to interpret ISO 8859-1 as UTF-8 will elicit that message.  
>
> A TDSDUMP log will provide more detailed information about the failed
> conversion.  

I've attached the log file (hope is the right thing to do)

> HTH.  
>
> --jkl

thanks
riki
Starting log file for FreeTDS 0.62.4
        on 2005-06-17 14:30:39 with debug level 99.
names for ISO-8859-1: ISO-8859-1
names for UTF-8: UTF-8
names for UCS-2LE: UCS-2LE
names for UCS-2BE: UCS-2BE
iconv to convert client-side data to the "UTF-8" character set
14:30:39.704338 tds_iconv_info_init: converting "UTF-8"->"UCS-2LE"
14:30:39.704422 tds_iconv_info_init: converting "ISO-8859-1"->"UCS-2LE"
14:30:39.704467 Connecting to 192.168.0.10 port 1433, TDS 8.0.
14:30:39.787075 tds_put_string converting 29 bytes of 
"fileserver.local.scasinet.com"
14:30:39.787224 tds_put_string wrote 58 bytes
14:30:39.787258 tds_put_string converting 5 bytes of "xxxxx"
14:30:39.787290 tds_put_string wrote 10 bytes
14:30:39.787322 tds_put_string converting 7 bytes of "PHP 4.0"
14:30:39.787350 tds_put_string wrote 14 bytes
14:30:39.787378 tds_put_string converting 8 bytes of "SQLSRV2K"
14:30:39.787409 tds_put_string wrote 16 bytes
14:30:39.787440 tds_put_string converting 10 bytes of "DB-Library"
14:30:39.787470 tds_put_string wrote 20 bytes
14:30:39.787501 tds_put_string converting 10 bytes of "us_english"
14:30:39.787532 tds_put_string wrote 20 bytes
14:30:39.787561 tds_put_string wrote 0 bytes
14:30:39.787703 tds_process_login_tokens()
Received header @ 14:30:39.788583
0000 04 01 01 a3 00 35 01 00-                        |.....5..|


Received packet @ 14:30:39.788655
0000 e3 1d 00 01 07 4d 00 48-00 53 00 32 00 30 00 30 |.....M.H .S.2.0.0|
0010 00 30 00 06 6d 00 61 00-73 00 74 00 65 00 72 00 |.0..m.a. s.t.e.r.|
0020 ab 8e 00 45 16 00 00 02-00 39 00 49 00 6c 00 20 |...E.... .9.I.l. |
0030 00 63 00 6f 00 6e 00 74-00 65 00 73 00 74 00 6f |.c.o.n.t .e.s.t.o|
0040 00 20 00 64 00 69 00 20-00 64 00 61 00 74 00 61 |. .d.i.  .d.a.t.a|
0050 00 62 00 61 00 73 00 65-00 20 00 e8 00 20 00 73 |.b.a.s.e . ... .s|
0060 00 74 00 61 00 74 00 6f-00 20 00 73 00 6f 00 73 |.t.a.t.o . .s.o.s|
0070 00 74 00 69 00 74 00 75-00 69 00 74 00 6f 00 20 |.t.i.t.u .i.t.o. |
0080 00 63 00 6f 00 6e 00 20-00 27 00 4d 00 48 00 53 |.c.o.n.  .'.M.H.S|
0090 00 32 00 30 00 30 00 30-00 27 00 2e 00 08 53 00 |.2.0.0.0 .'....S.|
00a0 51 00 4c 00 53 00 52 00-56 00 32 00 4b 00 00 00 |Q.L.S.R. V.2.K...|
00b0 00 e3 08 00 07 05 09 04-d0 00 34 00 e3 17 00 02 |........ ..4.....|
00c0 0a 75 00 73 00 5f 00 65-00 6e 00 67 00 6c 00 69 |.u.s._.e .n.g.l.i|
00d0 00 73 00 68 00 00 ab 6a-00 47 16 00 00 01 00 27 |.s.h...j .G.....'|
00e0 00 43 00 68 00 61 00 6e-00 67 00 65 00 64 00 20 |.C.h.a.n .g.e.d. |
00f0 00 6c 00 61 00 6e 00 67-00 75 00 61 00 67 00 65 |.l.a.n.g .u.a.g.e|
0100 00 20 00 73 00 65 00 74-00 74 00 69 00 6e 00 67 |. .s.e.t .t.i.n.g|
0110 00 20 00 74 00 6f 00 20-00 75 00 73 00 5f 00 65 |. .t.o.  .u.s._.e|
0120 00 6e 00 67 00 6c 00 69-00 73 00 68 00 2e 00 08 |.n.g.l.i .s.h....|
0130 53 00 51 00 4c 00 53 00-52 00 56 00 32 00 4b 00 |S.Q.L.S. R.V.2.K.|
0140 00 00 00 ad 36 00 01 71-00 00 01 16 4d 00 69 00 |....6..q ....M.i.|
0150 63 00 72 00 6f 00 73 00-6f 00 66 00 74 00 20 00 |c.r.o.s. o.f.t. .|
0160 53 00 51 00 4c 00 20 00-53 00 65 00 72 00 76 00 |S.Q.L. . S.e.r.v.|
0170 65 00 72 00 00 00 00 00-08 00 02 f8 e3 13 00 04 |e.r..... ........|
0180 04 34 00 30 00 39 00 36-00 04 34 00 30 00 39 00 |.4.0.9.6 ..4.0.9.|
0190 36 00 fd 00 00 00 00 00-00 00 00                |6....... ...|


14:30:39.788816 looking for login token, got  e3(ENVCHANGE)
14:30:39.788844 tds_process_default_tokens() marker is e3(ENVCHANGE)
tds_get_string: reading 14 from wire to give 7 to client.
tds_get_string: reading 12 from wire to give 6 to client.
14:30:39.788899 looking for login token, got  ab(INFO)
14:30:39.788922 tds_process_default_tokens() marker is ab(INFO)
tds_get_string: reading 114 from wire to give 57 to client.
tds_get_string: reading 16 from wire to give 8 to client.
14:30:39.788981 looking for login token, got  e3(ENVCHANGE)
14:30:39.789004 tds_process_default_tokens() marker is e3(ENVCHANGE)
14:30:39.824163 tds_iconv_info_init: converting "UTF-8"->"CP1252"
14:30:39.824507 looking for login token, got  e3(ENVCHANGE)
14:30:39.824568 tds_process_default_tokens() marker is e3(ENVCHANGE)
tds_get_string: reading 20 from wire to give 10 to client.
14:30:39.824736 looking for login token, got  ab(INFO)
14:30:39.824817 tds_process_default_tokens() marker is ab(INFO)
tds_get_string: reading 78 from wire to give 39 to client.
tds_get_string: reading 16 from wire to give 8 to client.
14:30:39.824962 looking for login token, got  ad(LOGINACK)
tds_get_string: reading 44 from wire to give 22 to client.
14:30:39.825015 looking for login token, got  e3(ENVCHANGE)
14:30:39.825063 tds_process_default_tokens() marker is e3(ENVCHANGE)
tds_get_string: reading 8 from wire to give 4 to client.
tds_get_string: reading 8 from wire to give 4 to client.
14:30:39.825145 increasing block size from 4096 to 4096
14:30:39.825199 looking for login token, got  fd(DONE)
14:30:39.825242 tds_process_default_tokens() marker is fd(DONE)
14:30:39.825292 tds_process_end: more_results = 0
                was_cancelled = 0
                error = 0
                done_count_valid = 0
14:30:39.825342 tds_process_end() state set to TDS_IDLE
14:30:39.825387 leaving tds_process_login_tokens() returning 1
14:30:39.825487 tds_put_string converting 19 bytes of "set textsize 64512 "
14:30:39.825550 tds_put_string wrote 38 bytes
Sending packet @ 14:30:39.825599
0000 01 01 00 2e 00 00 01 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. .|


Received header @ 14:30:39.826299
0000 04 01 00 11 00 35 01 00-                        |.....5..|


Received packet @ 14:30:39.826386
0000 fd 00 00 be 00 00 00 00-00                      |........ .|


14:30:39.826414 processing result tokens.  marker is  fd(DONE)
14:30:39.826438 tds_process_end: more_results = 0
                was_cancelled = 0
                error = 0
                done_count_valid = 0
14:30:39.826463 tds_process_end() state set to TDS_IDLE
14:30:39.826485 tds_process_result_tokens() state is COMPLETED
14:30:39.826586 dbuse()
14:30:39.826611 dbcmd() bufsz = 0
14:30:39.826633 in dbsqlexec()
14:30:39.826655 in dbsqlsend()
14:30:39.826680 tds_put_string converting 11 bytes of "use MHS2000"
14:30:39.826708 tds_put_string wrote 22 bytes
Sending packet @ 14:30:39.826732
0000 01 01 00 1e 00 00 01 00-75 00 73 00 65 00 20 00 |........ u.s.e. .|
0010 4d 00 48 00 53 00 32 00-30 00 30 00 30 00       |M.H.S.2. 0.0.0.|


14:30:39.826801 in dbsqlok() 
Received header @ 14:30:39.827186
0000 04 01 00 a9 00 35 01 00-                        |.....5..|


Received packet @ 14:30:39.827242
0000 e3 1f 00 01 07 4d 00 48-00 53 00 32 00 30 00 30 |.....M.H .S.2.0.0|
0010 00 30 00 07 4d 00 48 00-53 00 32 00 30 00 30 00 |.0..M.H. S.2.0.0.|
0020 30 00 ab 68 00 45 16 00-00 01 00 26 00 43 00 68 |0..h.E.. ...&.C.h|
0030 00 61 00 6e 00 67 00 65-00 64 00 20 00 64 00 61 |.a.n.g.e .d. .d.a|
0040 00 74 00 61 00 62 00 61-00 73 00 65 00 20 00 63 |.t.a.b.a .s.e. .c|
0050 00 6f 00 6e 00 74 00 65-00 78 00 74 00 20 00 74 |.o.n.t.e .x.t. .t|
0060 00 6f 00 20 00 27 00 4d-00 48 00 53 00 32 00 30 |.o. .'.M .H.S.2.0|
0070 00 30 00 30 00 27 00 2e-00 08 53 00 51 00 4c 00 |.0.0.'.. ..S.Q.L.|
0080 53 00 52 00 56 00 32 00-4b 00 00 01 00 e3 08 00 |S.R.V.2. K.......|
0090 07 05 09 04 d0 00 34 00-fd 00 00 e2 00 00 00 00 |......4. ........|
00a0 00                     -                        |.|


14:30:39.827374 dbsqlok() marker is e3
14:30:39.827405 processing result tokens.  marker is  e3(ENVCHANGE)
14:30:39.827436 tds_process_default_tokens() marker is e3(ENVCHANGE)
tds_get_string: reading 14 from wire to give 7 to client.
tds_get_string: reading 14 from wire to give 7 to client.
14:30:39.827498 processing result tokens.  marker is  ab(INFO)
14:30:39.827529 tds_process_default_tokens() marker is ab(INFO)
tds_get_string: reading 76 from wire to give 38 to client.
tds_get_string: reading 16 from wire to give 8 to client.
14:30:39.827588 processing result tokens.  marker is  e3(ENVCHANGE)
14:30:39.827618 tds_process_default_tokens() marker is e3(ENVCHANGE)
14:30:39.827654 processing result tokens.  marker is  fd(DONE)
14:30:39.827685 tds_process_end: more_results = 0
                was_cancelled = 0
                error = 0
                done_count_valid = 0
14:30:39.827719 tds_process_end() state set to TDS_IDLE
14:30:39.827767 dbsqlok() end status was success
14:30:39.827801 dbresults()
14:30:39.827830 dbresults_r()
14:30:39.827858 tds_process_result_tokens() state is COMPLETED
14:30:39.827888 leaving dbresults() returning 1
14:30:39.827918 tds_process_row_tokens() state is COMPLETED
14:30:39.827964 dbcmd() bufsz = 12
14:30:39.827993 in dbfreebuf()
14:30:39.828023 in dbsqlexec()
14:30:39.828050 in dbsqlsend()
14:30:39.828083 tds_put_string converting 76 bytes of "SELECT TOP 100 Nome, 
Città FROM PDV where città like '%cant%' ORDER BY Città"
14:30:39.828237 tds_client_msg: #2402: "Error converting client characters 
into server's character set. Some character(s) could not be converted.".  
Connection state is now 0.  
14:30:39.828272 Error: tds_put_string: Gave up converting 51 bytes due to 
error 84.
        Troublesome bytes:
        0000 e0 20 46 52 4f 4d 20 50-44 56 20 77 68 65 72 65 |. FROM P DV 
where|
0010 20 63 69 74 74 e0 20 6c-69 6b 65 20 27 25 63 61 | citt. l ike '%ca|
0020 6e 74 25 27 20 4f 52 44-45 52 20 42 59 20 43 69 |nt%' ORD ER BY Ci|
0030 74 74 e0               -                        |tt.|


14:30:39.828341 tds_put_string converting 51 bytes of "à FROM PDV where città 
like '%cant%' ORDER BY Città"
14:30:39.828372 Error: tds_put_string: Gave up converting 51 bytes due to 
error 84.
        Troublesome bytes:
        0000 e0 20 46 52 4f 4d 20 50-44 56 20 77 68 65 72 65 |. FROM P DV 
where|
0010 20 63 69 74 74 e0 20 6c-69 6b 65 20 27 25 63 61 | citt. l ike '%ca|
0020 6e 74 25 27 20 4f 52 44-45 52 20 42 59 20 43 69 |nt%' ORD ER BY Ci|
0030 74 74 e0               -                        |tt.|


14:30:39.828442 Error: tds_put_string: No conversion possible, giving up.
14:30:39.828472 tds_put_string wrote 50 bytes
Sending packet @ 14:30:39.828501
0000 01 01 00 3a 00 00 01 00-53 00 45 00 4c 00 45 00 |...:.... S.E.L.E.|
0010 43 00 54 00 20 00 54 00-4f 00 50 00 20 00 31 00 |C.T. .T. O.P. .1.|
0020 30 00 30 00 20 00 4e 00-6f 00 6d 00 65 00 2c 00 |0.0. .N. o.m.e.,.|
0030 20 00 43 00 69 00 74 00-74 00                   | .C.i.t. t.|


14:30:39.828621 in dbsqlok() 
Received header @ 14:30:39.829189
0000 04 01 00 bb 00 35 01 00-                        |.....5..|


Received packet @ 14:30:39.829247
0000 aa 52 00 cf 00 00 00 03-10 1b 00 49 00 6e 00 76 |.R...... ...I.n.v|
0010 00 61 00 6c 00 69 00 64-00 20 00 63 00 6f 00 6c |.a.l.i.d . .c.o.l|
0020 00 75 00 6d 00 6e 00 20-00 6e 00 61 00 6d 00 65 |.u.m.n.  .n.a.m.e|
0030 00 20 00 27 00 4e 00 6f-00 6d 00 65 00 27 00 2e |. .'.N.o .m.e.'..|
0040 00 08 53 00 51 00 4c 00-53 00 52 00 56 00 32 00 |..S.Q.L. S.R.V.2.|
0050 4b 00 00 01 00 aa 52 00-cf 00 00 00 03 10 1b 00 |K.....R. ........|
0060 49 00 6e 00 76 00 61 00-6c 00 69 00 64 00 20 00 |I.n.v.a. l.i.d. .|
0070 63 00 6f 00 6c 00 75 00-6d 00 6e 00 20 00 6e 00 |c.o.l.u. m.n. .n.|
0080 61 00 6d 00 65 00 20 00-27 00 43 00 69 00 74 00 |a.m.e. . '.C.i.t.|
0090 74 00 27 00 2e 00 08 53-00 51 00 4c 00 53 00 52 |t.'....S .Q.L.S.R|
00a0 00 56 00 32 00 4b 00 00-01 00 fd 02 00 fd 00 00 |.V.2.K.. ........|
00b0 00 00 00               -                        |...|


14:30:39.829391 dbsqlok() marker is aa
14:30:39.829423 processing result tokens.  marker is  aa(ERROR)
14:30:39.829454 tds_process_default_tokens() marker is aa(ERROR)
tds_get_string: reading 54 from wire to give 27 to client.
tds_get_string: reading 16 from wire to give 8 to client.
14:30:39.829567 tds_client_msg: #20018: "General SQL Server error: Check 
messages from the SQL Server.".  Connection state is now 0.  
14:30:39.829603 processing result tokens.  marker is  aa(ERROR)
14:30:39.829632 tds_process_default_tokens() marker is aa(ERROR)
tds_get_string: reading 54 from wire to give 27 to client.
tds_get_string: reading 16 from wire to give 8 to client.
14:30:39.829727 tds_client_msg: #20018: "General SQL Server error: Check 
messages from the SQL Server.".  Connection state is now 0.  
14:30:39.829761 processing result tokens.  marker is  fd(DONE)
14:30:39.829798 tds_process_end: more_results = 0
                was_cancelled = 0
                error = 1
                done_count_valid = 0
14:30:39.829832 tds_process_end() state set to TDS_IDLE
14:30:39.829876 dbsqlok() end status was error
14:30:39.830183 in dbfreebuf()