FreeTDS Development Group

[Ryan Barber <rbarber AT dargal.com>]

James K. Lowden wrote:

> On Mon, 16 Feb 2004, Ryan Barber <rbarber AT dargal.com> wrote:
>  
>
> > Oddly enuf, the only situation where this happens is when selecting from
> > a temporary table, and only when a where clause is used. Not a complex
> > where query either, eg:
> >
> > SELECT * FROM #TEMPTABLE WHERE ID > 32;
> >   vs.
> > SELECT * FROM #TEMPTABLE;
> >
> > Granted, this is probably only a quirk with our version of MSSQL, but we
> > still need a way to suppress TDS_RETURNSTATUS_TOKEN being returned as a
> > result set to DBD::Sybase.
> >    
>
> Good Morning Ryan, 
>
> I doubt it's a "quirk"; what server are you using?  
>
>  
SQL Server Enterprise Edition
8.00.760 (SP3)
Microsoft Windows NT - 5.0 (2195)

> I can't think of any reason you'd see a TDS_RETURNSTATUS_TOKEN arrive from
> the server when selecting from a temporary table, WHERE clause or no. 
> Could you send a TDSDUMP log of the session?  
>
>  
>
> > Am I barking up the wrong tree, should I be trying to bring DBD::Sybase
> > up to date with the freetds?
> >    
>
> I'm not sure.  I know for a fact that the ct-lib in 0.62 is much more
> correct than 0.53 was, and I know we *fixed* some return status handling
> last year, both at the packet-handling level in libtds, and in the client
> libraries.  If you're using DBD::Sybase 1.00 or better, I'd expect it to
> work correctly out of the box.  
>
> In any case, if we read a return status token, we can't suppress it. 
> We're supposed to pass it on to the calling layer.  ISTR we used to
> swallow it in libtds back in 0.53.  Is there any chance you're ignoring
> how result_type is set by ct_results()?
>
>  
Reviewing tdsdump logs from .53, when passed a RETURNSTATUS token, 
freetds would just step over it.

I'll look in DBD::Sybase, see if I can find where this result_type is 
being read.

> --jkl
>
>  

I've attached a trimmed version of the tdsdump logs, running with and 
without the where clause. These were generated this morning using the 
cvs version of freetds and DBD::Sybase 1.02. If you need more detail I 
can email you the full logs.

Also, I never noticed the 'Troublesome bytes:' before, but I don't 
belive it relates to this problem.

Thanks!

-Ryan

Failed Request, where clause.

08:52:54.089222 tds_put_string converting 177 bytes of "SET TEXTSIZE 40000; 
SELECT TOP 10 STARTDATE, ENDDATE, SUPPLIERPROPERTY, NIGHTS, ITINERARY, 
PRODUCTID, ROOMS, TAXES, LOCATION, CEFLAG FROM #TEMPHOTDEALS WHERE ID > '' 
ORDER BY ID"
08:52:54.089281 Error: tds_put_string: Gave up converting 49 bytes due to 
error 7.
        Troublesome bytes:
        0000 46 4c 41 47 20 46 52 4f-4d 20 23 54 45 4d 50 48 |FLAG FRO M 
#TEMPH|
0010 4f 54 44 45 41 4c 53 20-57 48 45 52 45 20 49 44 |OTDEALS  WHERE ID|
0020 20 3e 20 27 27 20 4f 52-44 45 52 20 42 59 20 49 | > '' OR DER BY I|
0030 44                     -                        |D|


