FreeTDS Development Group

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

Mike,

Interesting. If you have any more questions about the sp_cursor*
procedures, just ask.
If you don't mind talking about it, what exactly is it you are trying to
do?

Bill

-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Mike C.
Sent: 01 October 2004 09:55
To: 'FreeTDS Development Group'
Subject: RE: [freetds] sp_cursoroption and blob fields


Hi Bill,
Yes the trace is contiguous.
I post here a more complete trace between the oledb provider and a SQL
Server 6.5.
The 01 00 00 00 insert that broke my dialog with the provider was a
rowstat
column generated by the real server that confused me.
I am studying now how sp_cursor is used as READTEXT.

trace follows,
Mike


>>[frame 22]----------------------------------------------------------
0030                    03 01 00 7B 00 00 01 00 0D 73       ...{.....s
0040  70 5F 63 75 72 73 6F 72 6F 70 65 6E 00 00 00 01 p_cursoropen....
0050  26 04 00 00 00 27 46 46 73 65 6C 65 63 74 20 63 &....'FFselect.c
0060  6F 6C 5F 69 6E 74 34 2C 20 63 6F 6C 5F 74 65 78 ol_int4,.col_tex
0070  74 5F 31 2C 20 63 6F 6C 5F 74 65 78 74 5F 32 2C t_1,.col_text_2,
0080  20 63 6F 6C 5F 76 61 72 63 68 61 72 32 35 35 20 .col_varchar255.
0090  66 72 6F 6D 20 74 62 6C 5F 62 6C 6F 62 73 00 01 from.tbl_blobs..
00A0  38 08 00 00 00 00 01 38 01 00 00 00 00 01 26 04 8......8......&.
00B0  00                                              .

TDS: RPC - sp_cursoropen ( NULL, select col_int4, col_text_1,
col_text_2,
col_varchar255 from tbl_blobs)

<<--------------------------------------------------------------------
0030                    04 01 00 EB 00 00 00 00 A0 36       .........6
0040  00 08 63 6F 6C 5F 69 6E 74 34 0A 63 6F 6C 5F 74 ..col_int4.col_t
0050  65 78 74 5F 31 0A 63 6F 6C 5F 74 65 78 74 5F 32 ext_1.col_text_2
0060  0E 63 6F 6C 5F 76 61 72 63 68 61 72 32 35 35 07 .col_varchar255.
0070  72 6F 77 73 74 61 74 A1 39 00 07 00 09 00 26 04 rowstat.9.....&.
0080  13 00 09 00 23 FF FF FF 7F 09 00 74 62 6C 5F 62 ....#.....tbl_b
0090  6C 6F 62 73 13 00 09 00 23 FF FF FF 7F 09 00 74 lobs....#.....t
00A0  62 6C 5F 62 6C 6F 62 73 02 00 09 00 27 FF 07 00 bl_blobs....'...
00B0  00 00 38 A4 0A 00 09 74 62 6C 5F 62 6C 6F 62 73 ..8....tbl_blobs
00C0  A5 0F 00 01 01 00 02 01 00 03 01 00 04 01 00 05 ................
00D0  01 10 FF 51 00 C1 00 00 00 00 00 79 00 00 00 00 ...Q.......y....
00E0  AC 0B 00 00 01 00 00 00 00 38 30 D3 CF 00 AC 0B .........80.....
00F0  00 00 01 00 00 00 00 38 08 00 00 00 AC 0B 00 00 .......8........
0100  01 00 00 00 00 38 01 00 00 00 AC 0B 00 00 01 00 .....8..........
0110  00 00 00 38 03 00 00 00 FE 08 00 E0 00 00 00 00 ...8............
0120  00                                              .

TDS: Response to frame 22 - Column Name, Column Format, Table Name,
Column
Info, Done in Procedure, Return Status, Return Value, Retu*
TDS: Message Header = Table response (token stream) EOM Len = 235 Chnl =
0
Pkt = 0 Win = 0
TDS: Column Name | col_int4 | col_text_1 | col_text_2 | col_varchar255 |
rowstat
TDS: Column Format | Variable intiger | Text | Text | Variable character
|
Four byte intiger
TDS: Token = Table Name
TDS: Token = Column Info
TDS: Done in Procedure - MORE, PROCERROR, 0 Rows
TDS: Return Status = 0
TDS: Return Value - 13620016
TDS: Return Value - 8
TDS: Return Value - 1
TDS: Return Value - 3
TDS: Done Procedure - FINAL, PROC

>>[frame 24]----------------------------------------------------------
0030                    03 01 00 85 00 00 01 00 0F 73       .........s
0040  70 5F 63 75 72 73 6F 72 6F 70 74 69 6F 6E 00 00 p_cursoroption..
0050  00 00 38 30 D3 CF 00 00 00 38 01 00 00 00 00 00 ..80.....8......
0060  38 02 00 00 00 80 0F 73 70 5F 63 75 72 73 6F 72 8......sp_cursor
0070  6F 70 74 69 6F 6E 00 00 00 00 38 30 D3 CF 00 00 option....80....
0080  00 38 01 00 00 00 00 00 38 03 00 00 00 80 0E 73 .8......8......s
0090  70 5F 63 75 72 73 6F 72 66 65 74 63 68 02 00 00 p_cursorfetch...
00A0  00 38 30 D3 CF 00 00 00 38 10 00 00 00 00 00 38 .80.....8......8
00B0  01 00 00 00 00 00 38 01 00 00 00                ......8....

TDS: Message Header = Remote procedure call EOM Len = 133 Chnl = 0 Pkt =
1
Win = 0

TDS: Remote procedure name length = 15 (0xF)
TDS: Remote Procedure Name = sp_cursoroption
TDS: Options = 0 (0x0)
TDS: Parameter 1 - 13620016
TDS: Parameter 2 - 1
TDS: Parameter 3 - 2
TDS: Parameter 4 - sp_cursoroption = NULL
TDS: Parameter 5 - 13620016
TDS: Parameter 6 - 1
TDS: Parameter 7 - 3
TDS: Parameter 8 - sp_cursorfetch = NULL
TDS: Parameter 9 - 13620016
TDS: Parameter 10 - 16
TDS: Parameter 11 - 1
TDS: Parameter 12 - 1

<<--------------------------------------------------------------------
0030                    04 01 00 74 00 00 00 00 79 00       ...t....y.
0040  00 00 00 FE 89 00 E0 00 00 00 00 00 79 00 00 00 ............y...
0050  00 FE 89 00 E0 00 00 00 00 00 D1 04 01 00 00 00 ................
0060  10 89 01 00 00 00 00 00 00 01 00 00 00 93 0A 00 ................
0070  00 10 8A 01 00 00 00 00 00 00 01 00 00 00 98 0A ................
0080  00 00 0C 76 61 72 63 68 61 72 28 32 35 35 29 01 ...varchar(255).
0090  00 00 00 FF 51 00 C1 00 01 00 00 00 79 00 00 00 ....Q.......y...
00A0  00 FE 08 00 E0 00 01 00 00 00                   ..........

TDS: Response to frame 24 - Return Status, Done Procedure, Return
Status,
Done Procedure, Row, ...TDS: Message Header = Table response (token
stream)
EOM Len = 116 Chnl = 0 Pkt = 0 Win = 0

TDS: Return Status = 0
TDS: Done Procedure - MORE, PROC
TDS: Return Status = 0
TDS: Done Procedure - MORE, PROC
TDS: Row 1 | 1 | ???
---TDS: Token Type = Row
---TDS: Column 1 (col_int4) = 1
---TDS: Column 2 (col_text_1) (split) = ??? (byte 0 through byte 73 of
285
total bytes)

*** here the TDS parser gets puzzled, so starting with FF we have ***
TDS: Done in procedure
TDS: Return Status = 0
TDS: Done Procedure

>>[frame 26]----------------------------------------------------------
0030                    03 01 00 51 00 00 01 00 0F 73       ...Q.....s
0040  70 5F 63 75 72 73 6F 72 6F 70 74 69 6F 6E 00 00 p_cursoroption..
0050  00 00 38 30 D3 CF 00 00 00 38 03 00 00 00 00 00 ..80.....8......
0060  38 02 00 00 00 80 09 73 70 5F 63 75 72 73 6F 72 8......sp_cursor
0070  02 00 00 00 38 30 D3 CF 00 00 00 38 28 00 00 00 ....80.....8(...
0080  00 00 38 01 00 00 00                            ..8....

TDS: RPC - sp_cursoroption ( 13620016, 3, 2, ...)
TDS: Message Header = Remote procedure call EOM Len = 81 Chnl = 0 Pkt =
1
Win = 0

TDS: Remote procedure name length = 15 (0xF)
TDS: Remote Procedure Name = sp_cursoroption
TDS: Options = 0 (0x0)
TDS: Parameter 1 - 13620016
TDS: Parameter 2 - 3
TDS: Parameter 3 - 2
TDS: Parameter 4 - sp_cursor = NULL
TDS: Parameter 5 - 13620016
TDS: Parameter 6 - 40
TDS: Parameter 7 - 1

<<--------------------------------------------------------------------
0030                    04 01 01 BE 00 00 00 00 79 00       ........y.
0040  00 00 00 FE 89 00 E0 00 01 00 00 00 D1 04 01 00 ................
0050  00 00 10 89 01 00 00 00 00 00 00 01 00 00 00 93 ................
0060  0A 00 00 00 00 00 01 00 00 0A 94 4C 01 00 00 49 ...........L...I
0070  4E 53 45 52 54 20 61 70 70 65 6E 64 73 20 6E 65 NSERT.appends.ne
0080  77 20 72 6F 77 73 20 74 6F 20 61 20 74 61 62 6C w.rows.to.a.tabl
0090  65 2E 20 54 6F 20 72 65 70 6C 61 63 65 20 64 61 e..To.replace.da
00A0  74 61 20 69 6E 20 61 20 74 61 62 6C 65 2C 20 74 ta.in.a.table,.t
00B0  68 65 20 44 45 4C 45 54 45 20 6F 72 20 54 52 55 he.DELETE.or.TRU
00C0  4E 43 41 54 45 20 54 41 42 4C 45 20 73 74 61 74 NCATE.TABLE.stat
00D0  65 6D 65 6E 74 73 20 6D 75 73 74 20 62 65 20 75 ements.must.be.u
00E0  73 65 64 20 74 6F 20 63 6C 65 61 72 20 65 78 69 sed.to.clear.exi
00F0  73 74 69 6E 67 20 64 61 74 61 20 62 65 66 6F 72 sting.data.befor
0100  65 20 6C 6F 61 64 69 6E 67 20 6E 65 77 20 64 61 e.loading.new.da
0110  74 61 20 77 69 74 68 20 49 4E 53 45 52 54 2E 20 ta.with.INSERT..
0120  54 6F 20 6D 6F 64 69 66 79 20 63 6F 6C 75 6D 6E To.modify.column
0130  20 76 61 6C 75 65 73 20 69 6E 20 65 78 69 73 74 .values.in.exist
0140  69 6E 67 20 72 6F 77 73 2C 20 75 73 65 20 55 50 ing.rows,.use.UP
0150  44 41 54 45 2E 20 54 6F 20 63 72 65 61 74 65 20 DATE..To.create.
0160  61 20 6E 65 77 20 74 61 62 6C 65 20 61 6E 64 20 a.new.table.and.
0170  6C 6F 61 64 20 69 74 20 77 69 74 68 20 64 61 74 load.it.with.dat
0180  61 20 69 6E 20 6F 6E 65 20 73 74 65 70 2C 20 75 a.in.one.step,.u
0190  73 65 20 74 68 65 20 49 4E 54 4F 20 6F 70 74 69 se.the.INTO.opti
01A0  6F 6E 20 6F 66 20 74 68 65 20 53 45 4C 45 43 54 on.of.the.SELECT
01B0  20 73 74 61 74 65 6D 65 6E 74 2E 10 8A 01 00 00 .statement......
01C0  00 00 00 00 01 00 00 00 98 0A 00 00 0C 76 61 72 .............var
01D0  63 68 61 72 28 32 35 35 29 01 00 00 00 FF 51 00 char(255).....Q.
01E0  C1 00 01 00 00 00 79 00 00 00 00 FE 08 00 E0 00 ......y.........
01F0  01 00 00 00                                     ....

TDS: Response to frame 26 - Return Status, Done Procedure, Row, ...
TDS: Message Header = Table response (token stream) EOM Len = 446 Chnl =
0
Pkt = 0 Win = 0

TDS: Return Status = 0
TDS: Done Procedure - MORE, PROC
TDS: Row 1 | 1 | INSERT appends new rows to ... | ???
---TDS: Token Type = Row
---TDS: Column 1 (col_int4) = 1
---TDS: Column 2 (col_text_1) = INSERT appends ....
---TDS: Column 3 (col_text_2) (split) = ??? (byte 0 through byte 56 of
892678725 total bytes)

*** here the TDS parser gets puzzled, so starting with FF we have ***
TDS: Done in procedure
TDS: Return Status = 0
TDS: Done Procedure

>>[frame 28]----------------------------------------------------------
0030                    03 01 00 51 00 00 01 00 0F 73       ...Q.....s
0040  70 5F 63 75 72 73 6F 72 6F 70 74 69 6F 6E 00 00 p_cursoroption..
0050  00 00 38 30 D3 CF 00 00 00 38 03 00 00 00 00 00 ..80.....8......
0060  38 03 00 00 00 80 09 73 70 5F 63 75 72 73 6F 72 8......sp_cursor
0070  02 00 00 00 38 30 D3 CF 00 00 00 38 28 00 00 00 ....80.....8(...
0080  00 00 38 01 00 00 00                            ..8....

TDS: RPC - sp_cursoroption ( 13620016, 3, 3, ...)
TDS: Message Header = Remote procedure call EOM Len = 81 Chnl = 0 Pkt =
1
Win = 0

TDS: Remote procedure name length = 15 (0xF)
TDS: Remote Procedure Name = sp_cursoroption
TDS: Options = 0 (0x0)
TDS: Parameter 1 - 13620016
TDS: Parameter 2 - 3
TDS: Parameter 3 - 3
TDS: Parameter 4 - sp_cursor = NULL
TDS: Parameter 5 - 13620016
TDS: Parameter 6 - 40
TDS: Parameter 7 - 1

<<--------------------------------------------------------------------
0030                    04 00 02 00 00 00 00 00 79 00       ........y.
0040  00 00 00 FE 89 00 E0 00 01 00 00 00 D1 04 01 00 ................
0050  00 00 10 89 01 00 00 00 00 00 00 01 00 00 00 93 ................
0060  0A 00 00 00 00 00 01 00 00 0A 94 4C 01 00 00 49 ...........L...I
0070  4E 53 45 52 54 20 61 70 70 65 6E 64 73 20 6E 65 NSERT.appends.ne
0080  77 20 72 6F 77 73 20 74 6F 20 61 20 74 61 62 6C w.rows.to.a.tabl
0090  65 2E 20 54 6F 20 72 65 70 6C 61 63 65 20 64 61 e..To.replace.da
00A0  74 61 20 69 6E 20 61 20 74 61 62 6C 65 2C 20 74 ta.in.a.table,.t
00B0  68 65 20 44 45 4C 45 54 45 20 6F 72 20 54 52 55 he.DELETE.or.TRU
00C0  4E 43 41 54 45 20 54 41 42 4C 45 20 73 74 61 74 NCATE.TABLE.stat
00D0  65 6D 65 6E 74 73 20 6D 75 73 74 20 62 65 20 75 ements.must.be.u
00E0  73 65 64 20 74 6F 20 63 6C 65 61 72 20 65 78 69 sed.to.clear.exi
00F0  73 74 69 6E 67 20 64 61 74 61 20 62 65 66 6F 72 sting.data.befor
0100  65 20 6C 6F 61 64 69 6E 67 20 6E 65 77 20 64 61 e.loading.new.da
0110  74 61 20 77 69 74 68 20 49 4E 53 45 52 54 2E 20 ta.with.INSERT..
0120  54 6F 20 6D 6F 64 69 66 79 20 63 6F 6C 75 6D 6E To.modify.column
0130  20 76 61 6C 75 65 73 20 69 6E 20 65 78 69 73 74 .values.in.exist
0140  69 6E 67 20 72 6F 77 73 2C 20 75 73 65 20 55 50 ing.rows,.use.UP
0150  44 41 54 45 2E 20 54 6F 20 63 72 65 61 74 65 20 DATE..To.create.
0160  61 20 6E 65 77 20 74 61 62 6C 65 20 61 6E 64 20 a.new.table.and.
0170  6C 6F 61 64 20 69 74 20 77 69 74 68 20 64 61 74 load.it.with.dat
0180  61 20 69 6E 20 6F 6E 65 20 73 74 65 70 2C 20 75 a.in.one.step,.u
0190  73 65 20 74 68 65 20 49 4E 54 4F 20 6F 70 74 69 se.the.INTO.opti
01A0  6F 6E 20 6F 66 20 74 68 65 20 53 45 4C 45 43 54 on.of.the.SELECT
01B0  20 73 74 61 74 65 6D 65 6E 74 2E 10 8A 01 00 00 .statement......
01C0  00 00 00 00 01 00 00 00 98 0A 00 00 00 00 00 01 ................
01D0  00 00 0A 99 CA 00 00 00 41 20 74 61 62 6C 65 20 ........A.table.
01E0  76 61 72 69 61 62 6C 65 2C 20 69 6E 20 69 74 73 variable,.in.its
01F0  20 73 63 6F 70 65 2C 20 6D 61 79 20 62 65 20 61 .scope,.may.be.a
0200  63 63 65 73 73 65 64 20 6C 69 6B 65 20 61 20 72 ccessed.like.a.r
0210  65 67 75 6C 61 72 20 74 61 62 6C 65 2E 20 54 68 egular.table..Th
0220  75 73 2C 20 74 61 62 6C 65 20 76 61 72 69 61 62 us,.table.variab
0230  6C 65 20 6D 61 79                               le.may

TDS: Response to frame 28 - Return Status, Done Procedure, Row, ...
TDS: Message Header = Table response (token stream) Len = 512 Chnl = 0
Pkt =
0 Win = 0

TDS: Return Status = 0
TDS: Done Procedure - MORE, PROC
TDS: Row 1 | 1 | INSERT appends new rows to ... | ???
---TDS: Token Type = Row
---TDS: Column 1 (col_int4) = 1
---TDS: Column 2 (col_text_1) = INSERT appends new.......
---TDS: Column 3 (col_text_2) (split) = ??? (byte 0 through byte 122 of
231
total bytes)

<<--------------------------------------------------------------------
0030                    04 01 00 9C 00 00 00 00 20 62       .........b
0040  65 20 75 73 65 64 20 61 73 20 74 68 65 20 74 61 e.used.as.the.ta
0050  62 6C 65 20 74 6F 20 77 68 69 63 68 20 72 6F 77 ble.to.which.row
0060  73 20 61 72 65 20 74 6F 20 62 65 20 61 64 64 65 s.are.to.be.adde
0070  64 20 69 6E 20 61 6E 20 49 4E 53 45 52 54 20 73 d.in.an.INSERT.s
0080  74 61 74 65 6D 65 6E 74 2E 20 46 6F 72 20 6D 6F tatement..For.mo
0090  72 65 20 69 6E 66 6F 72 6D 61 74 69 6F 6E 2C 20 re.information,.
00A0  73 65 65 20 74 61 62 6C 65 2E 0C 76 61 72 63 68 see.table..varch
00B0  61 72 28 32 35 35 29 01 00 00 00 FF 51 00 C1 00 ar(255).....Q...
00C0  01 00 00 00 79 00 00 00 00 FE 08 00 E0 00 01 00 ....y...........
00D0  00 00                                           ..

TDS: Response to frame 28 - Row 1 (cont) at Column 3 (cont) ...
TDS: Message Header = Table response (token stream) EOM Len = 156 Chnl =
0
Pkt = 0 Win = 0

TDS: Row 1 (cont) | Col 3 (cont) | varchar(255) | 1
---TDS: Column 3 (col_text_2) (cont) = ??? (byte 123 through byte 230 of
231
total bytes)
---TDS: Column 4 (col_varchar255) = varchar(255)
---TDS: Column 5 (rowstat) = 1

TDS: Done in Procedure - MORE, PROCERROR, 1 Row
TDS: Return Status = 0
TDS: Done Procedure - FINAL, PROC

---------------------------------------------------------------
*** then it starts again for row 2, with sp_cursoroption /
sp_cursorfetch
like in frame 24 ***











-----Original Message-----
From: Thompson, Bill D (London) [mailto:bill_d_thompson AT ml.com]
Sent: Thursday, September 30, 2004 7:43 AM
To: mcbp223 AT yahoo.com
Subject: RE: [freetds] sp_cursoroption and blob fields


Hi Mike,

I assume the traces you've sent represent a single, contiguous
"conversation".
They seem to make some sense in this context.

This is what I understand about the various sp_cursor* commands being
sent...

/* just send me a TEXTPTR for column 2 */
-> sp_cursoroption @cursor, 1, 2

/* get me 1 row from ABSOLUTE (16) position 1 */
-> sp_cursorfetch  @cursor, 16, 1, 1

/* send me the data (not a TEXTPTR) for column 2 */
-> sp_cursoroption @cursor, 3, 2

/* AIUI This is saying: reposition me back to row 1 */
/* of the cursor and re-send me the data...         */

-> sp_cursor       @cursor, 40, 1

Did the server send the data again after this call ?

Bill



-----Original Message-----
From: Mike C. [mailto:mcbp223 AT yahoo.com]
Sent: 30 September 2004 15:19
To: Thompson, Bill D (London)
Subject: RE: [freetds] sp_cursoroption and blob fields


Hi Bill,

It looks like i was sending something that I don't need to send, the 01
00 00 00 portion right after TEXTPTR, because that's what I saw on a
real SQL Server trace. After reading your point, I modified my code to
send only TEXTPTR, and it looks like it goes past the first fetch.
Now I have to process sp_cursor with @opttype = 3, which I suppose
stands for READTEXT.

Below are some traces for a select with first column varint(4) and
second column varchar(1024), not including sp_cursoropen.

========================================================================
======|
>> TDS::RPC, length(8 + 85), packet(1), last
 |0000| 0f 73 70 5f  63 75 72 73  6f 72 6f 70  74 69 6f 6e
|.sp_cursoroption|
 |0010| 00 00 00 00  38 20 70 4b  00 00 00 38  01 00 00 00   |....8
pK...8....|
 |0020| 00 00 38 02  00 00 00 80  0e 73 70 5f  63 75 72 73
|..8......sp_curs|
 |0030| 6f 72 66 65  74 63 68 02  00 00 00 38  20 70 4b 00
|orfetch....8 pK.|
 |0040| 00 00 38 10  00 00 00 00  00 38 01 00  00 00 00 00
|..8......8......|
 |0050| 38 01 00 00  00                                      |8....
|

  --> sp_cursoroption
  --> par[0] = 4943904
  --> par[1] = 1
  --> par[2] = 2
  --> ------- proc separator? --------
  <-- TDS::RETURNSTATUS 0
  <-- TDS::DONE_PROC status: 89 info: e0 count: 0

  --> sp_cursorfetch
  --> par[0] = 4943904
  --> par[1] = 16
  --> par[2] = 1
  --> par[3] = 1
  <-- TDS::ROW
  <-- TDS::DONE_IN_PROC status: 11 info: c1 count: 1
  <-- TDS::RETURNSTATUS 0
  <-- TDS::DONE_PROC status: 08 info: e0 count: 1

<< TDS::RESPONSE, length(8 + 60), packet(1), last
 |0000| 79 00 00 00  00 fe 89 00  e0 00 00 00  00 00 d1 04
|y...............|
 |0010| 09 00 00 00  10 30 31 32  33 34 35 36  37 38 39 41
|.....0123456789A|
 |0020| 42 43 44 45  46 ff 11 00  c1 00 01 00  00 00 79 00
|BCDEF.........y.|
 |0030| 00 00 00 fe  08 00 e0 00  01 00 00 00
|............    |
------------------------------------------------------------------------
------|


========================================================================
======|
>> TDS::RPC, length(8 + 73), packet(1), last
 |0000| 0f 73 70 5f  63 75 72 73  6f 72 6f 70  74 69 6f 6e
|.sp_cursoroption|
 |0010| 00 00 00 00  38 20 70 4b  00 00 00 38  03 00 00 00   |....8
pK...8....|
 |0020| 00 00 38 02  00 00 00 80  09 73 70 5f  63 75 72 73
|..8......sp_curs|
 |0030| 6f 72 02 00  00 00 38 20  70 4b 00 00  00 38 28 00   |or....8
pK...8(.|
 |0040| 00 00 00 00  38 01 00 00  00                         |....8....
|

  --> sp_cursoroption
  --> par[0] = 4943904
  --> par[1] = 3
  --> par[2] = 2
  --> ------- proc separator? --------
  <-- TDS::RETURNSTATUS 0
  <-- TDS::DONE_PROC status: 89 info: e0 count: 0

  --> sp_cursor
  --> par[0] = 4943904
  --> par[1] = 40
  --> par[2] = 1
  <-- TDS::DONE status: 00 info: 0 count: 0

<< TDS::RESPONSE, length(8 + 23), packet(1), last
 |0000| 79 00 00 00  00 fe 89 00  e0 00 00 00  00 00 fd 00
|y...............|
 |0010| 00 00 00 00  00 00 00                                |.......
|
------------------------------------------------------------------------
------|


========================================================================
======|
>> TDS::RPC, length(8 + 33), packet(1), last
 |0000| 09 73 70 5f  63 75 72 73  6f 72 02 00  00 00 38 20
|.sp_cursor....8 |
 |0010| 70 4b 00 00  00 38 28 00  00 00 00 00  38 01 00 00
|pK...8(.....8...|
 |0020| 00                                                   |.
|

  --> sp_cursor
  --> par[0] = 4943904
  --> par[1] = 40
  --> par[2] = 1
  <-- TDS::DONE status: 00 info: 0 count: 0

<< TDS::RESPONSE, length(8 + 9), packet(1), last
 |0000| fd 00 00 00  00 00 00 00  00                         |.........
|
------------------------------------------------------------------------
------|

I'll check how SQL 6.5 does it.

Mike



-----Original Message-----
From: Thompson, Bill D (London) [mailto:bill_d_thompson AT ml.com]
Sent: Thursday, September 30, 2004 5:30 AM
To: mcbp223 AT yahoo.com; FreeTDS Development Group
Subject: RE: [freetds] sp_cursoroption and blob fields


Hi Mike,

The MS oledb provider is sending 2 sp_cursoroption RPC calls.
the meaning of value 1 in the first parameter to sp_cursoroption to tell
the server to only return the TEXTPTR,
as opposed to the complete data, for the specified BLOB column (0 for
all columns).

The oledb provider would then get at the BLOB data (if it wanted it) by
other methods, for example a READTEXT language command.

In order to answer your questions about the 0xD1 data, I'd need to see
the full 0xD1 message, accompanied by a description of the column types.

Bill

-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Mike C.
Sent: 30 September 2004 12:20
To: 'FreeTDS Development Group'
Subject: [freetds] sp_cursoroption and blob fields


Hi all,

I am trying to emulate a server TDS 4.2, and now I'm working on
server-side
cursors.
I can send data to the MS oledb provider by answering to sp_cursorfetch
and
friends, but there's something blurry with blob fields.
When the provider encounters metadata with blobs as response for
sp_cursoropen, it sends a chained sp_cursoroption as following:

0x03 - RPC
RPC name: sp_cursoroption
RPC options: 0
------------
par[0] = 123456 (cursor handle)
par[1] = 1      (option 1)
par[2] = 2      (column number with blob field)
0x80 ---------- (procedure separator ??)
par[3] = "sp_cursoroption"
par[4] = 123456 (cursor handle)
par[5] = 1      (option 1)
par[6] = 4      (column number with blob field)
0x80 ---------- (procedure separator ??)
par[7] = "sp_cursorfetch"
par[8] = 123456 (cursor handle)
par[9] = 0x10   (fetch type - absolute row index)
par[10]= 1      (start row number)
par[11]= 1      (row count to return)
--- eof ---

Tested against a real SQL server, the response to this RPC looks like
-------------------------------------------------------------
0x04 message header - response
----
0x79 return status (0)
0xFE done procedure (status 0x89, info 0xE0, count 0)
---- above repeated for each sp_cursoroption
then
--------
0xD1 ROW token
  field 1
  field 2
  etc.
--------
then
0xFF done inside procedure (status 0x51, info 0xC1, count 1)
0x79 return status (0)
0xFE done procedure (status 0x08, info 0xE0, count 1)
-------------------------------------------------------------

The option "1" from sp_cursoroption seem to truncate the size of each
blob
field passed inside 0xD1 ROW, and the field looks like
------------
10 (probably the TEXTPTR length)
31 03 00 00   00 00 00 00   01 00 00 00  BD 9F 34 00 (probably TEXTPTR)
01 00 00 00 (here should be TIMESTAMP 8 bytes, followed by data, but it
is
only 4 bytes)
------------

Well I tried to send the same thing to the provider, i.e. an arbitrary
16
bytes as TEXTPTR and 01 00 00 00 instead of TIMESTAMP and actual data,
but
the provider seems unphased, and it sends sp_cursorclose right away. If
I
send the whole data, the provider does the same thing, just closes the
cursor. It doesn't report any error in both cases.

Do you guys have any info on the cursor protocol when there are blob
fields
in the metadata?
Or if you can see any magic numbers inside the TEXTPTR area returned by
the
real server.

Thanks,
Mike


_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
--------------------------------------------------------

If you are not an intended recipient of this e-mail, please notify the
sender, delete it and do not read, act upon, print, disclose, copy,
retain or redistribute it. Click here for important additional terms
relating to this e-mail.     http://www.ml.com/email_terms/
--------------------------------------------------------
--------------------------------------------------------

If you are not an intended recipient of this e-mail, please notify the
sender, delete it and do not read, act upon, print, disclose, copy,
retain
or redistribute it. Click here for important additional terms relating
to
this e-mail.     http://www.ml.com/email_terms/
--------------------------------------------------------


_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds 
--------------------------------------------------------
 
If you are not an intended recipient of this e-mail, please notify the 
sender, delete it and do not read, act upon, print, disclose, copy, retain or 
redistribute it. Click here for important additional terms relating to this 
e-mail.     http://www.ml.com/email_terms/ 
--------------------------------------------------------