Discussion:
[freetds] Using dbwritetext against Ms-Sql
Trygve Liland
2012-05-25 12:15:33 UTC
Permalink
Hi

We are doing a test using FreeTDS on Solaris moving a Fortran/C application from Sybase to MS-Sql server.
Recently we ran into a problem I'm hoping to get a hint on how to solve.

Simplified we are doing:

- Update IMAGECOLTAB set IMAGECOL=NULL

- Select IMAGECOL from IMAGECOLTAB

- Get Pointer to IMAGECOL from dbtextptr

- Get TimeStamp from dbtexttimestamp

- Do dbwritetext with length of image to be written and NULL in last parameter

- Loop text and do dbmoretext
The problem is that we are getting
tds_put_string: "writetext bulk IMAGECOLTAB.IMAGECOL 0x00000000000000000000000000000000 timestamp = 0x0000000000000000 with log"
Invalid text, ntext, or image pointer value 0x00000000000000000000000000000000.

Any idea on what we are doing wrong? I have checked code that we have in another program where we used dblib against Ms-Sqlserver, and I see more or less the same code.

Below is a simplified C code for my function, and the resulting tds log file.

Our function:
DBBINARY *txtptr;
unsigned char buff[WIDEBUFSIZE];
unsigned char tabCol[100];
QueryTable *pqq, *pqu; /* Pointer to current table */
DBBINARY tsStamp[DBTXTSLEN];
DBBINARY txtPointer[DBTXPLEN];

/* Set the widecolumn to NULL to simplify the storage process */
//Execute "update IMAGECOLTAB set IMAGECOL=NULL where ID=1"

/* Select the record to obtain the text's timestamp */
//Execute "select IMAGECOL from IMAGECOLTAB where ID=1"
//pqq is query pointer

sprintf((char *)tabCol, "IMAGECOLTAB.IMAGECOL");

/* We must have a valid textpointer */
if ((txtptr = dbtxptr(pqq->DbProc, 1)) == NULL) {
return;
}
memcpy(txtPointer, txtptr, DBTXPLEN);
memcpy(tsStamp, dbtxtimestamp(pqq->DbProc, 1), DBTXTSLEN);

if ((dbwritetext(pqu->DbProc, tabCol, txtPointer, DBTXPLEN, tsStamp, TRUE, (DBINT) NoBytes, NULL))!= SUCCEED)
return;
if ((dbsqlok(pqu->DbProc)) != SUCCEED)
return;
if ((dbresults(pqu->DbProc)) != SUCCEED)
return;

/* Now, read all data from file and store into database */
SomeStored = 0;
while ((BytesRead = read(filePointer, buff, WIDEBUFSIZE)) > 0) {
if ((dbmoretext(pqu->DbProc, (DBINT) BytesRead, (LPCBYTE)buff)) != SUCCEED)
return;
if (!SomeStored)
SomeStored = 1;
}

if (SomeStored) {
if ((dbsqlok(pqu->DbProc)) != SUCCEED)
return;
if ((dbresults(pqu->DbProc)) != SUCCEED)
return;
}

The TDS log file (IMAGECOLTAB=PLOTHEAD, IMANGECOL=CONTENTS):
10:38:18.662559 5857 (dblib.c:1312):dbcmd(a74160, update PLOTHEAD set CONTENTS=NULL where MODULE='MAP' and PLOTNAME='ON-TEST-15MAI2012')
Snip
10:38:18.671062 5857 (dblib.c:1312):dbcmd(a74160, select CONTENTS from PLOTHEAD where MODULE='MAP' and PLOTNAME='ON-TEST-15MAI2012')
10:38:18.671068 5857 (dblib.c:1319):dbcmd() bufsz = 103
10:38:18.671074 5857 (dblib.c:5882):dbfreebuf(a74160)
10:38:18.671080 5857 (dblib.c:1369):dbsqlexec(a74160)
10:38:18.671086 5857 (dblib.c:6862):dbsqlsend(a74160)
10:38:18.671092 5857 (mem.c:615):tds_free_all_results()
10:38:18.671098 5857 (util.c:156):Changed query state from IDLE to QUERYING
10:38:18.671104 5857 (write.c:140):tds_put_string converting 98 bytes of "select CONTENTS from PLOTHEAD where MODULE='MAP' and INITIAL='ON' and PLOTNAME='ON-TEST-15MAI2012'"
Snip
10:38:18.671220 5857 (token.c:540):tds_process_tokens(a72e18, ffbdeb1c, ffbdeb18, 0x6914)
10:38:18.671227 5857 (util.c:156):Changed query state from PENDING to READING
10:38:18.671719 5857 (net.c:555):Received header
0000 04 01 00 42 00 bc 01 00- |...B....|
10:38:18.671733 5857 (net.c:609):Received packet
0000 04 01 00 42 00 bc 01 00-81 01 00 00 00 09 00 22 |...B.... ......."|
0010 00 10 00 00 08 00 50 00-4c 00 4f 00 54 00 48 00 |......P. L.O.T.H.|
0020 45 00 41 00 44 00 08 43-00 4f 00 4e 00 54 00 45 |E.A.D..C .O.N.T.E|
0030 00 4e 00 54 00 53 00 d1-00 fd 10 00 c1 00 01 00 |.N.T.S.. ........|
0040 00 00 - |..|

