[freetds] empty results on multiple step query

Adrian Lattke adrian at techonline.com
Sat Jul 3 13:47:16 EDT 2004


Hello Frediano:
 
Thank you for this patch. If someone can let me know when this makes it into the build, I will download and test, and let you guys know if it worked?
 
-Adrian Lattke

________________________________

From: freetds-bounces at lists.ibiblio.org on behalf of Frediano Ziglio
Sent: Sat 7/3/2004 1:37 PM
To: FreeTDS Development Group
Subject: Re: [freetds] empty results on multiple step query



Il sab, 2004-07-03 alle 16:19, James K. Lowden ha scritto:
> On Sat, 3 Jul 2004 <adrian at techonline.com> wrote:
> > Hi Guys,
> > 
> > David Barnwell posted a very similar problem to the one I am
> > experiencing on Fri Jun 25 15:07:35 EDT 2004:
> > 
> > http://lists.ibiblio.org/pipermail/freetds/2004q2/016465.html <http://lists.ibiblio.org/pipermail/freetds/2004q2/016465.html> 
> ...
> > ----------------------------------------------
> > set rowcount 0
> > select distinct
> >  c.content_id as content_id,
> >  c.content_title as content_title
> > into  #temptable
> > from  MYDB.dbo.content c
> > where  c.content_type_name = 'mycontenttype'
> > select  t.content_id,
> >  t.content_title,
> >  c.content_summary,
> >  c.broadcast_date,
> >  c.broadcast_length,
> >  c.content_author_company
> > from  TOL.dbo.content c, #temptable
> > where  c.content_id = t.content_id
> > order by c.broadcast_date desc
> > drop table #temptable
> > set rowcount 0
> > ----------------------------------------------
> > 
> > If I run this query against my SQL Server 7.0 server, using the new
> > configuration (i.e. freetds-0.62.4), it works fine. In fact, if I run
> > the query by tsql and run it against my SQL Server 2000 server, it runs
> > fine. For some reason, it returns "0" when I try fetching the results in
> > PERL against SQL Server 2000.
>
> The answer may be that you need to check $sth->{syb_result_type}, because
> the new server is returning spurious result status codes.  Cf. perldoc
> DBD::Sybase. 
>
> Your logs do have different counts for these things, see below.  The 4043
> is a (perhaps unexpected) CS_STATUS_RESULT. 
>
> HTH.

MMMmmm... this remember me the issue with output parameters... Before
SQL7 SP3 output parameters got returned to client for compatibility.
Perhaps even procedure results is always returned. However the question
is, does it have any sense?? It seems that mssql returns a lot of
spurious procedure results (for triggers, internal store procedure and
whatever...). Anyone care about these results?? What's proprietary dblib
and ctlib behavior?? ODBC care only about results returned from explicit
store procedure call (that is results before 0xac = param output or 0xfe
= store procedure end) tokens. It's quite easy to change code in tds to
skip spurious store procedure results...

Well a simple test with tsql

Msg 2411, Level 10, State 1, Server , Line 1
No conversions will be done.

1> use tempdb
2> go
1> create proc test (@i integer output) as select @i = 5 return 6
2> go
1> declare @i int
2> exec test @i output
3> select @i
4> go
(return status = 6)

5
1> bye

Sybase 11.9.2
0000 0f 01 00 3b 00 00 00 00-21 2e 00 00 00 00 64 65 |...;.... !.....de|
0010 63 6c 61 72 65 20 40 69-20 69 6e 74 0a 65 78 65 |clare @i  int.exe|
0020 63 20 74 65 73 74 20 40-69 20 6f 75 74 70 75 74 |c test @ i output|
0030 0a 73 65 6c 65 63 74 20-40 69 0a                |.select  @i.|
                                                                                                                               
                                                                                                                               
pid: 10784:Received header @ 19:04:59.454484
0000 04 01 00 57 00 00 00 00-                        |...W....|
                                                                                                                               
                                                                                                                               
pid: 10784:Received packet @ 19:04:59.454568
0000 ff 51 00 02 00 01 00 00-00 ff 51 00 02 00 01 00 |.Q...... ..Q.....|
0010 00 00 79 06 00 00 00 ac-0b 00 00 01 00 00 00 00 |..y..... ........|
0020 38 05 00 00 00 fe 09 00-02 00 01 00 00 00 ee 0b |8....... ........|
0030 00 01 00 00 30 07 00 00-00 26 04 00 ae 01 00 00 |....0... .&......|
0040 d1 04 05 00 00 00 fd 10-00 02 00 01 00 00 00    |........ .......|

