469,890 Members | 1,766 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,890 developers. It's quick & easy.

Inserting / Updating LongText Field using WriteText?

Hi, I'm trying to store large strings to a database, so am using the
text field type (LongText). I have used this before when storing the
html of a webpage, and was able to store more than 255 characters by
using just a normal update sql statement. Now I'm trying to store the
body of research papers, and must be doing something different, as I
can only store 255 characters.

Can someone explain why SQL Server doesn't like what I am doing -
should I be using the WriteText / UpdateText function? If so, please
explain by example how I would do that, and why doing that works.

Thanks so much,
Iain
Jul 20 '05 #1
5 5125
Iain Porter (st***@intraspin.com) writes:
Hi, I'm trying to store large strings to a database, so am using the
text field type (LongText). I have used this before when storing the
html of a webpage, and was able to store more than 255 characters by
using just a normal update sql statement. Now I'm trying to store the
body of research papers, and must be doing something different, as I
can only store 255 characters.

Can someone explain why SQL Server doesn't like what I am doing -
should I be using the WriteText / UpdateText function? If so, please
explain by example how I would do that, and why doing that works.


You need to tell us of what you are doing and how you are diagnosing
that you only store 255 characters. Did you check datalength() for
the column?

A common trap is that the default output in Query Analyzer is 255 chars;
this is an option in QA that you can change under Tools.

If this does not help, you need to supply more information. Code snippets,
and table defintions are welcome.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
Apologies for the lack of detail. The problem is apparent because
when selecting the longtext feild, using READTEXT as below, the text
data cuts off after 255. The problem could be here, getting data out -
I'm not convinced I have the code right, I just think it's in the
getting data in.

Thanks very much for your help, let me know if you need different
info,
Iain

Code samples:

To get data out from the longtext column(content) by id:
javascript:
try {
conn.Open(strConn);
cmd.ActiveConnection = conn;
cmd.CommandText = "SelectContent";
cmd.CommandType = adCmdStoredProc;
cmd.Parameters.Append(cmd.CreateParameter("@id", adInteger,
adParamInput, 4, id));
contentRS = cmd.Execute();

var content = "";
while (!contentRS.EOF) {
content += contentRS("content");
contentRS.MoveNext;
}
}
....
return content;

SQL (SelectContent):
SET NOCOUNT ON

DECLARE
@txtptrval VARBINARY(16),
@startOffset INT,
@readLength INT,
@datalength INT

SELECT @txtptrval = TEXTPTR(content) FROM papers WHERE id=@id
SELECT @startOffset = 0
SELECT @readLength = 255
SELECT @datalength = DATALENGTH(content) FROM papers WHERE id=@id

-- If last chunk, reduce buffer size to the nChars remaining
IF ((@startOffset + @readLength) > @datalength) (
SELECT @readLength = @datalength - @startOffset
)

WHILE (@startOffset < @datalength)
BEGIN
READTEXT papers.content @txtptrval @startOffset @readLength
SELECT @startOffset = @startOffset + @readLength

-- Last chunk, reduce buffer size to the get the last nChars
remaining
IF (@startOffset + @readLength) > @datalength
SELECT @readLength = @datalength - @startOffset
END
SET NOCOUNT OFF
END
---------------------------------------------------------------------

To get data in:

CREATE proc InsertPaper
@authors nvarchar(20),
@title nvarchar(255),
@pubyear int,
@journal nvarchar(255),
@issue int,
@pages nvarchar (13),
@paperabstract text,
@content text,
@id int OUTPUT

as

insert into Papers(authors, title, pubyear, journal, issue, pages,
abstract, content)
values (@authors, @title, @pubyear, @journal, @issue, @pages,
@paperabstract, @content)

select @id = @@identity
GO
--------------------------------------------------------
Jul 20 '05 #3
Iain Porter (st***@intraspin.com) writes:
Apologies for the lack of detail. The problem is apparent because
when selecting the longtext feild, using READTEXT as below, the text
data cuts off after 255. The problem could be here, getting data out -
I'm not convinced I have the code right, I just think it's in the
getting data in.

Thanks very much for your help, let me know if you need different
info,
When I run a modified version of you script, I seem to get all the data in
the text column, sliced in pieces of 255 chars at a time. So the SQL seems
to be OK.

The problem is like to be in the Javascript code:
while (!contentRS.EOF) {
content += contentRS("content");
contentRS.MoveNext;
}


You are assuming that you have one result set with all the slices of
the text column. But you have one result set for each slice, instead
of MoveNext, you should have:

contentsRS = contentsRS.NextRecordset

and the stop condition should be on whether contentsRS is a valid
object or not.

Disclaimer: I have no experience or knowledge of Javascript programming.
I am assuming that you are using ADO, because it looks like ADO.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
Erland, I'm sorry I've taken so long to come back to you - I didn't
get an email through alerting me to your post, not sure why - thank
you so much for responding again.

I've updated my code as you suggested, but unfortunately get the same
result. My change is listed below:

var content = "";
// while (!contentRS.EOF) {
// content += contentRS("content");
// contentRS.MoveNext;
// }

while (contentRS.State != 0) { // 0 means adStateClosed
content += contentRS("content");
contentRS.NextRecordset;
}

I'm at a loss as to why it's not working - any further thoughts? Is
there a way to count the number of RecordSets returned and reference
each by number or something? I can't see anything like that in a ADO
RecordSet Object Reference.

Thanks very much, I'll check back sooner this time.
Iain
Jul 20 '05 #5
Hi!! I figured it out - thanks so much for your help. The solution,
for anyone searching, is that you have to set the recordset to the
recordset's NextRecordSet:

contentRS = contentRS.NextRecordSet

Thanks again Erland,
Iain
Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by AP | last post: by
2 posts views Thread by Adrian Majchrzak | last post: by
1 post views Thread by Yama | last post: by
7 posts views Thread by diffuser78 | last post: by
4 posts views Thread by =?iso-8859-1?B?aWFuYXLp?= | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.