08:52:54.089411 tds_put_string converting 49 bytes of "FLAG FROM 
#TEMPHOTDEALS WHERE ID > '' ORDER BY ID"
08:52:54.089452 tds_put_string wrote 354 bytes
Sending packet @ 08:52:54.089489
0000 01 01 01 6a 00 00 01 00-53 00 45 00 54 00 20 00 |...j.... S.E.T. .|
0010 54 00 45 00 58 00 54 00-53 00 49 00 5a 00 45 00 |T.E.X.T. S.I.Z.E.|
0020 20 00 34 00 30 00 30 00-30 00 30 00 3b 00 20 00 | .4.0.0. 0.0.;. .|
0030 53 00 45 00 4c 00 45 00-43 00 54 00 20 00 54 00 |S.E.L.E. C.T. .T.|
0040 4f 00 50 00 20 00 31 00-30 00 20 00 53 00 54 00 |O.P. .1. 0. .S.T.|
0050 41 00 52 00 54 00 44 00-41 00 54 00 45 00 2c 00 |A.R.T.D. A.T.E.,.|
0060 20 00 45 00 4e 00 44 00-44 00 41 00 54 00 45 00 | .E.N.D. D.A.T.E.|
0070 2c 00 20 00 53 00 55 00-50 00 50 00 4c 00 49 00 |,. .S.U. P.P.L.I.|
0080 45 00 52 00 50 00 52 00-4f 00 50 00 45 00 52 00 |E.R.P.R. O.P.E.R.|
0090 54 00 59 00 2c 00 20 00-4e 00 49 00 47 00 48 00 |T.Y.,. . N.I.G.H.|
00a0 54 00 53 00 2c 00 20 00-49 00 54 00 49 00 4e 00 |T.S.,. . I.T.I.N.|
00b0 45 00 52 00 41 00 52 00-59 00 2c 00 20 00 50 00 |E.R.A.R. Y.,. .P.|
00c0 52 00 4f 00 44 00 55 00-43 00 54 00 49 00 44 00 |R.O.D.U. C.T.I.D.|
00d0 2c 00 20 00 52 00 4f 00-4f 00 4d 00 53 00 2c 00 |,. .R.O. O.M.S.,.|
00e0 20 00 54 00 41 00 58 00-45 00 53 00 2c 00 20 00 | .T.A.X. E.S.,. .|
00f0 4c 00 4f 00 43 00 41 00-54 00 49 00 4f 00 4e 00 |L.O.C.A. T.I.O.N.|
0100 2c 00 20 00 43 00 45 00-46 00 4c 00 41 00 47 00 |,. .C.E. F.L.A.G.|
0110 20 00 46 00 52 00 4f 00-4d 00 20 00 23 00 54 00 | .F.R.O. M. .#.T.|
0120 45 00 4d 00 50 00 48 00-4f 00 54 00 44 00 45 00 |E.M.P.H. O.T.D.E.|
0130 41 00 4c 00 53 00 20 00-57 00 48 00 45 00 52 00 |A.L.S. . W.H.E.R.|
0140 45 00 20 00 49 00 44 00-20 00 3e 00 20 00 27 00 |E. .I.D.  .>. .'.|
0150 27 00 20 00 4f 00 52 00-44 00 45 00 52 00 20 00 |'. .O.R. D.E.R. .|
0160 42 00 59 00 20 00 49 00-44 00                   |B.Y. .I. D.|


08:52:54.090149 ct_send() succeeded
08:52:54.090196 ct_results()
Received header @ 08:52:54.101634
0000 04 00 10 00 00 ff 01 00-                        |........|


Received packet @ 08:52:54.101939
0000 79 00 00 00 00 fd 01 00-be 00 00 00 00 00 81 0a |y....... ........|
0010 00 00 00 09 00 a7 bc 02-09 04 d0 00 34 09 53 00 |........ ....4.S.|
0020 54 00 41 00 52 00 54 00-44 00 41 00 54 00 45 00 |T.A.R.T. D.A.T.E.|
0030 00 00 09 00 a7 bc 02 09-04 d0 00 34 07 45 00 4e |........ ...4.E.N|
0040 00 44 00 44 00 41 00 54-00 45 00 00 00 09 00 a7 |.D.D.A.T .E......|
0050 bc 02 09 04 d0 00 34 10-53 00 55 00 50 00 50 00 |......4. S.U.P.P.|
0060 4c 00 49 00 45 00 52 00-50 00 52 00 4f 00 50 00 |L.I.E.R. P.R.O.P.|
0070 45 00 52 00 54 00 59 00-00 00 09 00 26 04 06 4e |E.R.T.Y. ....&..N|
0080 00 49 00 47 00 48 00 54-00 53 00 00 00 09 00 a7 |.I.G.H.T .S......|
0090 d0 07 09 04 d0 00 34 09-49 00 54 00 49 00 4e 00 |......4. I.T.I.N.|
00a0 45 00 52 00 41 00 52 00-59 00 00 00 09 00 a7 16 |E.R.A.R. Y.......|
00b0 00 09 04 d0 00 34 09 50-00 52 00 4f 00 44 00 55 |.....4.P .R.O.D.U|
00c0 00 43 00 54 00 49 00 44-00 00 00 09 00 a7 a0 0f |.C.T.I.D ........|
00d0 09 04 d0 00 34 05 52 00-4f 00 4f 00 4d 00 53 00 |....4.R. O.O.M.S.|
00e0 00 00 09 00 a7 32 00 09-04 d0 00 34 05 54 00 41 |.....2.. ...4.T.A|
00f0 00 58 00 45 00 53 00 00-00 09 00 a7 78 00 09 04 |.X.E.S.. ....x...|
0100 d0 00 34 08 4c 00 4f 00-43 00 41 00 54 00 49 00 |..4.L.O. C.A.T.I.|
0110 4f 00 4e 00 00 00 09 00-26 04 06 43 00 45 00 46 |O.N..... &..C.E.F|
0120 00 4c 00 41 00 47 00 a9-02 00 00 00 d1 13 00 46 |.L.A.G.. .......F|
0130 65 62 20 31 36 20 32 30-30 34 20 31 32 3a 30 30 |eb 16 20 04 12:00|
0140 41 4d 13 00 46 65 62 20-32 30 20 32 30 30 34 20 |AM..Feb  20 2004 |
0150 31 32 3a 30 30 41 4d 13-00 43 61 72 6e 69 76 61 |12:00AM. .Carniva|
0160 6c 3c 42 52 3e 45 63 73-74 61 73 79 04 04 00 00 |l<BR>Ecs tasy....|
0170 00 73 00 4c 6f 73 20 41-6e 67 65 6c 65 73 20 28 |.s.Los A ngeles (|
0180 4c 6f 6e 67 20 42 65 61-63 68 29 2c 20 43 41 3b |Long Bea ch), CA;|

  --- trimmed results from the select --
 