For who don't understand raw TDS data it's
DONE_INPROC
DONE_INPROC
RETURNSTATUS
PARAM
DONE_PROC
RESULT
CONTROL
ROW
DONE

mssql2k
0000 01 01 00 62 00 00 01 00-64 00 65 00 63 00 6c 00 |...b.... d.e.c.l.|
0010 61 00 72 00 65 00 20 00-40 00 69 00 20 00 69 00 |a.r.e. . @.i. .i.|
0020 6e 00 74 00 0a 00 65 00-78 00 65 00 63 00 20 00 |n.t...e. x.e.c. .|
0030 74 00 65 00 73 00 74 00-20 00 40 00 69 00 20 00 |t.e.s.t.  . at .i. .|
0040 6f 00 75 00 74 00 70 00-75 00 74 00 0a 00 73 00 |o.u.t.p. u.t...s.|
0050 65 00 6c 00 65 00 63 00-74 00 20 00 40 00 69 00 |e.l.e.c. t. . at .i.|
0060 0a 00                  -                        |..|
                                                                                                                               
                                                                                                                               
pid: 10847:Received header @ 19:10:23.432535
0000 04 01 00 41 00 33 01 00-                        |...A.3..|
                                                                                                                               
                                                                                                                               
pid: 10847:Received packet @ 19:10:23.432618
0000 ff 11 00 c1 00 01 00 00-00 ff 11 00 c1 00 01 00 |........ ........|
0010 00 00 79 06 00 00 00 fe-01 00 e0 00 01 00 00 00 |..y..... ........|
0020 81 01 00 00 00 01 00 26-04 00 d1 04 05 00 00 00 |.......& ........|
0030 fd 10 00 c1 00 01 00 00-00                      |........ .|

As you can see output it's very similar...


>From Adrian's log

mssql7
0340 5f 00 64 00 61 00 74 00-65 00 20 00 64 00 65 00 |_.d.a.t. e. .d.e.|
0350 73 00 63 00 0a 00      -                        |s.c...|
                                                                                                                                          
                                                                                                                                          
21:37:41.349922 ct_send() succeeded
21:37:41.349956 ct_results()
Received header @ 21:37:41.513146
0000 04 00 10 00 00 00 00 00-                        |........|
                                                                                                                                          
                                                                                                                                          
Received packet @ 21:37:41.545587
0000 fd 01 00 bd 00 00 00 00-00 fd 01 00 be 00 00 00 |ý..½.... .ý..¾...|
0010 00 00 fd 01 00 bd 00 00-00 00 00 fd 11 00 c2 00 |..ý..½.. ...ý..Â.|
0020 54 01 00 00 81 06 00 00-00 10 00 38 0a 63 00 6f |T....... ...8.c.o|

mssql2k
0340 5f 00 64 00 61 00 74 00-65 00 20 00 64 00 65 00 |_.d.a.t. e. .d.e.|
0350 73 00 63 00 0a 00      -                        |s.c...|
                                                                                                                                          
                                                                                                                                          
21:38:07.454454 ct_send() succeeded
21:38:07.454489 ct_results()
Received header @ 21:38:07.509835
0000 04 00 10 00 00 3d 01 00-                        |.....=..|
                                                                                                                                          
                                                                                                                                          
Received packet @ 21:38:07.514156
0000 fd 01 00 bd 00 00 00 00-00 fd 01 00 be 00 00 00 |ý..½.... .ý..¾...|
0010 00 00 fd 01 00 bd 00 00-00 00 00 fd 11 00 c2 00 |..ý..½.. ...ý..Â.|
0020 54 01 00 00 79 00 00 00-00 81 06 00 00 00 10 00 |T...y... ........|

As you can see 0x79 token is added (I don't understand why... perhaps a
new strange function in ADO ?). As you can see this is just a spurious
return (it's followed by a 0x81 token, not 0xac nor 0xfe).

Attached a patch for this issue. It pass all FreeTDS regression tests
(Sybase 11.9.2, mssql2k).

freddy77





More information about the FreeTDS mailing list