FreeTDS Development Group

["Thompson, Bill D (London)" <ThompBil AT exchange.uk.ml.com>]

Hi,

There's more information here than I have time to do justice to - sorry!

One thing that's confusing you is the "name" of the stored procedure to be
executed.

As described in our file doc/tds.html, the name length is held as a *two
byte* integer, followed by the name.

In TDS 76.0 of course this integer is little endian, so in your first
example the name length is 0x21 0x00 (33 in decimal), and the name of the SP
appears to be [mk1]..sp_procedure_params_rowset  - that's 33 characters (its
in UNICODE in the packet of course).

This stored procedure seems to be a SQL server internal procedure, but it's
known about - search in google!

You can execute it in SQL Query Analyzer, it seems to take a single
parameter - the procedure name - and returns details about the parameters.
VERY USEFUL as it turns out - I'd been wondering in the RPC logic how to
find out about a stored procedure's parameters as it would be very useful
information to have in the FreeTDS API's (when we try and validate a user's
calls to the RPC interface API's)...

So  (back on to your first example) . We're calling
sp_procedure_params_rowset with a single parameter of a procedure name.

After the procedure name we have two bytes of "flags" - whose meaning we
don't yet fully understand. 
However these are not set in this case, so we have two bytes  0x00 0x00

Then we have the parameter data. 
First up is the parameter name, sent as a self describing token - 1 byte of
length followed by n bytes of actual name where n = the length.
In this case, the length is 0x00, so there is no name.

Next up is a byte which we understand to say whether the parameter is input
or output. In this case the byte is 0x00, which indicates an INPUT
parameter.

Next up is the description of the type of parameter. This can get a bit
complicated. I'm not going to describe the many variations that can be
coded, look in tds_put_data_info in src/tds/query.c.

In this case though we have the following:

A byte for the data type - in this case 0xE7 which indicates a UNICODE
varchar ( NVARCHAR in Database terms)
There's then a type dependent field describing the length of the parameter.
A NVARCHAR requires a two byte integer to describe its length, in this case
0x1e 0x00 - I make that 30. 

Then we have the real Parameter data, first a type dependent length (0x1e
0x00 again) followed by the actual data (at last).
This is "mk_find_product" 15 characters in length which equals 30 in UNICODE


Q.E.D. (I hope)

Bill 


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



> -----Original Message-----
> From: lists-tds AT mymailfilter.net [SMTP:lists-tds AT mymailfilter.net]
> Sent: 22 July 2003 04:41
> To:   FreeTDS Development Group
> Subject:      Re: [freetds] RPC/Packet Type 3 Format
> 
> 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
> > 
> 
> 
> 
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds