Skip to Content.
Sympa Menu

freetds - Re: [freetds] RPC/Packet Type 3 Format

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Frediano Ziglio <freddyz77 AT tin.it>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] RPC/Packet Type 3 Format
  • Date: Tue, 22 Jul 2003 07:33:55 -0000

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






Archive powered by MHonArc 2.6.24.

Top of Page