10:38:18.671760 5857 (token.c:555):processing result tokens. marker is 81(TDS7_RESULT)
10:38:18.671766 5857 (token.c:1515):processing TDS7 result metadata.
10:38:18.671773 5857 (mem.c:615):tds_free_all_results()
10:38:18.671780 5857 (token.c:1540):set current_results (1 column) to tds->res_info
10:38:18.671786 5857 (token.c:1547):setting up 1 columns
10:38:18.671796 5857 (token.c:1486):tds7_get_data_info:
colname = CONTENTS (8 bytes)
type = 34 (image)
server's type = 34 (image)
column_varint_size = 4
column_size = 4096 (4096 on server)
10:38:18.671804 5857 (token.c:1556): name size/wsize type/wtype utype
10:38:18.671811 5857 (token.c:1557): -------------------- --------------- --------------- -------
10:38:18.671817 5857 (token.c:1567): CONTENTS 4096/4096 34/34 0
10:38:18.671824 5857 (util.c:156):Changed query state from READING to PENDING
10:38:18.671831 5857 (dblib.c:4700):dbsqlok() found result token
10:38:18.671837 5857 (dblib.c:1668):dbresults(a74160)
10:38:18.671842 5857 (dblib.c:1674):dbresults: dbresults_state is 1 (_DB_RES_RESULTSET_EMPTY)
10:38:18.671849 5857 (token.c:540):tds_process_tokens(a72e18, ffbdeb8c, ffbdeb88, 0x6914)
10:38:18.671855 5857 (util.c:156):Changed query state from PENDING to READING
10:38:18.671861 5857 (token.c:555):processing result tokens. marker is d1(ROW)
10:38:18.671867 5857 (token.c:666):tds_process_tokens::SET_RETURN stopping on current token
10:38:18.671892 5857 (util.c:156):Changed query state from READING to PENDING
10:38:18.671899 5857 (dblib.c:1695):dbresults() tds_process_tokens returned 1 (TDS_SUCCEED),
result_type TDS_ROW_RESULT
10:38:18.671906 5857 (dblib.c:1657):dbresults returning 1 (SUCCEED)
10:38:18.671912 5857 (dblib.c:4063):dbcmdrow(a74160)
10:38:18.671918 5857 (dblib.c:3842):dbrows(a74160)
10:38:18.671925 5857 (dblib.c:2575):dbbind(a74160, 1, 1, 0, ffbdecf6)
10:38:18.671932 5857 (dblib.c:2812):dbwillconvert(SYBIMAGE, SYBCHAR)
10:38:18.671939 5857 (convert.c:2788):tds_willconvert(34, 47)
10:38:18.671947 5857 (convert.c:2792):tds_willconvert(34, 47) returns yes
10:38:18.671954 5857 (dblib.c:2018):dbnextrow(a74160)
10:38:18.671960 5857 (dblib.c:2031):dbnextrow() dbresults_state = 2 (_DB_RES_RESULTSET_ROWS)
10:38:18.671967 5857 (token.c:540):tds_process_tokens(a72e18, ffbdeb9c, 0, 0x1508)
10:38:18.671973 5857 (util.c:156):Changed query state from PENDING to READING
10:38:18.671979 5857 (token.c:555):processing result tokens. marker is d1(ROW)
10:38:18.671985 5857 (token.c:2304):tds_process_row(): reading column 0
10:38:18.671992 5857 (token.c:2049):tds_get_data: type 34, varint size 4
10:38:18.671999 5857 (token.c:2110):tds_get_data(): wire column size is -1
10:38:18.672005 5857 (util.c:156):Changed query state from READING to PENDING
10:38:18.672012 5857 (buffering.h:306):buffer_transfer_bound_data(a74168 4040 -1 a74160 0)
10:38:18.672019 5857 (dblib.c:548):dbgetnull(a74160, 1, -1, ffbdecf6)
10:38:18.672026 5857 (dblib.c:2100):leaving dbnextrow() returning REG_ROW/MORE_ROWS
10:38:18.672038 5857 (dblib.c:6374):dbtxptr(a74160, 1)
10:38:18.672050 5857 (dblib.c:6347):dbtxtimestamp(a74160, 1)
10:38:18.672060 5857 (dblib.c:6410):dbwritetext(a627c8, PLOTHEAD.CONTENTS, 9d14cc, 16, 9d14dc, 1)
10:38:18.672068 5857 (dblib.c:2217):dbconvert(a627c8, SYBBINARY, 9d14cc, 16, SYBCHAR, ffbdebe0, -1)
10:38:18.672075 5857 (dblib.c:2349):dbconvert() calling tds_convert
10:38:18.672083 5857 (dblib.c:2352):dbconvert() called tds_convert returned 32
10:38:18.672089 5857 (dblib.c:2455):dbconvert() outputting 32 bytes character data destlen = -1
10:38:18.672096 5857 (dblib.c:2217):dbconvert(a627c8, SYBBINARY, 9d14dc, 8, SYBCHAR, ffbdebc8, -1)
10:38:18.672102 5857 (dblib.c:2349):dbconvert() calling tds_convert
10:38:18.672109 5857 (dblib.c:2352):dbconvert() called tds_convert returned 16
10:38:18.672115 5857 (dblib.c:2455):dbconvert() outputting 16 bytes character data destlen = -1
10:38:18.672153 5857 (mem.c:615):tds_free_all_results()
10:38:18.672161 5857 (util.c:156):Changed query state from IDLE to QUERYING
10:38:18.672167 5857 (write.c:140):tds_put_string converting 107 bytes of "writetext bulk PLOTHEAD.CONTENTS 0x00000000000000000000000000000000 timestamp = 0x0000000000000000 with log"
10:38:18.672176 5857 (write.c:168):tds_put_string wrote 214 bytes
10:38:18.672182 5857 (util.c:156):Changed query state from QUERYING to PENDING
10:38:18.672189 5857 (net.c:741):Sending packet
0000 01 01 00 de 00 00 01 00-77 00 72 00 69 00 74 00 |........ w.r.i.t.|
0010 65 00 74 00 65 00 78 00-74 00 20 00 62 00 75 00 |e.t.e.x. t. .b.u.|
0020 6c 00 6b 00 20 00 50 00-4c 00 4f 00 54 00 48 00 |l.k. .P. L.O.T.H.|
0030 45 00 41 00 44 00 2e 00-43 00 4f 00 4e 00 54 00 |E.A.D... C.O.N.T.|
0040 45 00 4e 00 54 00 53 00-20 00 30 00 78 00 30 00 |E.N.T.S. .0.x.0.|
0050 30 00 30 00 30 00 30 00-30 00 30 00 30 00 30 00 |0.0.0.0. 0.0.0.0.|
0060 30 00 30 00 30 00 30 00-30 00 30 00 30 00 30 00 |0.0.0.0. 0.0.0.0.|
0070 30 00 30 00 30 00 30 00-30 00 30 00 30 00 30 00 |0.0.0.0. 0.0.0.0.|
0080 30 00 30 00 30 00 30 00-30 00 30 00 30 00 20 00 |0.0.0.0. 0.0.0. .|
0090 74 00 69 00 6d 00 65 00-73 00 74 00 61 00 6d 00 |t.i.m.e. s.t.a.m.|
00a0 70 00 20 00 3d 00 20 00-30 00 78 00 30 00 30 00 |p. .=. . 0.x.0.0.|
00b0 30 00 30 00 30 00 30 00-30 00 30 00 30 00 30 00 |0.0.0.0. 0.0.0.0.|
00c0 30 00 30 00 30 00 30 00-30 00 30 00 20 00 77 00 |0.0.0.0. 0.0. .w.|
00d0 69 00 74 00 68 00 20 00-6c 00 6f 00 67 00 |i.t.h. . l.o.g.|