0fa0 6f 73 20 41 6e 67 65 6c-65 73 20 28 4c 6f 6e 67 |os Angel es (Long|
0fb0 20 42 65 61 63 68 29 2c-20 43 41 04 01 00 00 00 | Beach),  CA.....|
0fc0 d1 13 00 46 65 62 20 32-31 20 32 30 30 34 20 31 |...Feb 2 1 2004 1|
0fd0 32 3a 30 30 41 4d 13 00-46 65 62 20 32 38 20 32 |2:00AM.. Feb 28 2|
0fe0 30 30 34 20 31 32 3a 30-30 41 4d 1c 00 43 61 72 |004 12:0 0AM..Car|
0ff0 6e 69 76 61 6c 3c 42 52-                        |nival<BR|


08:52:54.107250 processing result tokens.  marker is  79(RETURNSTATUS)
08:52:54.107304 ct_results() process_result_tokens returned 1 (type 4043) 
08:52:54.107393 generating return status row. type = 56(int), varint_size 0
08:52:54.107467 ct_res_info()
08:52:54.107501 ct_res_info(): Number of columns is 1
08:52:54.107547 ct_describe()
08:52:54.107583 _ct_get_client_type(type 56, user 0, size 4)
08:52:54.107620 ct_describe() datafmt->datatype = 2 server type 56
08:52:54.107667 ct_bind() datafmt count = 1 column_number = 1
08:52:54.109202 ct_close()
08:52:54.109631 ct_con_drop()
08:52:54.110004 ct_close()
08:52:54.110150 ct_con_drop()






Working request, No where clause


08:59:41.778700 tds_put_string converting 163 bytes of "SET TEXTSIZE 40000; 
SELECT TOP 10 STARTDATE, ENDDATE, SUPPLIERPROPERTY, NIGHTS, ITINERARY, 
PRODUCTID, ROOMS, TAXES, LOCATION, CEFLAG FROM #TEMPHOTDEALS ORDER BY ID"
08:59:41.778757 Error: tds_put_string: Gave up converting 35 bytes due to 
error 7.
        Troublesome bytes:
        0000 46 4c 41 47 20 46 52 4f-4d 20 23 54 45 4d 50 48 |FLAG FRO M 
#TEMPH|
0010 4f 54 44 45 41 4c 53 20-4f 52 44 45 52 20 42 59 |OTDEALS  ORDER BY|
0020 20 49 44               -                        | ID|


