Length issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Length issue

Author

Topic

label
Posting Yak Master

197 Posts

Posted - 2003-03-12 : 10:38:37

I have a form that needs to accept a field that could easily have over 8000 characters in the text field. How do I allow a user to make one entry that is longer than 8000 characters?

For instance; I have people who are copying HTML output from an excel spreadsheet and the copied text is often longer than a varchar(8000).

What can I do and still keep the data in a single row?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-12 : 10:47:07

Why would you want to store that in a database anyway, and why would you have the users submit it via a web form? And how are they getting HTML output from (or in) an Excel spreadsheet?

You can store it in a text column if need be, but what you're describing is highly unusual and can probably be done in an easier and better fashion.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-12 : 11:51:53

Reading the subject, I expected a different response from you Rob ...

Jay White
{0}

chadmat
The Chadinator

1974 Posts

Posted - 2003-03-12 : 12:29:35

Nice one Jay!

label
Posting Yak Master

197 Posts

Posted - 2003-03-12 : 13:03:09

quote:

Why would you want to store that in a database anyway, and why would you have the users submit it via a web form?

1. I have a Bulk Emailer Wizard. I have created an application that will allow a user to type in the title of the section they are working with and then add content for that section. Some sections will need to include HTML formatting in order for the Email Newsletter to look good.

2. I store each section in a database so they can rearrange the order of each section or edit them.

3. They submit it via a webform because this Bulk Wizard mailer is part of a larger portal with a number of other tools designed to work with my site.

quote:
And how are they getting HTML output from (or in) an Excel spreadsheet?

File, Save As web page in Excel 2000.

quote:
You can store it in a text column if need be, but what you're describing is highly unusual and can probably be done in an easier and better fashion.

If you've got a suggestion let me know. I need to be able to allow people to enter a section at a time, reorder and edit each section afterwords and then use each section to programmatically generate a proffessional look Email Newsletter.

They need to be able to generate this email from here on the intranet or at home over the internet and it needs to be part of the existing Portal along with all the other tools we currently have.

If you can come up with a better way of doing it, I'm all ears.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-12 : 13:21:39

quote:
If you've got a suggestion let me know.

I think Rob did make a suggestion: TEXT datatype.

The other thing you could do is break your character data into 8000 character chunks and store each chunk in a varchar(8000) along with a primary key and an order number so that you can re-attach the chunks later.

Jay White
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-12 : 14:53:02

I've messed around with image data types. I was able to Place an Adobe file in a column with an image datatype, and retrieve it, but it always seem to be corrupted a little.

Anyway, Heres what I played with (never implemented):

Declare @ptrValue2 varbinary(16), @intSize2 int, @intBytesLeft int, @Offset int, @intSize3 int

Execute ImageTest_GetSize2 'A', @ptrValue = @ptrValue2 output, @Size = @intSize2 output

Select @intBytesLeft = @intSize2, @Offset = 0, @intSize3 = 32000
Select @intSize2, @Offset, @intSize3

While @intBytesLeft > 0

BEGIN

If @intBytesLeft < 32000
BEGIN
Select @intSize3 = @intBytesLeft
END

Select @intBytesLeft = @intBytesLeft - @intSize3

-- Select 'Execute ImageTest2', @ptrValue2, @Offset, @intSize3
Execute ImageTest2 @ptrValue2, @Offset, @intSize3

Select @Offset = @Offset + @intSize3

END

--

if exists (select * from sysobjects where id = object_id('dbo.ImageTest2') and sysstat & 0xf = 4)
drop procedure dbo.ImageTest2
GO

CREATE PROCEDURE ImageTest2

@ptrValue varbinary(16)
, @Offset int
, @Size int
AS

-- Execute ImageTest2
--
-- Enterprise Solutions
--
-- File: {\\tsstrv03\ESolutions}:\Technology Services\Projects\LifeProducts\Procedures\ImageTest2.sql
-- Date: March 4th, 2002
-- Author: Brett Kaiser
-- Server: NJROS1D136
-- Database: ImageTest2
-- Login: sa
-- Description: Investigate how to extract bolbs fro SQL Server 7
--
-- The stream will do the following:
--
-- 1.
--
--
--
--
-- Tables Used:
-- Table1
--
--
-- Tables Created: None
--
--
-- Row Estimates:
-- name rows reserved data index_size unused
-- -------------------- ----------- ------------------ ------------------ ------------------ ------------------
--
--Change Log
--
-- UserId Date Description
-- ----------- -------------- ------------------------------------------------------------------------------------------
-- x002548 12/14/2001 1. Initial release
--
--
--

Declare @error_out int, @Result_Count int, @Error_Message varchar(255), @Error_Type int, @Error_Loc int

Begin

Begin Tran

-- Select @ptrValue = TextPtr(imgPicture) from Table1 Where charKey = 'A'

If TextValid('Table1.imgPicture', @ptrValue) <> 1
BEGIN
Select @Error_Loc = 1
Select @Error_Message = 'Pointer value not valid. Value equals: ' + RTrim(Convert(Char(16),@ptrValue))
Select @Error_Type = 50002
GOTO ImageTest2_Error
END

ReadText Table1.imgPicture @ptrValue @Offset @Size

Select @Result_Count = @@ROWCOUNT, @error_out = @@error

