FreeTDS Development Group

["Klingler, Karl" <Karl.Klingler AT vsa.de>]

Hello freddy77,

thank you for your speedy reply!

On the first view i was thinking of a simple shift also, but why should
then only the integer values be shifted, 
and the string values stay as they should?   
The data does not change, if i execute the queries now the results are
the same...

i'm going to have a look at unixodbc, as that layer is involved too -
perhaps the fault lies there...

regards
karl

-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of ZIGLIO,
Frediano, VF-IT
Sent: Monday, January 07, 2008 5:31 PM
To: FreeTDS Development Group
Subject: Re: [freetds] SuSE Linux: columns of resultset don't match
forMSSQL Server 2000

> 
> 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-                        |........|
> 
...
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds