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,
IainIain Porter (stuff@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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
------------------|||Iain Porter (stuff@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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|||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