FreeTDS Development Group

[Frediano Ziglio <freddyz77 AT tin.it>]

Il mar, 2003-07-22 alle 05:41, lists-tds AT mymailfilter.net ha scritto:
> Thanks, that certainly explains the codes with which I was struggling.
> 
> > It's an RPC with parameters: -1 (output), NULL ntext, sql text, 1...
> > It seem an sp_prepare call... I don't understand where this code came
> > cause is not very good. A better way is to use RPC, withot sp_prepare...
> 
> The information I sent was from a packet taken from the wire going from an 
> ADO-based client toward an MSSQL 2000 server.  The underlying ADO library 
> or db-lib would've generated the TDS packet.
> 
> I'm trying to interpret the packet more than I'm trying to construct it 
> right now, and based on the current information it doesn't seem to be very 
> straight forward -- without knowing how many parameters, I'm not sure how 
> to tell where the parameters end and the SQL text begins.  
> 

There isn't a field that tell you directly how many parameters.
Parameters follow proc name and flags and stop when packet 3 (you can
have multiple packet too) ends.

On mssql7+ there are core store procedure (sp_executesql, sp_prepare)
that accept SQL text. So your sql text here is only a parameter of a
store procedure.

> For example,
> 
> 0030  fb 04 84 b8 00 00 03 01 00 88 00 00 01 00 21 00   ..............!.
> 0040  5b 00 6d 00 6b 00 31 00 5d 00 2e 00 2e 00 73 00   [.m.k.1.].....s.
> 0050  70 00 5f 00 70 00 72 00 6f 00 63 00 65 00 64 00   p._.p.r.o.c.e.d.
> 0060  75 00 72 00 65 00 5f 00 70 00 61 00 72 00 61 00   u.r.e._.p.a.r.a.
> 0070  6d 00 73 00 5f 00 72 00 6f 00 77 00 73 00 65 00   m.s._.r.o.w.s.e.
> 0080  74 00 00 00 00 00 e7 1e 00 1e 00 6d 00 6b 00 5f   t..........m.k._
> 0090  00 66 00 69 00 6e 00 64 00 5f 00 70 00 72 00 6f   .f.i.n.d._.p.r.o
> 00a0  00 64 00 75 00 63 00 74 00 00 00 38 01 00 00 00   .d.u.c.t...8....
> 00b0  00 00 e7 00 00 ff ff 00 00 e7 00 00 ff ff         ..............
> 
> 0030                                            21 00                 !.
> 
> If 21 (1st byte after the tds header) is interpretted as the length of the 
> procedure name (33), Length(sp_procedure_params_rowset) != 33.
> 

length is express in characters, not bytes. 0x21 characters mean 0x42
bytes. This is a procedure call, not a prepare or similar.

So this it's like executing

EXEC [mk1]..sp_procedure_params_rowset, N'mk_find_product', 1, NULL,
NULL

> FYI:
> ---
> [mk1] is the database name.
> mk_find_product is the procedure name being called.
> 
> This packet is some sort of "prepare" activity being generated by the ADO 
> driver, as the procedure would not be called like this.
> 

I never heard about sp_procedure_params_rowset...

