Skip to Content.
Sympa Menu

freetds - Re: [freetds] Problems executing a query

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Riccardo Penco <r.penco AT scasinet.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Problems executing a query
  • Date: Fri, 17 Jun 2005 14:48:19 +0200

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



Archive powered by MHonArc 2.6.24.

Top of Page