Skip to Content.
Sympa Menu

freetds - Re: [freetds] SuSE Linux: columns of resultset don't match for MSSQL Server 2000

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] SuSE Linux: columns of resultset don't match for MSSQL Server 2000
  • Date: Mon, 7 Jan 2008 17:31:01 +0100

>
> Hello,
>
> if i use MS Query Analyzer for the following query :
>
> select * from hds where PlanMA = 'KLINGLKA' and
> CONVERT(DATETIME,[Day],104) = CONVERT(DATETIME,'02.01.2008',104)
>
> i get the (right) result:
>
> Mo Di Mi Do Fr Sa So Day
> EXPR1 EXPR2 PlanMA
> ------ ------ ------ ------ ------ ------ ------
> -----------------------
> ----- ---------------- --------------------
> 0 0 162 56 0 0 0 2008-01-02
> 00:00:00.000
> HD Summe IT-Support KLINGLKA
>
>
> On SUSE Linux Enterprise 10 with freetds in isql and in my
> ROR-Application (so it's not isql) i get:
>
>
> SQL> select * from hds where PlanMA = 'KLINGLKA' and
> CONVERT(DATETIME,[Day],104) = CONVERT(DATETIME,'02.01.2008',104)
> +-------+-------+-------+-------+-------+-------+-------+-----
> ----------
> ---------+------+-----------------+----------------+
> | Mo | Di | Mi | Do | Fr | Sa | So | Day
> | EXPR1| EXPR2 | PlanMA |
> +-------+-------+-------+-------+-------+-------+-------+-----
> ----------
> ---------+------+-----------------+----------------+
> | 0 | 0 | 0 | 162 | 56 | 0 | 0 | 2008-01-02
> 00:00:00.000| HD | Summe IT-Support| KLINGLKA |
> +-------+-------+-------+-------+-------+-------+-------+-----
> ----------
> ---------+------+-----------------+----------------+
>
>
> As you can see the result has an additional 0 which is shown as the
> value for Mo.
>

No, data are shifted, try to count columns. I readed all reply from
server and isql output is correct !!! Are you sure that data was not
changed in the meantime?? I though that problem could be the date order
but I think you don't have data for febrary first so data seems
correct... I really don't understand :(
As you can see query are sent to server and server reply what isql
print.

> The values for Mo .. So are all integers.
> I tried the stable version 0.64 and the snapshot of today
>
> Can anybody help with this problem? - Thanks!
>
> karl
>


freddy77

> TDSDUMP:
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> ++++++++++
> +++++++++++++++++++++++++++
> log.c:190:Starting log file for FreeTDS 0.65.dev.20080106
> on 2008-01-07 13:47:03 with debug flags 0x4fff.
> iconv.c:197:names for ISO-8859-1: ISO-8859-1
> iconv.c:197:names for UTF-8: UTF-8
> iconv.c:197:names for UCS-2LE: UCS-2LE
> iconv.c:197:names for UCS-2BE: UCS-2BE
> iconv.c:363:iconv to convert client-side data to the "ISO-8859-1"
> character set
> iconv.c:516:tds_iconv_info_init: converting "ISO-8859-1"->"UCS-2LE"
> iconv.c:516:tds_iconv_info_init: converting "ISO-8859-1"->"UCS-2LE"
> net.c:192:Connecting to 192.168.170.201 port 1433 (TDS version 8.0)
> net.c:248:tds_open_socket: connect(2) returned "Operation now in
> progress"
> net.c:288:tds_open_socket() succeeded
> util.c:162:Changed query state from DEAD to IDLE
> login.c:735:quietly sending TDS 7+ login packet
> token.c:312:tds_process_login_tokens()
> net.c:547:Received header
> 0000 04 01 01 bd 00 e8 01 00- |........|
>
....
> odbc.c:3878:_SQLGetStmtAttr(0x558540, 10013, 0x7fff83e33db8, 8, (nil))
> odbc.c:4085:SQLPrepare(0x558540, select * from hds where PlanMA =
> 'KLINGLKA' and CONVERT(DATETIME,[Day],104) =
> CONVERT(DATETIME,'02.01.2008',104) , -3)
> odbc.c:4149:Creating prepared statement
> mem.c:563:tds_free_all_results()
> util.c:162:Changed query state from IDLE to QUERYING
> util.c:162:Changed query state from QUERYING to PENDING
> net.c:739:Sending packet
> 0000 03 01 01 1e 00 00 01 00-ff ff 0b 00 00 00 00 01
> |........ ........|
> 0010 26 04 00 00 00 63 00 00-00 00 09 04 d0 00 00 ff
> |&....c.. ........|
> 0020 ff ff ff 00 00 63 e2 00-00 00 09 04 d0 00 00 e2
> |.....c.. ........|
> 0030 00 00 00 73 00 65 00 6c-00 65 00 63 00 74 00 20
> |...s.e.l .e.c.t. |
> 0040 00 2a 00 20 00 66 00 72-00 6f 00 6d 00 20 00 68 |.*.
> .f.r .o.m. .h|
> 0050 00 64 00 73 00 20 00 77-00 68 00 65 00 72 00 65 |.d.s.
> .w .h.e.r.e|
> 0060 00 20 00 50 00 6c 00 61-00 6e 00 4d 00 41 00 20 |.
> .P.l.a .n.M.A. |
> 0070 00 3d 00 20 00 27 00 4b-00 4c 00 49 00 4e 00 47 |.=.
> .'.K .L.I.N.G|
> 0080 00 4c 00 4b 00 41 00 27-00 20 00 61 00 6e 00 64
> |.L.K.A.' . .a.n.d|
> 0090 00 20 00 43 00 4f 00 4e-00 56 00 45 00 52 00 54 |.
> .C.O.N .V.E.R.T|
> 00a0 00 28 00 44 00 41 00 54-00 45 00 54 00 49 00 4d
> |.(.D.A.T .E.T.I.M|
> 00b0 00 45 00 2c 00 5b 00 44-00 61 00 79 00 5d 00 2c
> |.E.,.[.D .a.y.].,|
> 00c0 00 31 00 30 00 34 00 29-00 20 00 3d 00 20 00 43
> |.1.0.4.) . .=. .C|
> 00d0 00 4f 00 4e 00 56 00 45-00 52 00 54 00 28 00 44
> |.O.N.V.E .R.T.(.D|
> 00e0 00 41 00 54 00 45 00 54-00 49 00 4d 00 45 00 2c
> |.A.T.E.T .I.M.E.,|
> 00f0 00 27 00 30 00 32 00 2e-00 30 00 31 00 2e 00 32
> |.'.0.2.. .0.1...2|
> 0100 00 30 00 30 00 38 00 27-00 2c 00 31 00 30 00 34
> |.0.0.8.' .,.1.0.4|
> 0110 00 29 00 20 00 00 00 26-04 04 01 00 00 00 |.). ...& ......|
>
> token.c:495:tds_process_tokens(0x51d390, 0x7fff83e33cac,
> 0x7fff83e33ca8,
> 0x104)
> util.c:162:Changed query state from PENDING to READING
> net.c:547:Received header
> 0000 04 01 00 cb 00 e8 01 00- |........|
>
> net.c:626:Received packet
> 0000 81 0b 00 00 00 08 00 38-02 4d 00 6f 00 00 00 08
> |.......8 .M.o....|
> 0010 00 38 02 44 00 69 00 00-00 08 00 38 02 4d 00 69
> |.8.D.i.. ...8.M.i|
> 0020 00 00 00 08 00 38 02 44-00 6f 00 00 00 08 00 38
> |.....8.D .o.....8|
> 0030 02 46 00 72 00 00 00 08-00 38 02 53 00 61 00 00
> |.F.r.... .8.S.a..|
> 0040 00 08 00 38 02 53 00 6f-00 00 00 08 00 3d 03 44
> |...8.S.o .....=.D|
> 0050 00 61 00 79 00 00 00 00-00 a7 02 00 09 04 d0 00
> |.a.y.... ........|
> 0060 00 05 45 00 58 00 50 00-52 00 31 00 00 00 00 00
> |..E.X.P. R.1.....|
> 0070 a7 10 00 09 04 d0 00 00-05 45 00 58 00 50 00 52
> |........ .E.X.P.R|
> 0080 00 32 00 00 00 08 00 a7-14 00 09 04 00 01 28 06
> |.2...... ......(.|
> 0090 50 00 6c 00 61 00 6e 00-4d 00 41 00 ff 01 00 c1
> |P.l.a.n. M.A.....|
> 00a0 00 00 00 00 00 79 00 00-00 00 ac 0d 00 00 01 38
> |.....y.. .......8|
> 00b0 00 00 00 26 04 04 02 00-00 00 fe 00 00 e0 00 00
> |...&.... ........|
> 00c0 00 00 00 - |...|
>
> token.c:510:processing result tokens. marker is 81(TDS7_RESULT)
> token.c:1520:processing TDS7 result metadata.
> mem.c:563:tds_free_all_results()
> token.c:1544:set current_results (11 columns) to tds->res_info
> token.c:1555:setting up column 0
> token.c:1490:tds7_get_data_info:
> colname = Mo (2 bytes)
> type = 56 (int)
> server's type = 56 (int)
> column_varint_size = 0
> column_size = 4 (4 on server)
> token.c:1555:setting up column 1
> token.c:1490:tds7_get_data_info:
> colname = Di (2 bytes)
> type = 56 (int)
> server's type = 56 (int)
> column_varint_size = 0
> column_size = 4 (4 on server)
> token.c:1555:setting up column 2
> token.c:1490:tds7_get_data_info:
> colname = Mi (2 bytes)
> type = 56 (int)
> server's type = 56 (int)
> column_varint_size = 0
> column_size = 4 (4 on server)
> token.c:1555:setting up column 3
> token.c:1490:tds7_get_data_info:
> colname = Do (2 bytes)
> type = 56 (int)
> server's type = 56 (int)
> column_varint_size = 0
> column_size = 4 (4 on server)
> token.c:1555:setting up column 4
> token.c:1490:tds7_get_data_info:
> colname = Fr (2 bytes)
> type = 56 (int)
> server's type = 56 (int)
> column_varint_size = 0
> column_size = 4 (4 on server)
> token.c:1555:setting up column 5
> token.c:1490:tds7_get_data_info:
> colname = Sa (2 bytes)
> type = 56 (int)
> server's type = 56 (int)
> column_varint_size = 0
> column_size = 4 (4 on server)
> token.c:1555:setting up column 6
> token.c:1490:tds7_get_data_info:
> colname = So (2 bytes)
> type = 56 (int)
> server's type = 56 (int)
> column_varint_size = 0
> column_size = 4 (4 on server)
> token.c:1555:setting up column 7
> token.c:1490:tds7_get_data_info:
> colname = Day (3 bytes)
> type = 61 (datetime)
> server's type = 61 (datetime)
> column_varint_size = 0
> column_size = 8 (8 on server)
> token.c:1555:setting up column 8
> token.c:3294:adjust_character_column_size:
> Server charset: CP1252
> Server column_size: 2
> Client charset: ISO-8859-1
> Client column_size: 2
> token.c:1490:tds7_get_data_info:
> colname = EXPR1 (5 bytes)
> type = 39 (varchar)
> server's type = 167 (xvarchar)
> column_varint_size = 2
> column_size = 2 (2 on server)
> token.c:1555:setting up column 9
> token.c:3294:adjust_character_column_size:
> Server charset: CP1252
> Server column_size: 16
> Client charset: ISO-8859-1
> Client column_size: 16
> token.c:1490:tds7_get_data_info:
> colname = EXPR2 (5 bytes)
> type = 39 (varchar)
> server's type = 167 (xvarchar)
> column_varint_size = 2
> column_size = 16 (16 on server)
> token.c:1555:setting up column 10
> iconv.c:516:tds_iconv_info_init: converting "ISO-8859-1"->"CP850"
> token.c:3294:adjust_character_column_size:
> Server charset: CP850
> Server column_size: 20
> Client charset: ISO-8859-1
> Client column_size: 20
> token.c:1490:tds7_get_data_info:
> colname = PlanMA (6 bytes)
> type = 39 (varchar)
> server's type = 167 (xvarchar)
> column_varint_size = 2
> column_size = 20 (20 on server)
> util.c:162:Changed query state from READING to PENDING
> token.c:495:tds_process_tokens(0x51d390, 0x7fff83e33cac,
> 0x7fff83e33ca8,
> 0x104)
> util.c:162:Changed query state from PENDING to READING
> token.c:510:processing result tokens. marker is ff(DONEINPROC)
> token.c:2201:tds_process_end: more_results = 1
> was_cancelled = 0
> error = 0
> done_count_valid = 0
> token.c:2232: rows_affected = 0
> token.c:510:processing result tokens. marker is 79(RETURNSTATUS)
> token.c:510:processing result tokens. marker is ac(PARAM)
> token.c:567:processing parameters for sp 11
> token.c:569:calling tds_process_param_result
> token.c:1198:tds_process_param_result(0x51d390, 0x7fff83e33c58)
> token.c:1596:processing result. type = 38(integer-null), varint_size 1
> token.c:1617:processing result. column_size 4
> token.c:1915:tds_get_data: type 38, varint size 1
> token.c:1975:tds_get_data(): wire column size is 4
> token.c:573:1 hidden return parameters
> token.c:510:processing result tokens. marker is fe(DONEPROC)
> token.c:2201:tds_process_end: more_results = 0
> was_cancelled = 0
> error = 0
> done_count_valid = 0
> token.c:2217:tds_process_end() state set to TDS_IDLE
> util.c:162:Changed query state from READING to IDLE
> token.c:2232: rows_affected = 0
> util.c:110:logic error: cannot change query state from IDLE to PENDING
> token.c:495:tds_process_tokens(0x51d390, 0x7fff83e33cac,
> 0x7fff83e33ca8,
> 0x104)
> token.c:498:tds_process_tokens() state is COMPLETED
> odbc.c:3222:SQLExecute(0x558540)
> odbc.c:2947:_SQLExecute(0x558540)
> odbc.c:2952:_SQLExecute() starting with state 0
> odbc.c:3060:End prepare, execute
> query.c:1631:tds_submit_execute()
> mem.c:563:tds_free_all_results()
> util.c:162:Changed query state from IDLE to QUERYING
> util.c:162:Changed query state from QUERYING to PENDING
> net.c:739:Sending packet
> 0000 03 01 00 29 00 00 01 00-0a 00 73 00 70 00 5f 00
> |...).... ..s.p._.|
> 0010 65 00 78 00 65 00 63 00-75 00 74 00 65 00 00 00
> |e.x.e.c. u.t.e...|
> 0020 00 00 26 04 04 02 00 00-00 |..&..... .|
>
> token.c:495:tds_process_tokens(0x51d390, 0x7fff83e33c54,
> 0x7fff83e33c50,
> 0x6914)
> util.c:162:Changed query state from PENDING to READING
> net.c:547:Received header
> 0000 04 01 01 00 00 e8 01 00- |........|
>
> net.c:626:Received packet
> 0000 81 0b 00 00 00 08 00 38-02 4d 00 6f 00 00 00 08
> |.......8 .M.o....|
> 0010 00 38 02 44 00 69 00 00-00 08 00 38 02 4d 00 69
> |.8.D.i.. ...8.M.i|
> 0020 00 00 00 08 00 38 02 44-00 6f 00 00 00 08 00 38
> |.....8.D .o.....8|
> 0030 02 46 00 72 00 00 00 08-00 38 02 53 00 61 00 00
> |.F.r.... .8.S.a..|
> 0040 00 08 00 38 02 53 00 6f-00 00 00 08 00 3d 03 44
> |...8.S.o .....=.D|
> 0050 00 61 00 79 00 00 00 00-00 a7 02 00 09 04 d0 00
> |.a.y.... ........|
> 0060 00 05 45 00 58 00 50 00-52 00 31 00 00 00 00 00
> |..E.X.P. R.1.....|
> 0070 a7 10 00 09 04 d0 00 00-05 45 00 58 00 50 00 52
> |........ .E.X.P.R|
> 0080 00 32 00 00 00 08 00 a7-14 00 09 04 00 01 28 06
> |.2...... ......(.|
> 0090 50 00 6c 00 61 00 6e 00-4d 00 41 00 d1 00 00 00
> |P.l.a.n. M.A.....|
> 00a0 00 00 00 00 00 00 00 00-00 a2 00 00 00 38 00 00
> |........ .....8..|
> 00b0 00 00 00 00 00 00 00 00-00 17 9a 00 00 00 00 00
> |........ ........|
> 00c0 00 02 00 48 44 10 00 53-75 6d 6d 65 20 49 54 2d
> |...HD..S umme IT-|
> 00d0 53 75 70 70 6f 72 74 08-00 4b 4c 49 4e 47 4c 4b
> |Support. .KLINGLK|
> 00e0 41 ff 11 00 c1 00 01 00-00 00 79 00 00 00 00 fe
> |A....... ..y.....|
> 00f0 00 00 e0 00 01 00 00 00- |........|
>
...



  • Re: [freetds] SuSE Linux: columns of resultset don't match for MSSQL Server 2000, ZIGLIO, Frediano, VF-IT, 01/07/2008

Archive powered by MHonArc 2.6.24.

Top of Page