> Here's an sp_unprepare the supports your explanation of the first byte 
> following the header being the stored procedure length:
> 
> 0000  00 01 03 e7 d7 aa 00 10 a4 93 e9 87 08 00 45 00   ..............E.
> 0010  00 53 b8 98 40 00 80 06 ab 5b c0 a8 0a c8 c0 a8   .S..@....[......
> 0020  0a 98 10 53 05 99 c6 fe 83 29 42 2a 18 20 50 18   ...S.....)B*. P.
> 0030  fb 04 07 5b 00 00 03 01 00 2b 00 00 01 00 0c 00   ...[.....+......
> 0040  73 00 70 00 5f 00 75 00 6e 00 70 00 72 00 65 00   s.p._.u.n.p.r.e.
> 0050  70 00 61 00 72 00 65 00 00 00 00 00 38 01 00 00   p.a.r.e.....8...
> 0060  00                                                .
> 

Strange... why they didn't use number for sp_unprepare ??
However sp_unprepare call is done when statement is closed so it can
refer to a previous prepare.

> But here's one that seems to pack a couple of procedures together:
> 
> 0030  fa dd 3d a5 00 00 03 01 00 e4 00 00 01 00 0a 00   ..=.............
> 0040  73 00 70 00 5f 00 70 00 72 00 65 00 70 00 61 00   s.p._.p.r.e.p.a.
> 0050  72 00 65 00 00 00 00 01 26 04 00 00 00 63 5c 00   r.e.....&....c\.
> 0060  00 00 5c 00 00 00 40 00 50 00 31 00 20 00 69 00   ..\...@.P.1. .i.
> 0070  6e 00 74 00 20 00 4f 00 55 00 54 00 50 00 55 00   n.t. .O.U.T.P.U.
> 0080  54 00 2c 00 40 00 50 00 32 00 20 00 76 00 61 00   T.,.@.P.2. .v.a.
> 0090  72 00 63 00 68 00 61 00 72 00 28 00 36 00 34 00   r.c.h.a.r.(.6.4.
> 00a0  29 00 2c 00 40 00 50 00 33 00 20 00 76 00 61 00   ).,.@.P.3. .v.a.
> 00b0  72 00 63 00 68 00 61 00 72 00 28 00 31 00 32 00   r.c.h.a.r.(.1.2.
> 00c0  29 00 00 00 63 46 00 00 00 46 00 00 00 20 00 45   )...cF...F... .E
> 00d0  00 58 00 45 00 43 00 20 00 40 00 50 00 31 00 3d   .X.E.C. .@.P.1.=
> 00e0  00 6d 00 6b 00 5f 00 66 00 69 00 6e 00 64 00 5f   .m.k._.f.i.n.d._
> 00f0  00 70 00 72 00 6f 00 64 00 75 00 63 00 74 00 20   .p.r.o.d.u.c.t. 
> 0100  00 40 00 50 00 32 00 2c 00 40 00 50 00 33 00 20   .@.P.2.,.@.P.3. 
> 0110  00 20 00 00 00 38 01 00 00 00                     . ...8....
> 

packet 3 (as stated above) can contain only a single procedure call.
This is just like

DECLARE @i INT
EXEC sp_prepare @i OUTPUT, N'@P1 int OUTPUT,@P2 varchar(64),@P3
varchar(12)', N' EXEC @P1=mk_find_product @P2,@P3 ', 1

> I think one can follow your explanation through the bytes and see the 
> meaning of some of the parameters and such, but how does one break it 
> down and dynamically determine what's there?  In the second data chunk 
> above, the sp_prepare starts 1 byte after the length:
> 
> 0030  fa dd 3d a5 00 00 03 01 00 e4 00 00 01 00 0a 00   ..=.............
>                                                 ^^
> 0040  73 00 70 00 5f 00 70 00 72 00 65 00 70 00 61 00   s.p._.p.r.e.p.a.
>       ^^
> 
> Length = 10, a 00 byte and the start of a procedure that has a length of 
> 10.  However, my original data held the parameter info between the length 
> byte and the actual SQL text:
> 
> > 0030  f7 48 ca 56 00 00 03 01 00 fa 00 00 01 00 ff ff   .H.V............
>                                                   ^^
> > 0040  0b 00 00 00 00 01 26 04 04 ff ff ff ff 00 00 63   ......&........c
> > 0050  00 00 00 00 09 04 00 01 32 ff ff ff ff 00 00 63   ........2......c
> > 0060  ba 00 00 00 09 04 00 01 32 ba 00 00 00 64 00 65   ........2....d.e
>                                                ^^ 
> > 0070  00 63 00 6c 00 61 00 72 00 65 00 20 00 40 00 70   .c.l.a.r.e. .@.p
> 

??? 
In the first 0a 00 (0xa) is length in character, ff ff (0xffff) mean we
use a number (16 bit) for procedure name.

The last call is

DECLARE @i INT
SET @i = -1
EXEC sp_prepare/sp_prepexec @i OUTPUT, NULL, N'declare @p...

Similar to previous. There are some difference, I think you are using a
mssql7 server and a mssql2k server ...

> What about the data tells me where the parameters start/end, and where the 
> SQL text starts/ends, etc?  I think I need to keep looking at this, I must 
> be missing something...
> 
> Thanks again for the info.
> 

If you can use latest tds.html (from CVS), I updated it yesterday

freddy77