08:59:41.778870 tds_put_string converting 35 bytes of "FLAG FROM 
#TEMPHOTDEALS ORDER BY ID"
08:59:41.778910 tds_put_string wrote 326 bytes
Sending packet @ 08:59:41.778947
0000 01 01 01 4e 00 00 01 00-53 00 45 00 54 00 20 00 |...N.... S.E.T. .|
0010 54 00 45 00 58 00 54 00-53 00 49 00 5a 00 45 00 |T.E.X.T. S.I.Z.E.|
0020 20 00 34 00 30 00 30 00-30 00 30 00 3b 00 20 00 | .4.0.0. 0.0.;. .|
0030 53 00 45 00 4c 00 45 00-43 00 54 00 20 00 54 00 |S.E.L.E. C.T. .T.|
0040 4f 00 50 00 20 00 31 00-30 00 20 00 53 00 54 00 |O.P. .1. 0. .S.T.|
0050 41 00 52 00 54 00 44 00-41 00 54 00 45 00 2c 00 |A.R.T.D. A.T.E.,.|
0060 20 00 45 00 4e 00 44 00-44 00 41 00 54 00 45 00 | .E.N.D. D.A.T.E.|
0070 2c 00 20 00 53 00 55 00-50 00 50 00 4c 00 49 00 |,. .S.U. P.P.L.I.|
0080 45 00 52 00 50 00 52 00-4f 00 50 00 45 00 52 00 |E.R.P.R. O.P.E.R.|
0090 54 00 59 00 2c 00 20 00-4e 00 49 00 47 00 48 00 |T.Y.,. . N.I.G.H.|
00a0 54 00 53 00 2c 00 20 00-49 00 54 00 49 00 4e 00 |T.S.,. . I.T.I.N.|
00b0 45 00 52 00 41 00 52 00-59 00 2c 00 20 00 50 00 |E.R.A.R. Y.,. .P.|
00c0 52 00 4f 00 44 00 55 00-43 00 54 00 49 00 44 00 |R.O.D.U. C.T.I.D.|
00d0 2c 00 20 00 52 00 4f 00-4f 00 4d 00 53 00 2c 00 |,. .R.O. O.M.S.,.|
00e0 20 00 54 00 41 00 58 00-45 00 53 00 2c 00 20 00 | .T.A.X. E.S.,. .|
00f0 4c 00 4f 00 43 00 41 00-54 00 49 00 4f 00 4e 00 |L.O.C.A. T.I.O.N.|
0100 2c 00 20 00 43 00 45 00-46 00 4c 00 41 00 47 00 |,. .C.E. F.L.A.G.|
0110 20 00 46 00 52 00 4f 00-4d 00 20 00 23 00 54 00 | .F.R.O. M. .#.T.|
0120 45 00 4d 00 50 00 48 00-4f 00 54 00 44 00 45 00 |E.M.P.H. O.T.D.E.|
0130 41 00 4c 00 53 00 20 00-4f 00 52 00 44 00 45 00 |A.L.S. . O.R.D.E.|
0140 52 00 20 00 42 00 59 00-20 00 49 00 44 00       |R. .B.Y.  .I.D.|


08:59:41.779549 ct_send() succeeded
08:59:41.779614 ct_results()
Received header @ 08:59:41.784363
0000 04 00 10 00 00 f6 01 00-                        |........|