10:38:18.672283 5857 (token.c:540):tds_process_tokens(a631d8, ffbdeacc, ffbdeac8, 0x100)
10:38:18.672292 5857 (util.c:156):Changed query state from PENDING to READING
10:38:18.673186 5857 (net.c:555):Received header
0000 04 01 01 31 00 7a 01 00- |...1.z..|

10:38:18.673202 5857 (net.c:609):Received packet
0000 04 01 01 31 00 7a 01 00-aa ba 00 d3 1b 00 00 01 |...1.z.. ........|
0010 10 4f 00 49 00 6e 00 76-00 61 00 6c 00 69 00 64 |.O.I.n.v .a.l.i.d|
0020 00 20 00 74 00 65 00 78-00 74 00 2c 00 20 00 6e |. .t.e.x .t.,. .n|
0030 00 74 00 65 00 78 00 74-00 2c 00 20 00 6f 00 72 |.t.e.x.t .,. .o.r|
0040 00 20 00 69 00 6d 00 61-00 67 00 65 00 20 00 70 |. .i.m.a .g.e. .p|
0050 00 6f 00 69 00 6e 00 74-00 65 00 72 00 20 00 76 |.o.i.n.t .e.r. .v|
0060 00 61 00 6c 00 75 00 65-00 20 00 30 00 78 00 30 |.a.l.u.e . .0.x.0|
0070 00 30 00 30 00 30 00 30-00 30 00 30 00 30 00 30 |.0.0.0.0 .0.0.0.0|
0080 00 30 00 30 00 30 00 30-00 30 00 30 00 30 00 30 |.0.0.0.0 .0.0.0.0|
0090 00 30 00 30 00 30 00 30-00 30 00 30 00 30 00 30 |.0.0.0.0 .0.0.0.0|
00a0 00 30 00 30 00 30 00 30-00 30 00 30 00 30 00 2e |.0.0.0.0 .0.0.0..|
00b0 00 08 44 00 42 00 53 00-51 00 4c 00 54 00 30 00 |..D.B.S. Q.L.T.0.|
00c0 34 00 00 01 00 ab 60 00-25 0e 00 00 00 00 22 00 |4.....`. %.....".|
00d0 54 00 68 00 65 00 20 00-73 00 74 00 61 00 74 00 |T.h.e. . s.t.a.t.|
00e0 65 00 6d 00 65 00 6e 00-74 00 20 00 68 00 61 00 |e.m.e.n. t. .h.a.|
00f0 73 00 20 00 62 00 65 00-65 00 6e 00 20 00 74 00 |s. .b.e. e.n. .t.|
0100 65 00 72 00 6d 00 69 00-6e 00 61 00 74 00 65 00 |e.r.m.i. n.a.t.e.|
0110 64 00 2e 00 08 44 00 42-00 53 00 51 00 4c 00 54 |d....D.B .S.Q.L.T|
0120 00 30 00 34 00 00 01 00-fd 02 00 fd 00 00 00 00 |.0.4.... ........|
0130 00 - |.|

10:38:18.673299 5857 (token.c:555):processing result tokens. marker is aa(ERROR)
10:38:18.673305 5857 (token.c:122):tds_process_default_tokens() marker is aa(ERROR)
10:38:18.673311 5857 (token.c:2588):tds_process_msg() reading message 7123 from server
10:38:18.673322 5857 (token.c:2661):tds_process_msg() calling client msg handler
10:38:18.673328 5857 (dbutil.c:85):_dblib_handle_info_message(a37b60, a631d8, ffbde8f8)
10:38:18.673334 5857 (dbutil.c:86):msgno 7123: "Invalid text, ntext, or image pointer value 0x00000000000000000000000000000000."


Regards
Trygve Liland
James K. Lowden
2012-05-25 13:33:52 UTC
Permalink
On Fri, 25 May 2012 12:15:33 +0000
Post by Trygve Liland
The problem is that we are getting
tds_put_string: "writetext bulk IMAGECOLTAB.IMAGECOL
0x00000000000000000000000000000000 timestamp = 0x0000000000000000
with log" Invalid text, ntext, or image pointer value
0x00000000000000000000000000000000.
Any idea on what we are doing wrong? I have checked code that we have
in another program where we used dblib against Ms-Sqlserver, and I
see more or less the same code.
Those zeros are a problem, no doubt about it.

Do the db-lib unit tests t0013.c and t0014.c work with your server?
That would verify that dbwritetext() works with the TDS version you're
using.

The log doesn't record the value of the textptr returned by dbtxtptr.
If the server is sending a NULL textptr, see if that changes using TDS
7.1. If not, if you're receiving a good textptr from the server, you
need to find out why you're sending a NULL textptr.

H?lsingar,

--jkl
Frediano Ziglio
2012-05-25 14:39:50 UTC
Permalink
Post by James K. Lowden
On Fri, 25 May 2012 12:15:33 +0000
Post by Trygve Liland
The problem is that we are getting
tds_put_string: "writetext bulk IMAGECOLTAB.IMAGECOL
0x00000000000000000000000000000000 timestamp = 0x0000000000000000
with log" Invalid text, ntext, or image pointer value
0x00000000000000000000000000000000.
Any idea on what we are doing wrong? I have checked code that we have
in another program where we used dblib against Ms-Sqlserver, and I
see more or less the same code.
Those zeros are a problem, no doubt about it.
Do the db-lib unit tests t0013.c and t0014.c work with your server?
That would verify that dbwritetext() works with the TDS version you're
using.
The log doesn't record the value of the textptr returned by dbtxtptr.
If the server is sending a NULL textptr, see if that changes using TDS
7.1. ?If not, if you're receiving a good textptr from the server, you
need to find out why you're sending a NULL textptr.
H?lsingar,
--jkl
Try also to downgrade to 7.0 protocol.

MS decided to not return textptr with data even they still support
TEXTPTR function on T-SQL, or better, they decide to always return an
invalid textptr! I don't remember if was with 7.1 or 7.2... I don't
really understand why...

Frediano
Trygve Liland
2012-05-30 07:58:34 UTC
Permalink
Thanks for feedback.
We tried to downgrade to 7.0, but same result...
The project is run by a governmental specialist directorate in Norway, and I'm just trying to help out with the C code issues in my spare time :-)
I've instructed the programmer, to try the unit tests.
Sorry we didn't try them first.

What I don't understand is that the text pointer is null.
This code should test for null values:
If (txtptr = dbtxptr(pqq->DbProc, 1)) == NULL) return;
And I dblib.c:
CHECK_NULP(textptr, "dbwritetext", 3, FAIL);
So I suspect this code I dblib.c:
dbconvert(dbproc, SYBBINARY, (BYTE *) textptr, textptrlen, SYBCHAR, (BYTE *) textptr_string, -1);
My textpointer is:
DBBINARY txtPointer[DBTXPLEN]

I'll report again when the unit tests have been run.

Trygve

-----Original Message-----
From: freetds-bounces at lists.ibiblio.org [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of Frediano Ziglio
Sent: 25. mai 2012 16:40
To: FreeTDS Development Group
Subject: Re: [freetds] Using dbwritetext against Ms-Sql
Post by James K. Lowden
On Fri, 25 May 2012 12:15:33 +0000
Post by Trygve Liland
The problem is that we are getting
tds_put_string: "writetext bulk IMAGECOLTAB.IMAGECOL
0x00000000000000000000000000000000 timestamp = 0x0000000000000000
with log" Invalid text, ntext, or image pointer value
0x00000000000000000000000000000000.
Any idea on what we are doing wrong? I have checked code that we have
in another program where we used dblib against Ms-Sqlserver, and I
see more or less the same code.
Those zeros are a problem, no doubt about it.
Do the db-lib unit tests t0013.c and t0014.c work with your server?
That would verify that dbwritetext() works with the TDS version you're
using.
The log doesn't record the value of the textptr returned by dbtxtptr.
If the server is sending a NULL textptr, see if that changes using TDS
7.1. ?If not, if you're receiving a good textptr from the server, you
need to find out why you're sending a NULL textptr.
H?lsingar,
--jkl
Try also to downgrade to 7.0 protocol.

MS decided to not return textptr with data even they still support TEXTPTR function on T-SQL, or better, they decide to always return an invalid textptr! I don't remember if was with 7.1 or 7.2... I don't really understand why...

Frediano

Loading...