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: <lists-tds AT mymailfilter.net>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] RPC/Packet Type 3 Format
  • Date: Mon, 21 Jul 2003 22:41:24 -0500 (CDT)

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.

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.

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.

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 .

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....

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

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.


On 21 Jul 2003, Frediano Ziglio wrote:

> I didn't read all messages related so perhaps some information will be
> duplicated.
> >
> > 0030 f7 48 ca 56 00 00
>
> This should be a TCP/IP info or other related.
> > 03 01 00 fa 00 00 01 00
>
> Header: type 3 (RPC) final, length 0xfa (correct)
>
> > ff ff .H.V............
>
> Length of store procedure name. TDS8 (mssql2k+, not mssql7) handle null
> name (-1 length) using numbers instead of names (I know only some
> numbers like 15 -> sp_unprepare).
>
> > 0040 0b 00
>
> This is RPC numbers... perhaps sp_executesql
>
> > 00 00
>
> flags
>
> > 00 01 26 04 04 ff ff ff ff
>
> output parameter, type INTN (0x26) length 4 (data and type length),
> value -1
>
> > 00 00 63 ......&........c
>
> input parameter, NTEXT
>
> > 0050 00 00 00 00
>
> ??? strange, must be length of type... perhaps I have to check tds
> code...
>
> > 09 04 00 01 32
>
> collation info (0x409 = english)
>
> > ff ff ff ff
>
> length of text (NULL)
>
> > 00 00 63 ........2......c
>
> input NTEXT...
>
> > 0060 ba 00 00 00
>
> yes, this is length
>
> > 09 04 00 01 32
>
> collate
>
> > ba 00 00 00
>
> length
>
> > 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
> > 0080 00 34 00 20 00 69 00 6e 00 74 00 65 00 67 00 65 .4. .i.n.t.e.g.e
> > 0090 00 72 00 0d 00 0a 00 65 00 78 00 65 00 63 00 20 .r.....e.x.e.c.
> > 00a0 00 6a 00 64 00 5f 00 74 00 65 00 73 00 74 00 5f .j.d._.t.e.s.t._
> > 00b0 00 30 00 30 00 31 00 20 00 40 00 70 00 31 00 3d .0.0.1. .@.p.1.=
> > 00c0 00 33 00 2c 00 20 00 40 00 70 00 32 00 3d 00 27 .3.,. .@.p.2.=.'
> > 00d0 00 74 00 65 00 73 00 74 00 27 00 2c 00 20 00 40 .t.e.s.t.'.,. .@
> > 00e0 00 70 00 33 00 3d 00 20 00 27 00 30 00 37 00 2f .p.3.=. .'.0.7./
> > 00f0 00 31 00 37 00 2f 00 32 00 30 00 30 00 33 00 27 .1.7./.2.0.0.3.'
> > 0100 00 2c 00 20 00 40 00 40 00 70 00 34 00 3d 00 40 .,. .@.@.p.4.=.@
> > 0110 00 70 00 34 00 20 00 6f 00 75 00 74 00 70 00 75 .p.4. .o.u.t.p.u
> > 0120 00 74 00 0d 00 0a 00
>
> just sql code you send
>
> > 00 00 26 04 04 01 00 00 00 .t.......&......
>
> input type INTN, length 4, value 1
>
> >
> > The actual SQL code is:
> >
> > declare @p4 integer
> > exec jd_test_001 @p1=3, @p2='test', @p3= '07/17/2003', @@p4=@p4 output
> >
> > I'm confused about what appears to be some flags and info preceding the
> > actual SQL code.
>
> ...
>
> Well, any byte is decoded...
>
> 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...
>
> I've learn two things:
> - length of NTEXT is before collation information
> - if parameter list is empty I can use a NULL value per parameters
>
> freddy77
>
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
>







Archive powered by MHonArc 2.6.24.

Top of Page