Received packet @ 08:59:41.784675
0000 fd 01 00 be 00 00 00 00-00 81 0a 00 00 00 09 00 |........ ........|
0010 a7 bc 02 09 04 d0 00 34-09 53 00 54 00 41 00 52 |.......4 .S.T.A.R|
0020 00 54 00 44 00 41 00 54-00 45 00 00 00 09 00 a7 |.T.D.A.T .E......|
0030 bc 02 09 04 d0 00 34 07-45 00 4e 00 44 00 44 00 |......4. E.N.D.D.|
0040 41 00 54 00 45 00 00 00-09 00 a7 bc 02 09 04 d0 |A.T.E... ........|
0050 00 34 10 53 00 55 00 50-00 50 00 4c 00 49 00 45 |.4.S.U.P .P.L.I.E|
0060 00 52 00 50 00 52 00 4f-00 50 00 45 00 52 00 54 |.R.P.R.O .P.E.R.T|
0070 00 59 00 00 00 09 00 26-04 06 4e 00 49 00 47 00 |.Y.....& ..N.I.G.|
0080 48 00 54 00 53 00 00 00-09 00 a7 d0 07 09 04 d0 |H.T.S... ........|
0090 00 34 09 49 00 54 00 49-00 4e 00 45 00 52 00 41 |.4.I.T.I .N.E.R.A|
00a0 00 52 00 59 00 00 00 09-00 a7 16 00 09 04 d0 00 |.R.Y.... ........|
00b0 34 09 50 00 52 00 4f 00-44 00 55 00 43 00 54 00 |4.P.R.O. D.U.C.T.|
00c0 49 00 44 00 00 00 09 00-a7 a0 0f 09 04 d0 00 34 |I.D..... .......4|
00d0 05 52 00 4f 00 4f 00 4d-00 53 00 00 00 09 00 a7 |.R.O.O.M .S......|
00e0 32 00 09 04 d0 00 34 05-54 00 41 00 58 00 45 00 |2.....4. T.A.X.E.|
00f0 53 00 00 00 09 00 a7 78-00 09 04 d0 00 34 08 4c |S......x .....4.L|
0100 00 4f 00 43 00 41 00 54-00 49 00 4f 00 4e 00 00 |.O.C.A.T .I.O.N..|
0110 00 09 00 26 04 06 43 00-45 00 46 00 4c 00 41 00 |...&..C. E.F.L.A.|
0120 47 00 a9 02 00 00 00 d1-13 00 46 65 62 20 31 36 |G....... ..Feb 16|
0130 20 32 30 30 34 20 31 32-3a 30 30 41 4d 13 00 46 | 2004 12 :00AM..F|
0140 65 62 20 32 30 20 32 30-30 34 20 31 32 3a 30 30 |eb 20 20 04 12:00|
0150 41 4d 13 00 43 61 72 6e-69 76 61 6c 3c 42 52 3e |AM..Carn ival<BR>|
0160 45 63 73 74 61 73 79 04-04 00 00 00 73 00 4c 6f |Ecstasy. ....s.Lo|
0170 73 20 41 6e 67 65 6c 65-73 20 28 4c 6f 6e 67 20 |s Angele s (Long |
0180 42 65 61 63 68 29 2c 20-43 41 3b 20 43 61 74 61 |Beach),  CA; Cata|

  --- trimmed results from the select --

0f80 20 41 6e 67 65 6c 65 73-20 28 4c 6f 6e 67 20 42 | Angeles  (Long B|
0f90 65 61 63 68 29 2c 20 43-41 2f 4c 6f 73 20 41 6e |each), C A/Los An|
0fa0 67 65 6c 65 73 20 28 4c-6f 6e 67 20 42 65 61 63 |geles (L ong Beac|
0fb0 68 29 2c 20 43 41 04 01-00 00 00 d1 13 00 46 65 |h), CA.. ......Fe|
0fc0 62 20 32 31 20 32 30 30-34 20 31 32 3a 30 30 41 |b 21 200 4 12:00A|
0fd0 4d 13 00 46 65 62 20 32-38 20 32 30 30 34 20 31 |M..Feb 2 8 2004 1|
0fe0 32 3a 30 30 41 4d 1c 00-43 61 72 6e 69 76 61 6c |2:00AM.. Carnival|
0ff0 3c 42 52 3e 43 61 72 6e-                        |<BR>Carn|


08:59:41.789925 processing result tokens.  marker is  fd(DONE)
08:59:41.789971 tds_process_end: more_results = 1
                was_cancelled = 0
                error = 0
                done_count_valid = 0
08:59:41.790016 ct_results() process_result_tokens returned 1 (type 4052) 
08:59:41.790055 ct_results() results state = 0
08:59:41.790119 ct_res_info()
08:59:41.790151 ct_res_info(): Number of rows is -1
08:59:41.790186 ct_results()
08:59:41.790220 ct_res_info()
08:59:41.790252 ct_res_info(): Number of rows is -1
08:59:41.790286 ct_results()
08:59:41.790317 processing result tokens.  marker is  81(TDS7_RESULT)
08:59:41.790413 adjust_character_column_size:
        Server charset: CP1252
        Server column_size: 700
        Client charset: ISO-8859-1
        Client column_size: 700
tds_get_string: reading 18 from wire to give 9 to client.
08:59:41.790511 tds7_get_data_info:1451: 
        colname = STARTDATE (9 bytes)
        type = 39 (varchar)
        server's type = 167 (xvarchar)
        column_varint_size = 2
        column_size = 700 (700 on server)
08:59:41.790566 adjust_character_column_size:
        Server charset: CP1252
        Server column_size: 700
        Client charset: ISO-8859-1
        Client column_size: 700
tds_get_string: reading 14 from wire to give 7 to client.

...