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

Insert Trigger and Updating a view

I am just getting back to SqlServer and TSQL after a 4 year hiatus.
I want to write a trigger to update a view with the same record that is
being inserted into a table. I have a trigger bound to the table to be
inserted and since it is a simple process, I will probably forgoing using a
stored proc.
In my trigger I want to essentially do:
On Insert....
Update MyView
Set Col A = NewCol A Value,
Col B = NewCol B Value,
Col C = NewCol C Value
The NewCol x Value values are the insert values of the record being posted
to the table being inserted.
Interbase has New property. Can anyone provide the syntac to accomplish my
task?
TIA
LarryLarry,
There are two special tables accessible within a trigger,
inserted and deleted. They hold the new rows (for inserts
and updates) and the old rows (for deletes and updates)
of the target table with respect to the statement that fired
the trigger. Note that a trigger fires only once, whether the
triggering statement affects multiple rows or not, and so the
inserted and deleted tables can have more than one row.
It sounds like your triggering statement will be affecting
only one row, but it is still a good idea to consider making
sure of that by checking @.@.rowcount at the very beginning
of the trigger.
Your trigger will probably look something like this:
create trigger... as
if @.@.rowcount <> 1 begin
raiserror (as appropriate)
rollback transaction -- or return, or whatever you need
update MyView set
ColA = i.ColA,
ColB = i.ColB,
. and so on
where MyView.viewKey = i.ColumnIdentifyingViewRowToUpdate
If you want, post CREATE TABLE statement and sample data for an
example and we can try to help more specifically to your case. You
can also find out more about the special tables inserted and deleted
in Books Online.
Steve Kass
Drew University
DelphiGuy wrote:

>I am just getting back to SqlServer and TSQL after a 4 year hiatus.
>I want to write a trigger to update a view with the same record that is
>being inserted into a table. I have a trigger bound to the table to be
>inserted and since it is a simple process, I will probably forgoing using a
>stored proc.
>In my trigger I want to essentially do:
>On Insert....
>Update MyView
>Set Col A = NewCol A Value,
> Col B = NewCol B Value,
> Col C = NewCol C Value
>
>The NewCol x Value values are the insert values of the record being posted
>to the table being inserted.
>Interbase has New property. Can anyone provide the syntac to accomplish my
>task?
>TIA
>Larry
>
>

Insert or update into a Image column causes data to double in size

We are experiencing problems inserting or updating image fields from
one table to another in SQL Server.

When we do this what ever size of file we insert is doubled in size
when it is inserted into the destination table.

This happens in insert and update queries, and if we use DTS.

Any help would be greatly appreciatedskyhawker9 (andyh@.bmtdsl.co.uk) writes:
> We are experiencing problems inserting or updating image fields from
> one table to another in SQL Server.
> When we do this what ever size of file we insert is doubled in size
> when it is inserted into the destination table.
> This happens in insert and update queries, and if we use DTS.
> Any help would be greatly appreciated

I'm afraid that it's difficult to help, since you don't disclose very
many details. Could you provide the code you use to copy the fields?

What is the typical size of the data you copy?

What you mean by "file ... is doubled in size"? What file? The database
file? But then you talk about inserting the file? How do you know
that it doubles in size?

Sorry for asking so many questions, but while you know very well what is
going on at your site, I know next to nothing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||We are storing documents such as PDFs, word documents etc in a object
field in a table. When we transfer the the contents from one table to
another using even a simple

INSERT INTO tblTest (ObjectField)
SELECT ObjectField
FROM tblTest2

The data in the destination is exactly doubled in size when you load
the document again ie when you save a 1Mb document into the table
transfer it to the second table it becomes 2Mb.

Hope this makes it more clear.|||skyhawker9 (andyh@.bmtdsl.co.uk) writes:
> We are storing documents such as PDFs, word documents etc in a object
> field in a table. When we transfer the the contents from one table to
> another using even a simple
> INSERT INTO tblTest (ObjectField)
> SELECT ObjectField
> FROM tblTest2
> The data in the destination is exactly doubled in size when you load
> the document again ie when you save a 1Mb document into the table
> transfer it to the second table it becomes 2Mb.

How do you deduce that?

It would help if you could provide a complete script that demonstrates
the problem. That would be a script that creates the table, populates
the first, and the loads the data into other, and then somehow gets
the size of the documents. I realise that including a 1MB object in
a script is not realistic, but the table pub..pub_info includes an
image column. Maybe you can build a repro script from that.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

insert or update errors

Is there a way I can stop a form from inserting or updating a record when there is an error. I have an sql 2000 DB. I have noticed that if the db field can handle 50 characters and the form field has no limit on the number of characters no errors are displayed to the user if they try to use more than the 50 characters in the textbox. The record is not saved. None of the fields are saved. I do notice the autonumber generated is skipped by the db. That is, the next autonumber for a successful insert skips the logical next number. How do I capture this error, or any save error and return the user back to the form? Yes, I have limited the number of characters a user can type on the textbox now, but I would really like to catch save or insert errors. I use asp.net 2.0 and VB. I don't know C#.
thanks
Milton

You can add some validation within stored procedures but are just using raw SQL for insert and update? It is far better to use stored procedures for all insert, update and delete operations. (If possible all access should be via stored procedures)

If you include an output BIT parameter called say NAD (for No Abnormalities Detected) and only set to to 1 if the insert/update worked with no error. Use a command button to fire the insert/update and if there is an error, post a message to the user and stay on the same page.

|||Its always a good idea to do some basic validation at the front end (like data types, length etc) before passing all the data to the db and validate at the back end. Validation at the DB level should be for any constraint validation, PK-FK validation etc.|||

Is there an example here or elsewhere? I don't see how the form interacts with the sql server. Output BIT parameter? I have a linkbutton control on my form which has no code behind. I have the command name as "insert" for that link button in the properties of the linkbutton. I use VB not C#.
thanks
Milton

|||I will post some links for you tonight.|||Please look at my posting onhttp://forums.asp.net/thread/1687128.aspx. This will allow you to create a range of stored procedures plus wrapper code in either VB.NET or C#. The accompanying library is in C#. I will attempt to produce a VB.Net port this weekend.