freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

RE: [freetds] sp_cursoroption and blob fields

From: "Mike C." <mcbp223 AT yahoo.com>
To: "'FreeTDS Development Group'" <freetds AT lists.ibiblio.org>
Subject: RE: [freetds] sp_cursoroption and blob fields
Date: Fri, 1 Oct 2004 03:02:15 -0700

Hi Bill,
Great, I'm going to ask whenever I get deadlocked.

As for what I'm trying to do, I have a database engine with no network
support, and I want to get some exposure for it, to different technologies
like odbc, oledb, jdbc and dotnet. Grace to this group's efforts, to jTDS
and mono, TDS seems to be the most open protocol to do client/server,
although it was not intended to be open.
I have complaced myself into 4.2 because the MS Network Monitor parser for
TDS makes it easy to discover the protocol, but it fails to keep in sync
when encountering wide characters, as in 7.0 and 8. However I'm afraid I'll
have to switch to wide character, since 4.2 is losing support fast, for
example the jdbc driver from Microsoft doesn't talk to TDS 4.2; I'm not sure
about Mono.
Regarding the programming language, I use C++ with gcc and Visual, I am too
afraid of memory leaks to use C, and I guess a server can afford to be less
portable than a client.

How about a higher level question - is it good to stick to a binary protocol
for a server, or go to xml rather?

Mike

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

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

<<attachment: winmail.dat>>

RE: [freetds] sp_cursoroption and blob fields, Mike C., 10/01/2004
- <Possible follow-up(s)>
- RE: [freetds] sp_cursoroption and blob fields, Thompson, Bill D (London), 10/01/2004
  - RE: [freetds] sp_cursoroption and blob fields, Mike C., 10/01/2004
- RE: [freetds] sp_cursoroption and blob fields, ZIGLIO, Frediano, VF-IT, 10/01/2004
  - RE: [freetds] sp_cursoroption and blob fields, Mike C., 10/01/2004