If @error_out <> 0
BEGIN
Select @Error_Loc = 2
Select @Error_Type = 50001
GOTO ImageTest2_Error
END

If @Result_Count = 0
BEGIN
Select @Error_Loc = 2
Select @Error_Message = 'Could Not find image for pointer value ' + RTrim(Convert(Char(16),@ptrValue))
Select @Error_Type = 50002
GOTO ImageTest2_Error
END

If @Result_Count > 1
BEGIN
Select @Error_Loc = 2
Select @Error_Message = 'Found multiple images for pointer value ' + RTrim(Convert(Char(16),@ptrValue))
Select @Error_Type = 50002
GOTO ImageTest2_Error
END

End

ImageTest2_Exit:

COMMIT TRAN
Return

ImageTest2_Error:

Rollback TRAN

If @Error_Type = 50001
BEGIN
Select @error_message = (Select 'Location: ' + RTrim(Convert(char(3),@Error_Loc))
+ ' @@ERROR: ' + RTrim(Convert(char(6),error))
+ ' Severity: ' + RTrim(Convert(char(3),severity))
+ ' Message: ' + RTrim(description)
From master..sysmessages
Where error = @error_out)
END
If @Error_Type = 50002
BEGIN
Select @Error_Message = 'Location: ' + RTrim(Convert(char(3),@Error_Loc))
+ ' Severity: UserLevel '
+ ' Message: ' + RTrim(@Error_Message)
END

RAISERROR @Error_Type @Error_Message
Return
GO

if exists (select * from sysobjects where id = object_id('dbo.ImageTest_GetSize2') and sysstat & 0xf = 4)
drop procedure dbo.ImageTest_GetSize2
GO

CREATE PROCEDURE ImageTest_GetSize2

@charKey Char(10)
, @ptrValue varbinary(16) Output
, @Size int Output

-- , @TableName varchar(255)
-- , @ColumnName varchar(255)

AS

-- Execute ImageTest_GetSize2 A
--
-- Enterprise Solutions
--
-- File: {\\tsstrv03\ESolutions}:\Technology Services\Projects\LifeProducts\Procedures\ImageTest_GetSize2.sql
-- Date: March 4th, 2002
-- Author: Brett Kaiser
-- Server: NJROS1D136
-- Database: ImageTest_GetSize2
-- Login: sa
-- Description: Get Size of an image column
--
-- The stream will do the following:
--
-- 1.
--
--
--
--
-- Tables Used:
-- Table1
--
--
-- Tables Created: None
--
--
-- Row Estimates:
-- name rows reserved data index_size unused
-- -------------------- ----------- ------------------ ------------------ ------------------ ------------------
--
--Change Log
--
-- UserId Date Description
-- ----------- -------------- ------------------------------------------------------------------------------------------
-- x002548 12/14/2001 1. Initial release
--
--

Declare @error_out int, @Result_Count int, @Error_Message varchar(255), @Error_Type int, @Error_Loc int

Begin

Begin Tran

-- Select @Size=DataLength(imgPicture), @PtrValue=TextPtr(imgPicture) from Table1 Where charKey = @charKey

Select @Size=DataLength(imgPicture), @ptrValue=TextPtr(imgPicture) from Table1 Where charKey = @charKey

Select @Result_Count = @@ROWCOUNT, @error_out = @@error

If @error_out <> 0
BEGIN
Select @Error_Loc = 2
Select @Error_Type = 50001
GOTO ImageTest_GetSize2_Error
END

If @Result_Count = 0
BEGIN
Select @Error_Loc = 2
Select @Error_Message = 'No rows found for key: ' + @charKey
Select @Error_Type = 50002
GOTO ImageTest_GetSize2_Error
END

If @Result_Count > 1
BEGIN
Select @Error_Loc = 2
Select @Error_Message = 'Multiple rows found for key: ' + @charKey
Select @Error_Type = 50002
GOTO ImageTest_GetSize2_Error
END

End

ImageTest_GetSize2_Exit:

COMMIT TRAN
Return

ImageTest_GetSize2_Error:

Rollback TRAN

If @Error_Type = 50001
BEGIN
Select @error_message = (Select 'Location: ' + RTrim(Convert(char(3),@Error_Loc))
+ ' @@ERROR: ' + RTrim(Convert(char(6),error))
+ ' Severity: ' + RTrim(Convert(char(3),severity))
+ ' Message: ' + RTrim(description)
From master..sysmessages
Where error = @error_out)
END
If @Error_Type = 50002
BEGIN
Select @Error_Message = 'Location: ' + RTrim(Convert(char(3),@Error_Loc))
+ ' Severity: UserLevel '
+ ' Message: ' + RTrim(@Error_Message)
END

RAISERROR @Error_Type @Error_Message
Return
GO

Brett

8-)

darinh
Yak Posting Veteran

58 Posts

Posted - 2003-03-12 : 15:35:33

Another issue you might want to consider, although really outside the scope of this forum, is that saving Excel spreadsheets as Html is going to give you a very 'Microsoftised' version of html. This is fine as long as everyone has a Microsoft application to view their email on which probably isn't the case. Also it will be bloated by all the Microsoft tags making each email much bigger than it needs to be. This could be an issue if you are sending a lot of stuff and paying your ISP for throughput.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources